Another efficient way of writing the SQL query hav

Question

I have a query in which I want to show the count of beds needed datewise in a daterange. So, if I have the daterange as '25-nov-2014' to '03-dec-2014', then I want individual daywise count of records and that too in a specific order, means that beds count on '25-nov-2014' would have all the patients being discharged after '25-nov-2014', beds count on '26-nov-2014' would have all the patients being discharged after '26-nov-2014' and so on, similarly beds count on '03-dec-2014' would have all the patients being discharged after '03-dec-2014'.

I have written the query but this is not suitable approach as the data is getting fixed and for long ranges it would have just the repetitive code.

The query is:

SELECT count(*) 
FROM tblMain 
WHERE 
    DischargeDate BETWEEN CONVERT (date, '25-nov-2014') AND CONVERT (date, '03-dec-2014') 
    AND DiscCode = '502' 

SELECT count(*) 
FROM tblMain 
WHERE 
    DischargeDate BETWEEN CONVERT (date, '26-nov-2014') AND CONVERT (date, '03-dec-2014') 
    AND DiscCode = '502' 

SELECT count(*) 
FROM tblMain 
WHERE 
    DischargeDate BETWEEN CONVERT (date, '27-nov-2014') AND CONVERT (date, '03-dec-2014') 
    AND DiscCode = '502' 

SELECT count(*) 
FROM tblMain 
WHERE 
    DischargeDate BETWEEN CONVERT (date, '28-nov-2014') AND CONVERT (date, '03-dec-2014') 
    AND DiscCode = '502' 

SELECT count(*) 
FROM tblMain 
WHERE 
    DischargeDate BETWEEN CONVERT (date, '29-nov-2014') AND CONVERT (date, '03-dec-2014')   
    AND DiscCode = '502' 

  .....

As visible from the script, everywhere the code is repetitive and not a good approach for dynamic date range. So how can I write this query so that I could get the output datewise for all dates in one go and supporting dynamic range?

Answer 1

Have a look at this post : Get a list of dates between two dates using a function

Use the Function ExplodeDates to write a query such as :

Select a.theDate, b.cnt from ExplodeDates('2014-11-25', '2014-12-03') a
outer apply (
  SELECT count(*) cnt FROM tblMain where DiscCode = '502' 
   and 
 case when CONVERT (date, a.TheDate) = '2014-12-03' then 
   case when DischargeDate > CONVERT (date, '03-dec-2014') then 1 else 0 end
 else 
   case when DischargeDate BETWEEN CONVERT (date, a.TheDate) AND CONVERT (date, '2014-12-03') then 1 else 0 end
  end  = 1 
) b