title: SQL calculate number of days of residency by month, by user, by location
body: <p>I'm working on a query for a rehab organization where tenants (client/patients) live in a building when they first arrive, as they progress in their treatment they move to another building and as they near the end of treatment they are in a third building.</p>
  
  <p>For funding purposes we need to know how many nights a tenant spent in each building in each month. 
  I can use DateDiff to get the total number of nights, but how do I get the total for each client in each month in each building? </p>
  
  <p>For example, John Smith is in Building A 9/12-11/3; moves to Building B 11/3-15; moves to Building C on and is still there: 11/15 - today</p>
  
  <p>What query returns a result that show the number of nights he spent in:
  Building A in Septmeber, October and November. 
  Buidling B in November
  Building C in November </p>
  
  <p>Two tables hold the client's name, building name and move-in date and move-out date</p>
  
  <pre><code>CREATE TABLE [dbo].[clients](
  [ID] [nvarchar](50) NULL,
  [First_Name] [nvarchar](100) NULL,
  [Last_Name] [nvarchar](100) NULL
  ) ON [PRIMARY]
  
  --populate w/ two records  
  insert into clients (ID,First_name, Last_name)
  values ('A2938', 'John', 'Smith')
  
  insert into clients (ID,First_name, Last_name)
  values ('A1398', 'Mary', 'Jones')
  
  
  
  
  CREATE TABLE [dbo].[Buildings](
  [ID_U] [nvarchar](50) NULL,
  [Move_in_Date_Building_A] [datetime] NULL,
  [Move_out_Date_Building_A] [datetime] NULL,
  [Move_in_Date_Building_B] [datetime] NULL,
  [Move_out_Date_Building_B] [datetime] NULL,
  [Move_in_Date_Building_C] [datetime] NULL,
  [Move_out_Date_Building_C] [datetime] NULL,
  [Building_A] [nvarchar](50) NULL,
  [Building_B] [nvarchar](50) NULL,
  [Building_C] [nvarchar](50) NULL
  ) ON [PRIMARY]
  
  
  -- Populate the tables with two records
  insert into buildings (ID_U,Move_in_Date_Building_A,Move_out_Date_Building_A, Move_in_Date_Building_B,
  Move_out_Date_Building_B, Move_in_Date_Building_C, Building_A, Building_B, Building_C)
  VALUES ('A2938','2010-9-12', '2010-11-3','2010-11-3','2010-11-15', '2010-11-15', 'Kalgan', 'Rufus','Waylon')
  
  
  insert into buildings (ID_U,Move_in_Date_Building_A,Building_A)
  VALUES ('A1398','2010-10-6', 'Kalgan')
  </code></pre>
  
  <p>Thanks for your help. </p>