CODEWITHSUNDEEP
sqlsql-server-2008-r2
learner
learner

Reputation: 541

Difficult SQL Query and Subquery For Report

I have bed management module where by a client visits us and we assign a bed to the client. When we assign a bed to a client, the system starts a new episode by adding an entry into the episode table, the system also adds the start date of the visit in the episode table. When the client leaves, then the system adds a new record in the Discharge table and it also adds the end date (the date client leaves). A client may come back and in that case the system starts a second episode in the Episode table and so on....

A client can have multiple episodes and each episode will have 0 or 1 discharge record (depending on whether client is discharged or still occupying a bed).

I need to write a query to receive all those episodes/clients that exist (i.e client occupied a bed) during a specified 12 months period, and the second requirement is out of all the data that fulfilled the first query's requirement, I need to get all those clients/episodes which re-occupied the bed within 6 months of their end date.

I have created a SQL fiddle to make it easier.

SQL Fiddle

If I run this query in/for July 2007 then the start and end date of the 12 month period is 01/01/2006 - 31/12/2006. For Aug 2007, the 12 month period is 01/02/2007 - 31/01/2007 and for Sep 2007, the 12 month period is 01/03/2007 - 28/02/2007. I should receive the following data for each month:

Result:

EpisodesOccupyingBed    Didnt Re-Occupied   Re-Occupied
EpisodeID1                                  X
EpisodeID2              X   
EpisodeID3      
Total Jul 07   3        1                      1

EpisodesOccupyingBed    Didnt Re-Occupied   Re-Occupied
EpisodeID4      
EpisodeID2              X   
EpisodeID3      
Total Aug 07  3         1                      0

EpisodesOccupyingBed    Didnt Re-Occupied   Re-Occupied
EpisodeID4      
EpisodeID2              X   
EpisodeID3      
Total Aug 07  3         1                      0

Please note that I don't need the output in above format as long as I can receive suitable EpisodeIDs for qualified records.

I am using following query but I don't know how to extract records for re-occupying bed records:

Declare @StartDate DateTime
Declare @EndDate DateTime
Declare @Month varchar(5)
set @Month = 'Jul07' -- set @Month='Aug07' -- set @Month = 'Sep07'
if(@Month = 'Jul07') begin -- report run on July 2007
  set @StartDate = 'January, 01 2006' set @EndDate = 'December, 31 2006'
  end
else if(@Month = 'Aug07')  begin -- report run on July 2007
  set @StartDate = 'February, 01 2006' set @EndDate = 'January, 31 2007'
  end
else if(@Month = 'Sep07') begin
  set @StartDate = 'March, 01 2006' set @EndDate = 'February, 28 2007'
  end
select * from ClientTable as CT inner join EpisodeTable as ET on 
CT.ClientIDCSV = ET.ClientIDCSV
left outer join DischargeTable as DT on 
ET.EpisodeIDCSV=DT.EpisodeIDCSV where
ET.StartDate >= @StartDate and ET.StartDate <= @EndDate

Thanks for you help.

Upvotes: 0

Views: 108

Answers (1)

TommCatt
TommCatt

Reputation: 5636

You need to list all the episodes started during the previous year and attach any new episodes for the same clients that started after the first episodes end date. From that it's easy to get the difference in months between the end of the first episode and the start of the next episode and react accordingly.

EDIT: Here's a revised query based on new information.

select  e.EpisodeIDCSV,
        case when DateDiff( month,  d.EndDate, e1.StartDate ) > 6 then 'X' end as NoReoccupy,
        case when DateDiff( month,  d.EndDate, e1.StartDate ) <= 6 then 'X' end as Reoccupy
from    Clients c
join    Episodes e
    on  e.ClientIDCSV = c.ClientIDCSV
left join Discharges d
    on  d.EpisodeIDCSV = e.EpisodeIDCSV
join Episodes e1
    on  e1.ClientIDCSV = c.ClientIDCSV
    and e1.StartDate > e.StartDate
left join Discharges d1
    on  d1.EpisodeIDCSV = e1.EpisodeIDCSV
where   e.StartDate >= @StartDate
    and e.StartDate < @EndDate
order by c.EpisodeIDCSV, e.StartDate;

Here's SQL Fiddles for Jul07, Aug07 and Sep07. Notice Episode 5 is now showing up in all three time periods as an "intreat" condition. At first I thought it was a problem, but episode 5 starts in all three time periods and is not discharged within any of them. So it qualifies as "in treatment" but you don't show it in any of your examples. On what basis do you disqualify it?

Upvotes: 1

Related Questions