November 21, 2010 at 9:17 am
I need to calculate the number of nights a tenant rents an apartment each month, but I don't always have an end date since the tenant may still be residing.
What I'd like to return the name of the tentant and number of nights each month. I know the grid doesn't work... but I hope you get the idea of how I'd like the results to appear.
Name 8/2010 9/2010 10/2010 11/2010
John 15 30 31 21 (November calculated on Today)
Steve 22 28 0 0
Thanks.
November 21, 2010 at 10:15 am
In order to receive tested help, please post table definition, some sample data in an easy to use format following the procedure listed in article referenced by clicking on the first link in my signature block and the required result based on the sample data.
Question - does the rental start on the first day of the month for the initial rental. From your example for John, that does not appear to be the case.
November 21, 2010 at 2:42 pm
The question is actually larger than I originally described. I need to find out how many nights an individual stayed in different buildings over a period of months.
CREATE TABLE tenant
(First_Name nvarchar(50),
Last_Name nvarchar(50),
Move_in_Date_Building_A datetime,
Move_out_Date_Building_A datetime,
Move_in_Date_Building_B datetime,
Move_out_Date_Building_B datetime,
Move_in_Date_Building_C datetime,
Move_out_Date_Building_C datetime)
Insert into tenant(first_name, last_name, 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,Move_out_Date_Building_C )
values ('John', 'Smith','2010-6-1', '2010-8-8','2010-8-8','2010-9-30', '2010-9-30')
I need know the number of nights that John stayed in each building for June, July, August, September, October and November.
In this example the tenant still resides in Building C... so the Moved_Out_Date_Building_C is NULL.
The tenant can move-in or move-out on any day of the month.
Thanks for your help.
November 21, 2010 at 3:23 pm
Is this your actual table design? :pinch:
You should really consider to normalize your table structure.
You'd need separate tables for Persons, Buildings and BuildingsOccupied.
Based on that, the query becomes rather simple.
Regarding your current design:
How would you deal with a new building (BuildingD)? or what would you do if the person would jump back and forth between buidlng A and B?
November 21, 2010 at 5:33 pm
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]
-- Populate the tables
insert into Tenants (ID,first_name, last_name)values ('123','John','Smith')
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)
VALUES ('123','2010-6-1', '2010-8-8','2010-8-8','2010-9-30', '2010-9-30')
How can I get the number of days the tenant was in a building in each month? It's easy to use 'DateDiff 'if there is a move-in and move-out date, but how do you calculate the number of days when there is no move-out date?
Thank you
November 23, 2010 at 8:17 am
I have a couple more questions and maybe I'm just not understanding the problem totally...
Lets say person A moves into your building A on 12/15/2010.
On 12/31/2010 - You want to run a report to see how long person A was living in Building A during December? So, that would be a getdate() - move in date to get 15 days for December for Person A?
On 1/31/2011, you want to run another report to see how long person A has been living in Building A for the month of January... but, from what you've said the move out date would not be populated. So, by default wouldn't the number of days he had been living there for January be 31?
If I'm understanding the problem correctly, which I may not be, there only needs to be a calculation if the person moved in in the middle of the month (or moved out) right? Otherwise you would just assume they had lived there for 31/30/28 days if there is no move out date specified?
November 23, 2010 at 8:57 am
You're very close to understanding what we're trying to accomplish. Here's where I'm having a challenge... maybe I'm over-thinking it.
What if the person moves into Building A in December and stays until February. What is the query to get the number of days in January?
and
The administrator cannot always run the report on the last day of the month... she needs to be able to run it during the first few days of the next month. She may run the January report on February 5th.
I appreciate your insight.
Thanks.
November 23, 2010 at 10:10 am
November 23, 2010 at 11:11 am
I've setup a test database with three tables - clients, buildings, residency_dates.
The Residency_date table table holds the Client ID, Building ID, Move-In and Move-Out dates.
USE [NightsStay]
GO
/****** Object: Table [dbo].[Buildings] Script Date: 11/23/2010 12:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Buildings](
[Building_ID] [nvarchar](50) NULL,
[Building] [nvarchar](50) NULL
) ON [PRIMARY]
USE [NightsStay]
GO
/****** Object: Table [dbo].[clients] Script Date: 11/23/2010 12:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clients](
[ID] [nvarchar](50) NULL,
[First_Name] [nvarchar](100) NULL,
[Last_Name] [nvarchar](100) NULL
) ON [PRIMARY]
USE [NightsStay]
GO
/****** Object: Table [dbo].[Residency_Dates] Script Date: 11/23/2010 12:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Residency_Dates](
[ID_U] [nvarchar](50) NULL,
[Building_ID] [nvarchar](50) NULL,
[Move_in_Date] [datetime] NULL,
[Move_out_Date] [datetime] NULL
) ON [PRIMARY]
I'm trying to develop a query... this is where I'm at:
SELECT first_name, last_Name, Building, datename(month,Move_in_Date), 'Nights' =
CASE
when Move_out_date is not null THEN datediff(d,move_in_Date,move_out_Date)
ELSE datediff(d,move_in_date, getdate())
END
from clients c
join residency_Dates rd
on c.id = rd.id_u
join buildings b
on b.building_ID = rd.building_ID
November 23, 2010 at 11:23 am
November 23, 2010 at 12:30 pm
In the previous post I started testing using three tables and a query.
This is the result of the query:
John Smith Kagan September 52
John Smith Waylon November 18
John Smith Winthrup November 2
Mary Jones Winthurp November 20
Where Kagan is building A, Waylong building B and Winthrup building C.
It shows that John Smith spent 52 days in September in Kagan... Actually he was in 18 days in September, 31 days Oct, 3 days November in Kagan. I need to show it this way:
John Smith Kagan September 18
John Smith Kagan October 31
John Smith Kagan November 3
John Smith Waylon November 18
John Smith Winthrup November 2
Mary Jones Winthurp November 20
November 23, 2010 at 12:41 pm
I'm getting confused...
First you posted table def for [Tenants] and [Buildings] including sample data. Good point to start with...
When I asked what you've tried so far you posted different DDL statements ([clients] and [Residency_Dates]), this time without sample data.
I don't think I can help you based on the data provided at the moment...
If you'd provide a consistent test scenario with table DDL, sample data (both in a ready to use format), expected result and what you've tried so far, I'm pretty sure we''d be able to help you.
November 23, 2010 at 1:02 pm
I added to data to your original sample data
Columns left to right ID_U, Bulding A in, Building A out, Building B in, Building B out and so on
123 2010-06-01 00:00:00.000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000 2010-09-30 00:00:00.000 2010-09-30 00:00:00.000 NULL
333 2010-06-01 00:00:00.000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000 2010-09-30 00:00:00.000 NULL NULL
444 2010-06-01 00:00:00.000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000 2010-09-30 00:00:00.000 2010-09-30 00:00:00.000 NULL
555 2010-06-01 00:00:00.000 2010-07-01 00:00:00.000 2010-08-08 00:00:00.000 2010-09-30 00:00:00.000 NULL NULL
666 2010-06-01 00:00:00.000 2010-07-01 00:00:00.000 NULL NULL 2010-08-02 00:00:00.000 NULL
Next executed this T-SQL
SELECT t.ID, t.first_name, t.Last_Name,
DATEDIFF(Day,COALESCE(b.Move_in_Date_Building_A,GETDATE()),COALESCE( b.Move_out_Date_Building_A,GETDATE())) AS 'Days in A',
DATEDIFF(Day,COALESCE(b.Move_in_Date_Building_B,GETDATE()),COALESCE( b.Move_out_Date_Building_B,GETDATE())) AS 'Days in B',
DATEDIFF(Day,COALESCE(b.Move_in_Date_Building_C,GETDATE()),COALESCE( b.Move_out_Date_Building_C,GETDATE())) AS 'Days in C'
FROM Tenants t
JOIN Buildings b ON t.id = b.id_u
Result:
Days in A Days in B Days in C
123 John Smith 68 53 54
333 Jack Sprat 68 53 0
444 John Doe 68 53 54
555 Mary Smith 30 53 0
666 Martha Wash 30 0 113
Are the above results what you originally required?
November 23, 2010 at 2:13 pm
I think this is getting close Ron.
This is for an organization that helps people in therapy/recovery. When they start the program they're in Building A, then B, then C.
Our funding source needs to know how many nights each client spent in each Building in each month because the rates are different in each building.
The question my client has is, "How many nights in June was John in Building A? And, how many nights in July? And how many in August. How many nights was he in Building B in August?, etc.
I appreciate your help.
November 23, 2010 at 2:23 pm
@Ron:
I guess the OP is all yours from now on. 😉
At least (s)he's letting you know that you're getting closer...
In order to get the monthla split I'd probably use a calendar table though.
Wouldn't it be nice to see the actual expected output based on some sample data together with an information regarding the input values (to narrow down the result set)?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply