SQLTeam.com | Weblogs | Forums

Query Help


#1

I have a table with dates I need to use in a range within query. Please advice how to do multiple in single query.

SELECT COUNT(CID) AS TOTALS, CDESC
FROM TABLE1
WHERE DATE BETWEEN 'TABLE2.StartDate' AND 'TABLE2.EndDate'

TABLE2
Start Date End Date
2016-08-01 2016-08-07
2016-08-08 2016-08-14


#2
SELECT COUNT(CID), startdate, enddate
FROM
	TABLE1  a
	INNER JOIN TABLE2 b ON
		a.date >= b.startdate
		AND a.date <= b.enddate
GROUP BY
	b.startdate,
	b.enddate;	

If there are overlapping dates in TABLE2, the total count can be more than the number of CID's in TABLE1.

If your dates have a time portion to it, you need modify the query a little bit to make sure that you pick up CID's that fall on enddates.


#3

Thank you JamesK did some tweaks and worked perfect.