我有一个查询,我想在日期范围内显示日期所需的床位数。 因此,如果我将日期范围设置为'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 ) bHave 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更多推荐
发布评论