SQL Select Rows Between Dynamic Date Range

Question

For every NAME, I want to query data for the CALL_DATE that is less than or equal to the three months from START_DATE. Below is my attempt but I am getting an error.

Query:

SELECT CALLER_NAME AS "NAME", 
       CAST(CREATED AS DATE) AS "CALL_DATE", 
       DURATION, 
       CAST(START_DATE AS DATE) AS "START_DATE"
FROM table1 
GROUP BY NAME
    HAVING CAST(CREATED AS DATE) >= MIN(CAST(START_DATE AS DATE)) AND CAST(CREATED AS DATE) <= DATEADD(MONTH, 3, CAST(START_DATE AS DATE))
ORDER BY AE_NAME, CALL_DATE

Error

Msg 8121, Level 16, State 1, Line 12 Column 'CREATED' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 12 Column 'START_DATE' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

I understand why I am getting this error but I don't know how to fix this. I'll very much appreciate your help.

WHERE is used to filter rows, HAVING is used to filter groups. You need a where clause here. — Sean Lange
– Sean Lange, Commented Jan 10, 2019 at 16:43
Without knowing the expected results or the data, the only suggestion i can give is to put "CREATED" & "START_DATE" in the group by — Doug Coats
– Doug Coats, Commented Jan 10, 2019 at 16:43

John Pherson · Accepted Answer · 2019-01-10 16:44:37Z

2

You need to group by the original field names, not the alias's. You also need to group everything that is not aggregated.

This should work:

SELECT CALLER_NAME AS "NAME", 
       CAST(CREATED AS DATE) AS "CALL_DATE", 
       DURATION, 
       CAST(START_DATE AS DATE) AS "START_DATE"
FROM table1 
GROUP BY  CALLER_NAME, CREATED, DURATION, START_DATE
    HAVING CAST(CREATED AS DATE) >= MIN(CAST(START_DATE AS DATE)) AND CAST(CREATED AS DATE) <= DATEADD(MONTH, 3, CAST(START_DATE AS DATE))
ORDER BY AE_NAME, CALL_DATE

answered Jan 10, 2019 at 16:44

John Pherson

362 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krishnang K Dalal Over a year ago

Thank you, John, for your help.

Jon · Accepted Answer · 2019-01-10 16:44:32Z

1

Add 'CREATED' and 'START_DATE' to the Group By clause.

SELECT CALLER_NAME AS "NAME", 
       CAST(CREATED AS DATE) AS "CALL_DATE", 
       DURATION, 
       CAST(START_DATE AS DATE) AS "START_DATE"
FROM table1 
GROUP BY NAME, CREATED, START_DATE
    HAVING CAST(CREATED AS DATE) >= MIN(CAST(START_DATE AS DATE)) AND CAST(CREATED AS DATE) <= DATEADD(MONTH, 3, CAST(START_DATE AS DATE))
ORDER BY AE_NAME, CALL_DATE

Or use WHERE rather than HAVING

SELECT CALLER_NAME AS "NAME", 
       CAST(CREATED AS DATE) AS "CALL_DATE", 
       DURATION, 
       CAST(START_DATE AS DATE) AS "START_DATE"
FROM table1 
GROUP BY NAME
    WHER CAST(CREATED AS DATE) >= MIN(CAST(START_DATE AS DATE)) AND CAST(CREATED AS DATE) <= DATEADD(MONTH, 3, CAST(START_DATE AS DATE))
ORDER BY AE_NAME, CALL_DATE

answered Jan 10, 2019 at 16:44

Jon

362 bronze badges

1 Comment

Krishnang K Dalal Over a year ago

Ah, it was a stupid mistake. Thank you for the answer.

Roy Brander · Accepted Answer · 2019-01-10 16:57:15Z

I'm afraid you have to fix it by re-writing from scratch. As the error indicates, when you GROUP on a column or more, the only things that can really be in the select are those columns (or expressions) in the GROUP BY clause, or aggregate functions that apply, in this case, to ALL the rows with a given name.

When you select both the NAME and (say) the DURATION, it doesn't think you want the DURATION value that's in the same row as one NAME, but some function (sum, min, max,etc) of all the DURATIONs beside that NAME. You aren't doing that with ANY of the other columns selected, neither the two dates nor the duration.

This is confusing enough that I'm kind of guessing what you do want, which is not really an aggregate report on multiple rows for each NAME. I think you want a row of output for every row with a NAME, but just select rows that where:

1) CREATED is >= the minimum START_DATE in the whole table 2) CREATED is < three months after the START_DATE in that row

The only aggregation needed at all is to find that minimum START_DATE. The easiest-to-read code for that is a subquery. If I've guess right, that code would be (not sure of your SQL variant, I'll attempt to follow that CAST syntax with cut&paste):

SELECT CALLER_NAME AS "NAME", 
       CAST(CREATED AS DATE) AS "CALL_DATE", 
       DURATION, 
       CAST(START_DATE AS DATE) AS "START_DATE"
FROM table1 
WHERE CAST(CREATED AS DATE) >= (SELECT MIN(CAST(START_DATE AS DATE)) FROM table1)
  AND CAST(CREATED AS DATE) <= DATEADD(MONTH, 3, CAST(START_DATE AS DATE));

Collectives™ on Stack Overflow

SQL Select Rows Between Dynamic Date Range

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related