-3

I am working on SQL and came across one scenario that needs to build in SQL. Below is scenario:

PatientID AdmitDate          DischargeDate
12        7/24/2017 09:45    7/24/2017 11:01
13        7/21/2016 08:37    7/22/2017 00:15

I want result set as below:

enter image description here

  • For patientID 13, count is calculated in first 2 rows and
  • For patientid 12, count is calculated in last row.
xQbert
  • 34,733
  • 2
  • 41
  • 62
Kshama Shah
  • 1
  • 1
  • 3
    Which RDBMS are you using? Please amend the tags, unless you're specifically looking for an answer that works with more than one type – Bridge Aug 16 '17 at 13:13
  • there can be only patient at one time? – Whencesoever Aug 16 '17 at 13:14
  • 1
    have you tried to write anything for this yet? If so please show it. Note that it may not be possible to directly get your desired output layout using SQL. You probably need to do that in your UI layer. In the output shown, how can be see which record is for which patient? it's not clear. Or is it irrelevant? e.g. If the times for the patients overlapped by 1 hour, would we expect to see a "2" displayed for that hour, because there were 2 patients being treated at that time? Again it's not clear. I think we need some better sample data in order to to get this right – ADyson Aug 16 '17 at 13:14
  • Your result image doesn't show what lines associate to what patient. Does that matter? It can be inferred from date; but if patients dates overlap you'd have problems. – xQbert Aug 16 '17 at 13:15
  • 2
    This should indeed probably be done in your application layer, not the database... – Milney Aug 16 '17 at 13:19
  • I am Using Microsoft SQL Server. – Kshama Shah Aug 17 '17 at 09:00
  • I dont need to display patients details. I need cumulative count of a patient. – Kshama Shah Aug 17 '17 at 09:01
  • lets say, There is one patient A, admitted on 7/21 8:37 and discharged on 7/21 11:39. So I need to count this patient as 1 in each column 08:01-09:00, 09:01-10:00, 10:01-11:00 and 11:01-12:00 for row/date 7/21. Why only on these column because admitted hour is 8 and discharged hour is 11 so will count/add 1 for this patient between 8 to 11 hour columns. – Kshama Shah Aug 17 '17 at 09:04
  • And there is one patient B, admitted on 7/21 8:00 and discharged on 7/21 13:01 then this patient will be counted/added as 1 in 08:01-09:00, 09:01-10:00, 10:01-11:00 , 11:01-12:00, 12:01 - 13:00 and 13:01 - 14:00 for date 7/21. So final output for date 7/21 will be: columns 08:01-09:00, 09:01-10:00, 10:01-11:00 and 11:01-12:00 will have 2 value and 12:01 to 13:00 and 13:01 - 14:00 will have 1 value. – Kshama Shah Aug 17 '17 at 09:10
  • Hope this clears what I wants to explain. This may have some issue in explanation as I am writing on your portal for first time. – Kshama Shah Aug 17 '17 at 09:11
  • @KshamaShah Thanks for the update, however please edit the question itself with extra information like this, and use the formatting tools to display it clearly and simply for readers to understand. Info in comments like this can be missed by others and is also difficult to comprehend quickly and clearly due to the lack of formatting. – ADyson Aug 17 '17 at 09:26

1 Answers1

-1

Well, that looks like whatever you do will be slow. I think I'd use a tally table. The table, instead of just containing the usual n years worth of dates / days / day of week etc. would also contain one record for each hour in the day. The Primary Key would represent one of those segments.

Then you could pass the admission date and discharge date for a patient to a function that would return a list, or range, of the hours that the patient is in for. So, Patient 13 might get a return value of (for example) 1500,1517 (i.e the patient was in for 17 hours and you will know the date and time because 1500 will be the Primary Key of a record that gives you the date and hour of the day he was admitted). Patient 12 would (to continue the example) return a value of 1544,1546

You could then build the dataset from Date A to Date B by returning all the data between those dates from the tally table and then check whether each hour is a yes or no for a particular patient.

The display of the data - your desired result set - I would do in somewhere else. I'd probably return two datasets. One would be used to build your table structure. The other would be used to decide whether to put a '1' in the box or not. You could do your summing on the fly.

I imagine this would get interesting with patients on the same dates ... you'd have to show each patient separately?

Martin Smellworse
  • 1,702
  • 3
  • 28
  • 46