title: Difficult SQL Query and Subquery For Report
body: <p>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.... </p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>I have created a SQL fiddle to make it easier.</p>
  
  <p><a href="http://www.sqlfiddle.com/#!6/024f5/1" rel="nofollow">SQL Fiddle</a></p>
  
  <p>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:</p>
  
  <p>Result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Please note that I don't need the output in above format as long as I can receive suitable EpisodeIDs for qualified records.</p>
  
  <p>I am using following query but I don't know how to extract records for re-occupying bed records:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>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 &gt;= @StartDate and ET.StartDate &lt;= @EndDate</code></pre>
  </div>
  </div>
  </p>
  
  <p>Thanks for you help.</p>