SQL calculate number of days of residency by month, by user, by location

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.

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?

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

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

Two tables hold the client's name, building name and move-in date and move-out date

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','W开发者_Python百科aylon')


insert into buildings (ID_U,Move_in_Date_Building_A,Building_A)
VALUES ('A1398','2010-10-6', 'Kalgan')

Thanks for your help.

I'd use a properly normalized database schema, your Buildings table is not useful like this. After splitting it up I believe that getting your answer will be pretty easy.

Edit (and updated): Here's a CTE which will take this strange table structure and split it into a more normalized form, displaying the user id, building name, move in and move out dates. By grouping on the ones you want (and using DATEPART() etc.) you should be able to get the data you need with that.

WITH User_Stays AS (
    SELECT
        ID_U,
        Building_A Building,
        Move_in_Date_Building_A Move_In,
        COALESCE(Move_out_Date_Building_A, CASE WHEN ((Move_in_Date_Building_B IS NULL) OR (Move_in_Date_Building_C<Move_in_Date_Building_B)) AND (Move_in_Date_Building_C>Move_in_Date_Building_A) THEN Move_in_Date_Building_C WHEN Move_in_Date_Building_B>=Move_in_Date_Building_A THEN Move_in_Date_Building_B END, GETDATE()) Move_Out
    FROM dbo.Buildings 
    WHERE Move_in_Date_Building_A IS NOT NULL   
    UNION ALL
    SELECT
        ID_U, 
        Building_B,
        Move_in_Date_Building_B, 
        COALESCE(Move_out_Date_Building_B, CASE WHEN ((Move_in_Date_Building_A IS NULL) OR (Move_in_Date_Building_C<Move_in_Date_Building_A)) AND (Move_in_Date_Building_C>Move_in_Date_Building_B) THEN Move_in_Date_Building_C WHEN Move_in_Date_Building_A>=Move_in_Date_Building_B THEN Move_in_Date_Building_A END, GETDATE())
    FROM dbo.Buildings 
    WHERE Move_in_Date_Building_B IS NOT NULL
    UNION ALL
    SELECT
        ID_U, 
        Building_C,
        Move_in_Date_Building_C, 
        COALESCE(Move_out_Date_Building_C, CASE WHEN ((Move_in_Date_Building_B IS NULL) OR (Move_in_Date_Building_A<Move_in_Date_Building_B)) AND (Move_in_Date_Building_A>Move_in_Date_Building_C) THEN Move_in_Date_Building_A WHEN Move_in_Date_Building_B>=Move_in_Date_Building_C THEN Move_in_Date_Building_B END, GETDATE())
    FROM dbo.Buildings
    WHERE Move_in_Date_Building_C IS NOT NULL
)
SELECT *
FROM User_Stays
ORDER BY ID_U, Move_In

This query run on your sample data produces he following output:

ID_U     Building    Move_In                 Move_Out
-------- ----------- ----------------------- -----------------------
A1398    Kalgan      2010-10-06 00:00:00.000 2010-11-23 18:35:59.050
A2938    Kalgan      2010-09-12 00:00:00.000 2010-11-03 00:00:00.000
A2938    Rufus       2010-11-03 00:00:00.000 2010-11-15 00:00:00.000
A2938    Waylon      2010-11-15 00:00:00.000 2010-11-23 18:35:59.050

(4 row(s) affected)

As you can see, from here on it will be much easier to isolate the days per patient or building, and also to find the records for specific months and calculate the correct stay duration in that case. Note that the CTE displays the current date for patients which are still in a building.

Edit (again): In order to get all months including their start and end dates for all relevant years, you can use a CTE like this:

WITH User_Stays AS (             
        [...see above...]
    )
,
    Months AS (          
        SELECT  m.IX,
                y.[Year], dateadd(month,(12*y.[Year])-22801+m.ix,0) StartDate, dateadd(second, -1, dateadd(month,(12*y.[Year])-22800+m.ix,0)) EndDate
                FROM    (            
                    SELECT  1 IX UNION ALL 
                    SELECT  2 UNION ALL 
                    SELECT  3 UNION ALL 
                    SELECT  4 UNION ALL 
                    SELECT  5 UNION ALL 
                    SELECT  6 UNION ALL 
                    SELECT  7 UNION ALL 
                    SELECT  8 UNION ALL 
                    SELECT  9 UNION ALL 
                    SELECT  10 UNION ALL 
                    SELECT  11 UNION ALL 
                    SELECT  12 
                )
        m 
            CROSS JOIN (             
                    SELECT  Datepart(YEAR, us.Move_In) [Year] 
                    FROM    User_Stays us UNION 
                    SELECT  Datepart(YEAR, us.Move_Out) 
                    FROM    User_Stays us 
                )
        y 
    )
SELECT  * 
FROM    months;

So since we now have a tabular representation of all date ranges which can be of interest, we simply join this together:

WITH User_Stays AS ([...]),
Months AS ([...])
SELECT  m.[Year],
    DATENAME(MONTH, m.StartDate) [Month],
    us.ID_U,
    us.Building,
    DATEDIFF(DAY, CASE WHEN us.Move_In>m.StartDate THEN us.Move_In ELSE m.StartDate END, CASE WHEN us.Move_Out<m.EndDate THEN us.Move_Out ELSE DATEADD(DAY, -1, m.EndDate) END) Days 
FROM    Months m 
JOIN User_Stays us ON (us.Move_In < m.EndDate) AND (us.Move_Out >= m.StartDate)
ORDER BY m.[Year],
    us.ID_U,
    m.Ix,
    us.Move_In

Which finally produces this output:

Year        Month        ID_U     Building   Days
----------- ------------ -------- ---------- -----------
2010        October      A1398    Kalgan     25
2010        November     A1398    Kalgan     22
2010        September    A2938    Kalgan     18
2010        October      A2938    Kalgan     30
2010        November     A2938    Kalgan     2
2010        November     A2938    Rufus      12
2010        November     A2938    Waylon     8

-- set the dates for which month you want

Declare @startDate datetime
declare @endDate datetime

set @StartDate = '09/01/2010'
set @EndDate = '09/30/2010'


select 
-- determine if the stay occurred during this month
    Case When @StartDate <= Move_out_Date_Building_A and @EndDate >= Move_in_Date_Building_A
         Then 
                  (DateDiff(d, @StartDate , @enddate+1) 
                   )
-- drop the days off the front
                - (Case When @StartDate <  Move_in_Date_Building_A
                       Then datediff(d, @StartDate, Move_in_Date_Building_A)
                       Else 0
                  End)
--drop the days of the end
                - (Case When @EndDate > Move_out_Date_Building_A
                       Then datediff(d, @EndDate,  Move_out_Date_Building_A)
                       Else 0
                  End)
        Else 0
    End AS Building_A_Days_Stayed
from Clients c 
inner join Buildings b
on c.id = b.id_u

Try using a date table. For example, you could create one like so:

CREATE TABLE Dates
(
  [date]    datetime,
  [year]    smallint,
  [month]   tinyint,
  [day]     tinyint
)

INSERT INTO Dates(date)
SELECT dateadd(yy, 100, cast(row_number() over(order by s1.object_id) as datetime))
FROM sys.objects s1
  CROSS JOIN sys.objects s2

UPDATE Dates
SET [year] = year(date),
    [month] = month(date),
    [day] = day(date)

Just modify the initial Dates population to meet your needs (on my test instance, the above yielded dates from 2000-01-02 to 2015-10-26). With a dates table, the query is pretty straight forward, something like this:

select c.First_name, c.Last_name,
    b.Building_A BuildingName, dA.year, dA.month, count(distinct dA.day) daysInBuilding
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dA on dA.date between b.Move_in_Date_Building_A and isnull(b.Move_out_Date_Building_A, getDate())
group by c.First_name, c.Last_name,
    b.Building_A, dA.year, dA.month
UNION
select c.First_name, c.Last_name,
    b.Building_B, dB.year, dB.month, count(distinct dB.day)
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dB on dB.date between b.Move_in_Date_Building_B and isnull(b.Move_out_Date_Building_B, getDate())
group by c.First_name, c.Last_name,
    b.Building_B, dB.year, dB.month
UNION
select c.First_name, c.Last_name,
    b.Building_C, dC.year, dC.month, count(distinct dC.day)
from clients c
    join Buildings b on c.ID = b.ID_U
    left join Dates dC on dC.date between b.Move_in_Date_Building_C and isnull(b.Move_out_Date_Building_C, getDate())
group by c.First_name, c.Last_name,
    b.Building_C, dC.year, dC.month

If you can't restructure the Building table you can create a query that will normalize it for you and allow for easier calculations:

SELECT "A" as Building, BuidlingA as Name, Move_in_Date_Building_A as MoveInDate, 
Move_out_Date_Building_A As MoveOutDate
UNION
SELECT "B", BuidlingB, Move_in_Date_Building_B, Move_out_Date_Building_B 
 UNION
SELECT "C", BuidlingC, Move_in_Date_Building_C, Move_out_Date_Building_C