November 23, 2010 at 2:38 pm
stan-617410 (11/21/2010)
Lutz,Thank you for your questions.
There are two tables - Tenant and Buildings.
The ID in Tenant = ID_U in Buildings.
If a tentant returns they will have a new Tenant record.
CREATE TABLE [dbo].[Tenants](
[ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL
) ON [PRIMARY]
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]
Stan, as others have pointed out, until the table design is correct this is going to be an uphill struggle with no summit. The table Buildings above is no such thing, it's a bastardised join between a Buildings table and an occupancy table. If this is what you are stuck with because it's a third-party database, then commiserations. If this is your design, then please reconsider carefully - any effort you put in now to correct the design will pay you back in spades later.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 23, 2010 at 2:56 pm
stan-617410
Have to attend a meeting and must leave in a few minutes... will attempt to get back to assisting you .. but it might be tomorrow ...
Give serious thoughts to ChrisM@home comments and respond to him as to wether you can or can not do what he/she suggets
November 23, 2010 at 3:38 pm
How does this work for you?
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
DECLARE @Buildings TABLE ([Building_ID] [nvarchar](50) NULL,
[Building] [nvarchar](50) NULL);
DECLARE @Clients TABLE([ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL);
DECLARE @Residency_Dates TABLE([ID_U] [nvarchar](50) NULL,
[Building_ID] [nvarchar](50) NULL,
[Move_in_Date] [datetime] NULL,
[Move_out_Date] [datetime] NULL);
INSERT INTO @Buildings
SELECT 'A', 'Building A' UNION ALL
SELECT 'B', 'Building B' UNION ALL
SELECT 'C', 'Building C' UNION ALL
SELECT 'D', 'Building D';
INSERT INTO @Clients
SELECT 1, 'Joe', 'Crab' UNION ALL
SELECT 2, 'Martha', 'Sawyer' UNION ALL
SELECT 3, 'David', 'Spencer';
INSERT INTO @Residency_Dates
SELECT 1, 'A', '20100715', NULL UNION ALL
SELECT 2, 'A', '20100722', '20100812' UNION ALL
SELECT 2, 'B', '20100813', '20100830' UNION ALL
SELECT 2, 'C', '20100831', '20101018' UNION ALL
SELECT 2, 'D', '20101019', NULL UNION ALL
SELECT 3, 'C', '20100911', '20101101' UNION ALL
SELECT 3, 'D', '20101102', '20101122';
DECLARE @StartDate datetime;
SET @StartDate = DateAdd(year, DateDiff(year, 0, GetDate()), 0);
WITH Tally (N) AS
(
SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.objects
), CTE1 AS
(
SELECT c.First_Name,
c.Last_Name,
c.ID,
b.Building,
b.Building_ID,
r.Move_In_Date,
r.Move_Out_Date,
StartOfMonth = DateAdd(month, t1.N-1, @StartDate),
EndOfMonth = DateAdd(day, -1, DateAdd(month, t1.N, @StartDate)),
MonthNbr = t1.N
FROM @Clients c
JOIN @Residency_Dates r
ON c.ID = r.ID_U
JOIN @Buildings b
ON b.Building_ID = r.Building_ID
JOIN Tally t1
ON t1.N between month(r.move_in_date) and month(coalesce(r.move_out_date, getdate()))
), CTE2 AS
(
SELECT First_Name,
Last_Name,
Building,
MonthNbr,
ID
,StayForMonth = CASE WHEN Move_In_Date > StartOfMonth THEN DateDiff(day, Move_In_Date, COALESCE(Move_Out_Date, EndOfMonth))
ELSE DateDiff(day, StartOfMonth, COALESCE(Move_Out_Date, EndOfMonth))
END + 1
FROM CTE1
)
SELECT First_Name,
Last_Name,
Building,
January = MAX(CASE WHEN MonthNbr = 1 THEN StayForMonth ELSE 0 END),
February = MAX(CASE WHEN MonthNbr = 2 THEN StayForMonth ELSE 0 END),
March = MAX(CASE WHEN MonthNbr = 3 THEN StayForMonth ELSE 0 END),
April = MAX(CASE WHEN MonthNbr = 4 THEN StayForMonth ELSE 0 END),
May = MAX(CASE WHEN MonthNbr = 5 THEN StayForMonth ELSE 0 END),
June = MAX(CASE WHEN MonthNbr = 6 THEN StayForMonth ELSE 0 END),
July = MAX(CASE WHEN MonthNbr = 7 THEN StayForMonth ELSE 0 END),
August = MAX(CASE WHEN MonthNbr = 8 THEN StayForMonth ELSE 0 END),
September = MAX(CASE WHEN MonthNbr = 9 THEN StayForMonth ELSE 0 END),
October = MAX(CASE WHEN MonthNbr = 10 THEN StayForMonth ELSE 0 END),
November = MAX(CASE WHEN MonthNbr = 11 THEN StayForMonth ELSE 0 END),
December = MAX(CASE WHEN MonthNbr = 12 THEN StayForMonth ELSE 0 END)
FROM CTE2
GROUP BY First_Name, Last_Name, Building
ORDER BY Last_Name, First_Name, Building;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 7:28 am
Wayne, Chris and Ron,
The database is from a third party and I'm struggling to make it work. I will take your suggestions and normalize so I can use the code that Wayne has provided.
One question regarding the results... when I run the above scripts, I see that Martha Sawyer has been in building C for 49 days in August and 48 days in September. How would I get those numbers to reflect the actual number of days within the month?
Thank you for your help and patience w/ a newbie.
November 24, 2010 at 11:23 am
stan-617410 (11/24/2010)
One question regarding the results... when I run the above scripts, I see that Martha Sawyer has been in building C for 49 days in August and 48 days in September. How would I get those numbers to reflect the actual number of days within the month?
Whoops - where did that come from? I'll be back shortly with a revision...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 11:35 am
Just needed to revise the case statement... how's this?
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
DECLARE @Buildings TABLE ([Building_ID] [nvarchar](50) NULL,
[Building] [nvarchar](50) NULL);
DECLARE @Clients TABLE([ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL);
DECLARE @Residency_Dates TABLE([ID_U] [nvarchar](50) NULL,
[Building_ID] [nvarchar](50) NULL,
[Move_in_Date] [datetime] NULL,
[Move_out_Date] [datetime] NULL);
INSERT INTO @Buildings
SELECT 'A', 'Building A' UNION ALL
SELECT 'B', 'Building B' UNION ALL
SELECT 'C', 'Building C' UNION ALL
SELECT 'D', 'Building D';
INSERT INTO @Clients
SELECT 1, 'Joe', 'Crab' UNION ALL
SELECT 2, 'Martha', 'Sawyer' UNION ALL
SELECT 3, 'David', 'Spencer';
INSERT INTO @Residency_Dates
SELECT 1, 'A', '20100715', NULL UNION ALL
SELECT 2, 'A', '20100722', '20100812' UNION ALL
SELECT 2, 'B', '20100813', '20100830' UNION ALL
SELECT 2, 'C', '20100831', '20101018' UNION ALL
SELECT 2, 'D', '20101019', NULL UNION ALL
SELECT 3, 'C', '20100911', '20101101' UNION ALL
SELECT 3, 'D', '20101102', '20101122';
DECLARE @StartDate datetime;
SET @StartDate = DateAdd(year, DateDiff(year, 0, GetDate()), 0);
WITH Tally (N) AS
(
SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.objects
), CTE1 AS
(
SELECT c.First_Name,
c.Last_Name,
c.ID,
b.Building,
b.Building_ID,
r.Move_In_Date,
r.Move_Out_Date,
StartOfMonth = DateAdd(month, t1.N-1, @StartDate),
EndOfMonth = DateAdd(day, -1, DateAdd(month, t1.N, @StartDate)),
MonthNbr = t1.N
FROM @Clients c
JOIN @Residency_Dates r
ON c.ID = r.ID_U
JOIN @Buildings b
ON b.Building_ID = r.Building_ID
JOIN Tally t1
ON t1.N between month(r.move_in_date) and month(coalesce(r.move_out_date, getdate()))
), CTE2 AS
(
SELECT First_Name,
Last_Name,
Building,
MonthNbr,
ID
,StayForMonth = CASE WHEN Move_In_Date > StartOfMonth AND Move_out_Date <= EndOfMonth
THEN DateDiff(day, Move_In_Date, Move_Out_Date)
WHEN Move_In_Date > StartOfMonth
THEN DateDiff(day, Move_In_Date, EndOfMonth)
WHEN Move_out_Date > EndOfMonth THEN datediff(day, StartOfMonth, EndOfMonth)
ELSE DateDiff(day, StartOfMonth, COALESCE(Move_Out_Date, GetDate()))
END + 1
FROM CTE1
)
--SELECT * FROM CTE2 ORDER BY ID
SELECT First_Name,
Last_Name,
Building,
January = MAX(CASE WHEN MonthNbr = 1 THEN StayForMonth ELSE 0 END),
February = MAX(CASE WHEN MonthNbr = 2 THEN StayForMonth ELSE 0 END),
March = MAX(CASE WHEN MonthNbr = 3 THEN StayForMonth ELSE 0 END),
April = MAX(CASE WHEN MonthNbr = 4 THEN StayForMonth ELSE 0 END),
May = MAX(CASE WHEN MonthNbr = 5 THEN StayForMonth ELSE 0 END),
June = MAX(CASE WHEN MonthNbr = 6 THEN StayForMonth ELSE 0 END),
July = MAX(CASE WHEN MonthNbr = 7 THEN StayForMonth ELSE 0 END),
August = MAX(CASE WHEN MonthNbr = 8 THEN StayForMonth ELSE 0 END),
September = MAX(CASE WHEN MonthNbr = 9 THEN StayForMonth ELSE 0 END),
October = MAX(CASE WHEN MonthNbr = 10 THEN StayForMonth ELSE 0 END),
November = MAX(CASE WHEN MonthNbr = 11 THEN StayForMonth ELSE 0 END),
December = MAX(CASE WHEN MonthNbr = 12 THEN StayForMonth ELSE 0 END)
FROM CTE2
GROUP BY First_Name, Last_Name, Building
ORDER BY Last_Name, First_Name, Building;
Edit: revised the ELSE condition in the CASE statement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 1:56 pm
Looking good... Martha and David appear on target. Joe has 116 days in August and 85 in September. I wish I understood this better.
Thanks for your help.
November 24, 2010 at 2:21 pm
change the appropriate section in cte2 to
,StayForMonth = CASE WHEN Move_In_Date > StartOfMonth AND Move_out_Date <= EndOfMonth
THEN DateDiff(day, Move_In_Date, Move_Out_Date)
WHEN Move_In_Date > StartOfMonth
THEN DateDiff(day, Move_In_Date, EndOfMonth)
WHEN Move_out_Date > EndOfMonth THEN datediff(day, StartOfMonth, EndOfMonth)
WHEN Move_In_Date < StartOfMonth AND COALESCE(Move_Out_Date, GetDate())>EndOfMonth
THEN DATEDIFF(dd,StartOfMonth,EndOfMonth)
ELSE DateDiff(day, StartOfMonth, COALESCE(Move_Out_Date, GetDate()))
END + 1
I'm sure you'll see the change and why it changed.
Edit: Initially wrong code. corrected
November 24, 2010 at 2:29 pm
:blush: (not again!!!) This should do it this time!
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
DECLARE @Buildings TABLE ([Building_ID] [nvarchar](50) NULL,
[Building] [nvarchar](50) NULL);
DECLARE @Clients TABLE([ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL);
DECLARE @Residency_Dates TABLE([ID_U] [nvarchar](50) NULL,
[Building_ID] [nvarchar](50) NULL,
[Move_in_Date] [datetime] NULL,
[Move_out_Date] [datetime] NULL);
INSERT INTO @Buildings
SELECT 'A', 'Building A' UNION ALL
SELECT 'B', 'Building B' UNION ALL
SELECT 'C', 'Building C' UNION ALL
SELECT 'D', 'Building D';
INSERT INTO @Clients
SELECT 1, 'Joe', 'Crab' UNION ALL
SELECT 2, 'Martha', 'Sawyer' UNION ALL
SELECT 3, 'David', 'Spencer';
INSERT INTO @Residency_Dates
SELECT 1, 'A', '20100715', NULL UNION ALL
SELECT 2, 'A', '20100722', '20100812' UNION ALL
SELECT 2, 'B', '20100813', '20100830' UNION ALL
SELECT 2, 'C', '20100831', '20101018' UNION ALL
SELECT 2, 'D', '20101019', NULL UNION ALL
SELECT 3, 'C', '20100911', '20101101' UNION ALL
SELECT 3, 'D', '20101102', '20101122';
DECLARE @StartDate datetime;
SET @StartDate = DateAdd(year, DateDiff(year, 0, GetDate()), 0);
WITH Tally (N) AS
(
SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.objects
), CTE1 AS
(
SELECT c.First_Name,
c.Last_Name,
c.ID,
b.Building,
b.Building_ID,
r.Move_In_Date,
r.Move_Out_Date,
StartOfMonth = DateAdd(month, t1.N-1, @StartDate),
EndOfMonth = DateAdd(day, -1, DateAdd(month, t1.N, @StartDate)),
MonthNbr = t1.N
FROM @Clients c
JOIN @Residency_Dates r
ON c.ID = r.ID_U
JOIN @Buildings b
ON b.Building_ID = r.Building_ID
JOIN Tally t1
ON t1.N between month(r.move_in_date) and month(coalesce(r.move_out_date, getdate()))
), CTE2 AS
(
SELECT First_Name,
Last_Name,
Building,
MonthNbr,
ID
,StayForMonth = CASE WHEN Move_In_Date > StartOfMonth AND Move_out_Date <= EndOfMonth
THEN DateDiff(day, Move_In_Date, Move_Out_Date)
WHEN Move_In_Date > StartOfMonth
THEN DateDiff(day, Move_In_Date, EndOfMonth)
WHEN Move_out_Date > EndOfMonth
THEN DateDiff(day, StartOfMonth, EndOfMonth)
WHEN Move_out_Date IS NULL AND month(StartOfMonth) = month(GetDate())
THEN DateDiff(day, StartOfMonth, GetDate())
ELSE DateDiff(day, StartOfMonth, COALESCE(Move_Out_Date, EndOfMonth))
END + 1
FROM CTE1
)
SELECT First_Name,
Last_Name,
Building,
January = MAX(CASE WHEN MonthNbr = 1 THEN StayForMonth ELSE 0 END),
February = MAX(CASE WHEN MonthNbr = 2 THEN StayForMonth ELSE 0 END),
March = MAX(CASE WHEN MonthNbr = 3 THEN StayForMonth ELSE 0 END),
April = MAX(CASE WHEN MonthNbr = 4 THEN StayForMonth ELSE 0 END),
May = MAX(CASE WHEN MonthNbr = 5 THEN StayForMonth ELSE 0 END),
June = MAX(CASE WHEN MonthNbr = 6 THEN StayForMonth ELSE 0 END),
July = MAX(CASE WHEN MonthNbr = 7 THEN StayForMonth ELSE 0 END),
August = MAX(CASE WHEN MonthNbr = 8 THEN StayForMonth ELSE 0 END),
September = MAX(CASE WHEN MonthNbr = 9 THEN StayForMonth ELSE 0 END),
October = MAX(CASE WHEN MonthNbr = 10 THEN StayForMonth ELSE 0 END),
November = MAX(CASE WHEN MonthNbr = 11 THEN StayForMonth ELSE 0 END),
December = MAX(CASE WHEN MonthNbr = 12 THEN StayForMonth ELSE 0 END)
FROM CTE2
GROUP BY First_Name, Last_Name, Building
ORDER BY Last_Name, First_Name, Building;
@Lutz: thanks, but it's reporting the days in the following months as zero, not the proper days. It comes down to when do we need to use GetDate(), and when to use EndOfMonth in the DateDiff calculation. Argh!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 2:42 pm
WayneS (11/24/2010)
:blush: (not again!!!) This should do it this time!...
@Lutz: thanks, but it's reporting the days in the following months as zero, not the proper days. It comes down to when do we need to use GetDate(), and when to use EndOfMonth in the DateDiff calculation. Argh!
You didn't see my revised version, did you?
This thread is an "excellent example" to describe what happens if an OP decide not to post ready to use sample data and expected result: a waste of valuable resources (plus the time I spent ;-)).
November 24, 2010 at 3:44 pm
Wayne, Thank you for your persistence and patience.
November 24, 2010 at 4:00 pm
LutzM (11/24/2010)
WayneS (11/24/2010)
:blush: (not again!!!) This should do it this time!...
@Lutz: thanks, but it's reporting the days in the following months as zero, not the proper days. It comes down to when do we need to use GetDate(), and when to use EndOfMonth in the DateDiff calculation. Argh!
You didn't see my revised version, did you?
No, I saw the original where you were using the -1. Sorry... and again, thanks for stepping in and helping. I really do appreciate it! 🙂
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 4:03 pm
stan-617410 (11/24/2010)
Wayne, Thank you for your persistence and patience.
No problem. To be fair, Lutz and Ron were pretty instrumental in getting this solved also!
Is this working right for you now?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 4:18 pm
WayneS (11/24/2010)
stan-617410 (11/24/2010)
Wayne, Thank you for your persistence and patience.No problem. To be fair, Lutz and Ron were pretty instrumental in getting this solved also!
Is this working right for you now?
I'd leave it to Ron and you. Without Ron, this would be a dead thread from the very beginning. And without you, we still wouldn't have any sample data to play with.... My part? Just requesting, complaining, whining, and being ignored by the OP...;-)
November 24, 2010 at 4:52 pm
See??? Instrumental! :-D:-P
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply