医院入住率报告
医院入住率报告
提问于 2015-07-31 17:45:59
我在用sql2008r2，我需要一份床位占用率报告。为此，我刚刚创建了一个视图和字段，如下所示：
registrationid,name,admitdate,dischargedate,accomodationdate,branchid。
我需要如下所示的分支占用结果：
Date,openingbalance,totaladmission,totaldischarge,Closingbalance。
在这里，期初余额意味着，例如，如果我拿今天的入住率报告，那么期初余额就是昨天的结算日。
还有closing balance means Opening +totaladmission-totaldischarge。
我找到totaladmission和totaldischarge了。
如何获取同行期初余额？
PatientCode PatientName AdmissionDate DischargeDate Roomlogdate RoomName HospitalBranchID RoomTypeMasterName IsDischarged RegistrationID
N294 AAD 06-04-2015 24-04-2015 24-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 23-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 22-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 21-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 20-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 19-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 18-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 17-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 16-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 15-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 14-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 13-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 12-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 11-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 10-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 09-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 08-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 07-04-2015 Own-11 49 Own 1 157480
N294 AAD 06-04-2015 24-04-2015 06-04-2015 Own-11 49 Own 1 157480
N294 AAD 31-03-2014 16-04-2014 16-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 15-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 14-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 13-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 12-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 11-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 10-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 09-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 08-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 07-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 06-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 05-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 04-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 03-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 02-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 01-04-2014 Own-1 49 Own 1 88975
N294 AAD 31-03-2014 16-04-2014 31-03-2014 Own-1 49 Own 1 88975
N294 AAD 25-03-2013 10-04-2013 10-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 09-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 08-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 07-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 06-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 05-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 04-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 03-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 02-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 01-04-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 31-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 30-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 29-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 28-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 27-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 26-03-2013 Own-4 49 Own 1 37204
N294 AAD 25-03-2013 10-04-2013 25-03-2013 Own-4 49 Own 1 37204
1303-94 bb 14-03-2013 25-03-2013 25-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 24-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 23-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 22-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 21-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 20-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 19-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 18-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 17-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 16-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 15-03-2013 306-2 49 Ward 1 36581
1303-94 bb 14-03-2013 25-03-2013 14-03-2013 306-2 49 Ward 1 36581
1503-863 ag 31-03-2015 19-04-2015 19-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 18-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 17-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 16-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 15-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 14-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 13-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 12-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 11-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 10-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 09-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 08-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 07-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 06-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 05-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 04-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 03-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 02-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 01-04-2015 PH-1A-3 49 Outside Rooms 1 156582
1503-863 ag 31-03-2015 19-04-2015 31-03-2015 PH-1A-3 49 Outside Rooms 1 156582
1105-637 abd 26-12-2012 09-01-2013 09-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 08-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 07-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 06-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 05-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 04-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 03-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 02-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 01-01-2013 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 31-12-2012 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 30-12-2012 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 29-12-2012 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 28-12-2012 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 27-12-2012 4257 49 Double Room With TV 1 29661
1105-637 abd 26-12-2012 09-01-2013 26-12-2012 4257 49 Double Room With TV 1 29661
1308-80 arp 09-07-2015 23-07-2015 23-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 22-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 21-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 20-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 19-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 18-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 17-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 16-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 15-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 14-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 13-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 12-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 11-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 10-07-2015 5019-1 49 V 1 177337
1308-80 arp 09-07-2015 23-07-2015 09-07-2015 5019-1 49 V 1 177337
1308-80 arp 04-08-2014 19-08-2014 19-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 18-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 17-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 16-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 15-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 14-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 13-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 12-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 11-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 10-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 09-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 08-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 07-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 06-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 05-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 04-08-2014 19-08-2014 04-08-2014 G-1-2B 49 Outside Rooms 1 111798
1308-80 arp 08-08-2013 20-08-2013 20-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 19-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 18-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 17-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 16-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 15-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 14-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 13-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 12-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 11-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 10-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 09-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1308-80 arp 08-08-2013 20-08-2013 08-08-2013 GH1-2A 49 Guest House-Floor1 1 51580
1505-1041 aan 26-05-2015 10-06-2015 10-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 09-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 08-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 07-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 06-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 05-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 04-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 03-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 02-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 01-06-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 31-05-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 30-05-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 29-05-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 28-05-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 27-05-2015 359 49 Double Room 1 168564
1505-1041 aan 26-05-2015 10-06-2015 26-05-2015 359 49 Double Room 1 168564
1308-183 aavas 30-06-2014 17-07-2014 17-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 16-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 15-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 14-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 13-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 12-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 11-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 10-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 09-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 08-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 07-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 06-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 05-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 04-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 03-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 02-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 01-07-2014 4365 49 Double Room 1 105825
1308-183 aavas 30-06-2014 17-07-2014 30-06-2014 4365 49 Double Room 1 105825回答 1
Stack Overflow用户
发布于 2015-07-31 19:55:42
如果您创建了一个新的日期表，那么您可以获得每天的期初余额，如下所示：
SELECT
Date
,count(registrationid)
FROM
(
select
*
FROM
Dates as d left outer join
Table as t
on t.admitdate < d.Date
and t.DischargeDate >= d.Date
) as a
GROUP BY
Date日期表：
Date
------------
01/01/2014 -- start of analysis period
02/01/2014
..
..
31/12/2015 -- end of analysis period对于在该日期之前入院并在当天或之后出院的每个患者/用户，内部查询每天都有一行。
group by的外部查询只是按天聚合数据。
要获得期末余额，只需稍微调整一下，使另一个join子句中的or等于wan：
SELECT
Date
,count(registrationid)
FROM
(
select
*
FROM
Dates as d left outer join
Table as t
on t.admitdate <= d.Date
and t.DischargeDate > d.Date
) as a
GROUP BY
Date要获得一个表中的所有数据，您可以将许多子查询连接在一起，包括以下查询：
SELECT
D.Date
,OpeningBalance
,Admissions
,Discharges
FROM
Dates as D
INNER JOIN
(
SELECT
Date
,count(registrationid) as OpeningBalance
FROM
(
select
*
FROM
Dates as d left outer join
Table as t
on t.admitdate < d.Date
and t.DischargeDate >= d.Date
) as a
GROUP BY
Date
) as OB
on D.Date = OB.Date
INNER JOIN
(
SELECT
Date
,count(registrationid) as Admissions
FROM
(
select
*
FROM
Dates as d left outer join
Table as t
on t.admitdate = d.Date
) as a
GROUP BY
Date
) AS A
on D.Date = A.Date
INNER JOIN
(
SELECT
Date
,count(registrationid) as Discharges
FROM
(
select
*
FROM
Dates as d left outer join
Table as t
on t.DischargeDate = d.Date
) as a
GROUP BY
Date
) AS DC
on D.Date = DC.Date我相信还有其他方法可以做到这一点，但我喜欢有一个日期表的可靠性。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31743124
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号