CODEWITHSUNDEEP
sql-serverdatabasesqlperformance
Rahul Kumar
Rahul Kumar

Reputation: 1

Query with multiple date range query

I have to collect data from one table base on date range. Right now am using multiple select query to get this count Ex

Select  @count1 =  count(*) 
from table
where  createddate between @date1 and @date2 and status in (1,2)

Select  @count2 =  count(*) 
from table
where  createddate between @date2 and @date3 and status in (3,4)

Select @count23 =  count(*) 
from table
where  createddate between @date3 and @date4 and status in (5,6)

Select count1, count2, count3

How to merge all above 3 queries? Will it increase performance?

Upvotes: 0

Views: 68

Answers (1)

Thom A
Thom A

Reputation: 95561

Use conditional aggregation:

SELECT COUNT(CASE WHEN createdate BETWEEN @Date1 AND @Date2 AND [Status] IN (1,2) THEN 1 END) AS Count1,
       COUNT(CASE WHEN createdate BETWEEN @Date2 AND @Date3 AND [Status] IN (3,4) THEN 1 END) AS Count2
FROM dbo.YourTable;

Upvotes: 1

Related Questions