Search code examples
sqlmysqlmysql-5.5

Referencing another column in HAVING clause

I've set up this table:

SELECT n.order, n.Start, n.End

FROM 
(SELECT t.order
, LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
, LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
FROM 
(SELECT 0 as 'order' 
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
) n

result

The actual query I want is:

SELECT COUNT(b.PatNum) FROM
    (SELECT pl.PatNum
    FROM procedurelog pl 
    WHERE pl.ProcStatus=2
    GROUP BY pl.PatNum
    HAVING MIN(pl.ProcDate) BETWEEN n.Start AND n.End) b

How can I reference n.Start and n.End like this?

Most of my attempts all resulted in 'unknown column...' in 'having clause'

The closest I've gotten yields only a single row:

SELECT b.order, b.Start, b.End, COUNT(b.PatNum)

FROM 

(   SELECT n.order, n.Start, n.End,pl.PatNum
    FROM procedurelog pl
    ,(  SELECT t.order
        , LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
        , LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
        FROM 
        (SELECT 0 as 'order' 
        UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
    ) n
    GROUP BY pl.PatNum
    HAVING MIN(pl.ProcDate) BETWEEN n.Start AND n.End
) b
Solution

  • MYsql 5.5 is very old and out of support, you should think about upgrading it first to 5.6 and then at least to 5.7, it should be a problem,but maybe you need to change one or othe query.

    SELECT 
     b.order, b.Start, b.End, COUNT(b.PatNum)    
FROM 
(   SELECT n.order, n.Start,n.End,p3.PatNum
 FROM 
      ( SELECT  MIN(pl.ProcDate) minPDCTDate ,pl.PatNum
    FROM procedurelog pl

    GROUP BY pl.PatNum) p2
    JOIN procedurelog p3 ON p2.PatNum = p3.PatNum
            JOIN (  SELECT t.order
        , LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
        , LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
        FROM 
        (SELECT 0 as 'order' 
        UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
    ) n
     ON minPDCTDate BETWEEN n.Start AND n.End
) b
GROUP By b.order, b.Start, b.En

    this would show you the count of partnums for every time frame.

    I reduced the speed of the query as i remocve the cross join and this should yield the correct numbers, but as you didn't provide data to test the query