Geek Answers Handbook

SQL: where is the item

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

The above query gives me a result for Campaign_id that was active on any day between 2015-03-01 and 2015-03-31.

I want the result set to contain Campaign_id if they were active on all dates between 2015-03-01 and 2015-03-31.

How can i do this?

+5
source share
2 answers

Assuming DATE is a DATE type 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 version without 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]);
+6
source

Using the HAVING 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 look at this blog post from Aaron Betrand to understand why using BETWEEN for dates is a bad idea.

You can make a request to specify only dates once, 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 people prefer JOIN to CROSS JOIN in this case. Out of habit, I always set CTE parameters in a query using CROSS JOIN .

+4
source

More articles:


All Articles