另一种编写具有日期范围数据的SQL查询的有效方法(Another efficient way of writing the SQL query having data in date range)

我有一个查询，我想在日期范围内显示日期所需的床位数。因此，如果我将日期范围设置为'25 -nov-2014'到'03 -dec-2014'，那么我想要按个别日期计数记录，并且按特定顺序排列，这意味着床数计入'25 -nov- 2014年“所有患者将在'25 -nov-2014'后出院，'26 -nov-2014'的病床计数将使所有患者在'26 -nov-2014'之后出院等等，同样的病床计数'03 -dec-2014'将使所有患者在'03 -dec-2014'后出院。

我已经编写了查询，但这不是合适的方法，因为数据是固定的，对于长距离，它只有重复的代码。

查询是：

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' .....

从脚本中可以看出，代码在任何地方都是重复的，而不是动态日期范围的好方法。那么我该如何编写这个查询，以便我可以一次性获取所有日期的输出日期并支持动态范围？

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?

最满意答案

看看这篇文章：使用函数获取两个日期之间的日期列表

使用函数ExplodeDates编写查询，例如：

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

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

更多推荐