CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL: Where Clause

SELECT DISTINCT Campaign_id 
FROM Impressions 
WHERE Date BETWEEN '2015-03-01' AND '2015-03-31' ;

The above query gives me the result for the Campaign_id's that have been active on any date between 2015-03-01 and 2015-03-31.

I want the result set to contain the campaign_id's if the have been active on all the dates in between 2015-03-01 and 2015-03-31.

How would I go about this?

Upvotes: 5

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Using HAVING clause with COUNT(DISTINCT):

SELECT Campaign_id 
FROM Impressions
WHERE Date between '2015-03-01' and '2015-03-31' 
GROUP BY Campaign_id
HAVING COUNT(DISTINCT Date) = 31;

You should also review this blog post by Aaron Betrand to understand why using BETWEEN for dates is a bad idea.

You can make arrange the query to only mention the dates once by doing something like:

WITH params as (
      SELECT CAST('2015-03-01' as DATE) as date1, CAST('2015-03-31' as DATE) date2
     )
SELECT i.Campaign_id
FROM params CROSS JOIN
     Impressions i
WHERE i.Date >= params.Date1 and i.Date < DATEADD(day, 1, params.Date2)
GROUP BY i.Campaign_id, params.date1, params.date2
HAVING COUNT(DISTINCT i.Date) = 1 + DATEDIFF(day, params.date1, params.date2);

Note: Some would prefer a JOIN to a CROSS JOIN in this case. By habit, I always put a parameters CTE in a query using CROSS JOIN.

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 452947

Assuming DATE is a DATE datatype and has no time component.

DECLARE @Start DATE = '2015-03-01',
        @End   DATE = '2015-03-31'

SELECT Campaign_id
FROM   Impressions
WHERE  Date BETWEEN @Start AND @End
GROUP  BY Campaign_id
HAVING COUNT(DISTINCT Date) = 1 + DATEDIFF(DAY, @Start, @End);

Or a version without the variables

SELECT Campaign_id
FROM   Impressions
       CROSS APPLY (VALUES ({ d '2015-03-01' },
                            { d '2015-03-31' })) V([Start], [End])
WHERE  [Date] BETWEEN [Start] AND [End]
GROUP  BY Campaign_id, [Start], [End]
HAVING COUNT(DISTINCT Date) = 1 + DATEDIFF(DAY, [Start], [End]);

Upvotes: 6

Related Questions