February 28, 2015 at 9:13 pm
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
)
CREATE TABLE hotel_daily_visit
(
[guest_name] [varchar](25) NULL,
[effective_date] [date] NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
)
INSERT INTO hotel_guests (guest_name,start_date,end_date)
VALUES ('jim','2015-02-01','2015-02-15','cleaning');
All help is much appreciated!
February 28, 2015 at 11:04 pm
This is one of many problems you can solve pretty easily with a calendar table.
You can read about them here[/url].
I will use Dwain's generate calendar function for my sample solution here. If you already have a calendar table, you can do something similar with it (in that case you cross apply a select of the calendar table with the calendar's date column BETWEEN your other tables start and end date columns)
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME
,@NoDays INT
)
-- Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).
-- See RETURNS table (comments) for meaning of each column.
-- Notes: 1) Max for NoDays is 65536, which runs in just over 2 seconds.
--
-- Example calls to generate the calendar:
-- 1) Forward for 365 days starting today:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 365)
-- ORDER BY SeqNo;
-- 2) Backwards for 365 days back starting today:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, -365)
-- ORDER BY SeqNo;
-- 3) For only the FromDate:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 1);
-- 4) Including only the last week days of each month:
-- Note: Seq no in this case are as if all dates were generated
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 365)
-- WHERE Last = 1 ORDER BY SeqNo;
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of SQL MVP Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL MVP Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (
SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
-- [SeqNo]=Sequential day number (@FromDate always=1) forward or backwards
SELECT [SeqNo] = t.N,
-- [Date]=Date (with 00:00:00.000 for the time component)
[Date] = dt.DT,
-- [Year]=Four digit year
[Year] = dp.YY,
-- [YrNN]=Two digit year
[YrNN] = dp.YY % 100,
-- [YYYYMM]=Integer YYYYMM (year * 100 + month)
[YYYYMM] = dp.YY * 100 + dp.MM,
-- [BuddhaYr]=Year in Buddhist calendar
[BuddhaYr] = dp.YY + 543,
-- [Month]=Month (as an INT)
[Month] = dp.MM,
-- [Day]=Day (as an INT)
[Day] = dp.DD,
-- [WkDNo]=Week day number (based on @@DATEFIRST)
[WkDNo] = DATEPART(dw,dt.DT),
-- Next 3 columns dependent on language setting so may not work for non-English
-- [WkDName]=Full name of the week day, e.g., Monday, Tuesday, etc.
[WkDName] = CONVERT(NCHAR(9),dp.DW),
-- [WkDName2]=Two characters for the week day, e.g., Mo, Tu, etc.
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
-- [WkDName3]=Three characters for the week day, e.g., Mon, Tue, etc.
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
-- [JulDay]=Julian day (day number of the year)
[JulDay] = dp.DY,
-- [JulWk]=Week number of the year
[JulWk] = dp.DY/7+1,
-- [WkNo]=Week number
[WkNo] = dp.DD/7+1,
-- [Qtr]=Quarter number (of the year)
[Qtr] = DATEPART(qq,dt.Dt),
-- [Last]=Number the weeks for the month in reverse
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
-- [LdOfMo]=Last day of the month
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
-- [LDtOfMo]=Last day of the month as a DATETIME
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY
( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY
( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY (cCA), courtesy of Chris Morris)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp;
And now the query:
INSERT hotel_daily_visit (guest_name, effective_date, start_date, end_date, comment)
SELECT guest_name,g.date,start_date,end_date,comment
FROM hotel_guests h
CROSS APPLY GenerateCalendar (h.start_date,datediff(day,h.start_date,h.end_date)+1) g
This is the output I got from your sample (once I cleared up a syntax issue with your insert):
guest_nameeffective_datestart_dateend_datecomment
jim2015-02-012015-02-012015-02-15cleaning
jim2015-02-022015-02-012015-02-15cleaning
jim2015-02-032015-02-012015-02-15cleaning
jim2015-02-042015-02-012015-02-15cleaning
jim2015-02-052015-02-012015-02-15cleaning
jim2015-02-062015-02-012015-02-15cleaning
jim2015-02-072015-02-012015-02-15cleaning
jim2015-02-082015-02-012015-02-15cleaning
jim2015-02-092015-02-012015-02-15cleaning
jim2015-02-102015-02-012015-02-15cleaning
jim2015-02-112015-02-012015-02-15cleaning
jim2015-02-122015-02-012015-02-15cleaning
jim2015-02-132015-02-012015-02-15cleaning
jim2015-02-142015-02-012015-02-15cleaning
jim2015-02-152015-02-012015-02-15cleaning
March 1, 2015 at 11:23 am
Thank you very much! i didn't think to use a function, i do already have a date dimension table.
the solution worked using the following code for those who need as well
SELECT guest_name,start_date,end_date
FROM hotel_guests t1
CROSS JOIN (SELECT date FROM date_table) t2
WHERE t2.date >= t1.start_date and t2.date <= t1.end_date
March 1, 2015 at 9:40 pm
cs_source (2/28/2015)
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
)
CREATE TABLE hotel_daily_visit
(
[guest_name] [varchar](25) NULL,
[effective_date] [date] NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
)
INSERT INTO hotel_guests (guest_name,start_date,end_date)
VALUES ('jim','2015-02-01','2015-02-15','cleaning');
All help is much appreciated!
My question now would be, why do you want to expand the data like this? What purpose will it be used for. I ask because it may be totally unnecessary to perform this data duplication.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2015 at 12:56 am
posted too quick see comment below.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 2, 2015 at 1:13 am
cs_source (3/1/2015)
Thank you very much! i didn't think to use a function, i do already have a date dimension table.the solution worked using the following code for those who need as well
SELECT guest_name,start_date,end_date
FROM hotel_guests t1
CROSS JOIN (SELECT date FROM date_table) t2
WHERE t2.date >= t1.start_date and t2.date <= t1.end_date
Surely and Inner join to the Dates table with a BETWEEN on the Join is far better than the Cross Join.
SELECT guest_name,start_date,end_date,date
FROM hotel_guests t1
JOIN date_table t2 on date Between Start_date and end_date
Especially if the DATE column on the date table is indexed.
I would actually consider this structure
CREATE TABLE hotel_guests
(
[GuestId] Int NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION
,[GuestName] VARCHAR(255) NOT NULL
,[StartDate] DATE NOT NULL
,[Duration] SmallInt NOT NULL
)
GO
CREATE TABLE Hotel_Tasks
(
[HotelTaskId] SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1) NOT FOR REPLICATION
,[HotelTaskType] varchar(50)
)
GO
CREATE TABLE hotel_daily_visit
(
[GuestId] Int NOT NULL --FK to hotel_guests
,[VisitDate] DATE NOT NULL
,[HotelTaskId] SmallInt NOT NULL -- FK to HotelTasks
,[Comment] VARCHAR(255) NULL
)
The Hotel_Task table would include things like 'Room service', 'Cleaning', 'Mini-Bar Check', 'Linen Change', 'Maintenance' etc.
The insert would then be relatively simple as you only ever have to insert a row(s) for the current day IF the date is between the guests StartDate and the StartDate+Duration.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 2, 2015 at 7:46 am
Hi Jeff,
Fair question and at this point i'm not sure if this is something i need however what the end goal is to get a listing using start_date, effective_date (date in between), end_date and each hour that the customer was there.
Using this code below which separates the days into hours works perfectly however i have 1 issue and that is i can't seem to put the "effective" date in the table.
start_date:2015-03-02 0800
end_date:2015-03-04 1200
effective_date:2015-03-02<--
effective_date:2015-03-03<--
effective_date:2015-03-04<--
INSERT INTO cmh_dw.dbo.fact_discharge_planning_by_hour(
[EDD_month]
,[EDD_start_Date]
--,[EDD_Effective_Date]
,[EDD_end_Date]
,[Account_Number]
,[Hour]
,[H0]
,[H1]
,[H2]
,[H3]
,[H4]
,[H5]
,[H6]
,[H7]
,[H8]
,[H9]
,[H10]
,[H11]
,[H12]
,[H13]
,[H14]
,[H15]
,[H16]
,[H17]
,[H18]
,[H19]
,[H20]
,[H21]
,[H22]
,[H23])
SELECT DISTINCT dim_date.Month
,a.start_date
--,a.edd_date
,a.end_date
,a.account_number
,x.Hour_in_service
,case when Hour_in_service = '0' THEN '1' ELSE '0' END
,case when Hour_in_service = '1' THEN '1' ELSE '0' END
,case when Hour_in_service = '2' THEN '1' ELSE '0' END
,case when Hour_in_service = '3' THEN '1' ELSE '0' END
,case when Hour_in_service = '4' THEN '1' ELSE '0' END
,case when Hour_in_service = '5' THEN '1' ELSE '0' END
,case when Hour_in_service = '6' THEN '1' ELSE '0' END
,case when Hour_in_service = '7' THEN '1' ELSE '0' END
,case when Hour_in_service = '8' THEN '1' ELSE '0' END
,case when Hour_in_service = '9' THEN '1' ELSE '0' END
,case when Hour_in_service = '10' THEN '1' ELSE '0' END
,case when Hour_in_service = '11' THEN '1' ELSE '0' END
,case when Hour_in_service = '12' THEN '1' ELSE '0' END
,case when Hour_in_service = '13' THEN '1' ELSE '0' END
,case when Hour_in_service = '14' THEN '1' ELSE '0' END
,case when Hour_in_service = '15' THEN '1' ELSE '0' END
,case when Hour_in_service = '16' THEN '1' ELSE '0' END
,case when Hour_in_service = '17' THEN '1' ELSE '0' END
,case when Hour_in_service = '18' THEN '1' ELSE '0' END
,case when Hour_in_service = '19' THEN '1' ELSE '0' END
,case when Hour_in_service = '20' THEN '1' ELSE '0' END
,case when Hour_in_service = '21' THEN '1' ELSE '0' END
,case when Hour_in_service = '22' THEN '1' ELSE '0' END
,case when Hour_in_service = '23' THEN '1' ELSE '0' END
FROM hotel a
CROSS APPLY (
SELECT TOP (1+ DATEDIFF(hour, a.start_datetime, a.stop_datetime))
Hour_in_service = DATEPART(HOUR,DATEADD(hour,n,a.start_datetime))
FROM iTally t
) x
JOIN CMH_DW.dbo.Dim_Date
ON Dim_Date.Date=a.start_date
where a.end_date is not null
Output is something like this:
Month 2015-03-01
Start_Date 2015-03-02
Effective_Date -- i dont know how to populate this referencing the effective date.. aka which date are the hours for.
Stop_Date 2015-03-04
Account_Number 123456
Hour 0
H0 1
H1 0
H2 0
...
H20 0
H21 0
H22 0
H23 0
I will know the start date and the end date and the hours however when i report on this table i won't know what hour is part of what day, i'm not sure if that makes sense?
Hi Ten,
the structure you presented i can see how it would benefit, as soon as i complete the issue above i will certainly see if that would work better.
March 3, 2015 at 12:47 am
hi,
Sorry I didn't get back to you yesterday, work got hectic.
I didn't realise this was for a DW, had it been a OLTP system I would have used the structure you proposed.
However, from a DW perspective I would actually do something different.
The first thing I would is create a Fact table that Pivoted the Hours so that hours becomes a Dimension, this has a number of benefits
1) You don't need to enter rows where there are no markers.
2) you can Cross Tab the data in Excel or a report so much easier
The Structure I would use is something like
Create Table dim_Hours
(
Hour SmallInt NOT NULL PRIMARY KEY
,HourDescription CHAR(3) --Formated to be 'H00' - 'H23'
)
CREATE TABLE Fact_discharge_planning_by_hour
(
EDD_Month INT --FK to Dim Period
,EDD_StartDate DATE --FK to Dim Calendar
,EDD_EndDate DATE --FK to Dim Calendar
,EDD_EffectiveDate DATE --FK to Dim Calendar
,AccountKey INT -- FK To Dim_Account
,Hour SmallInt -- FK to Dim_Hour
,Measure
)
So all you then need to do on the insert is the following
INSERT INTO Fact_discharge_planning_by_hour
SELECT
Month
,a.Start_date
,a.End_date
,calendar.Date EffectiveDate
,Account.AccountKey
,DATEPART(HOUR,a.StartDateTime)
,1
FROM
Hotel a
JOIN CMH_DW.dbo.Dim_Date Calendar
ON Dim_Date.Date>a.StartDate and Dim_date.date<=a.EndDate
JOIN Dim_Account Account
ON a.AccountNumber = Dim_Account.AccountNumber
To Get Data out its very simple with a Cross tab, or Pivot you can create the missing columns, very easily.
Eg
SELECT
Edd_Month
,Edd_StartDate
,Edd_EndDate
,Edd_EffectiveDate
, AccountKey
, Sum(CASE Hour WHEN 0 THEN Measure ELSE 0 END) [H0]
, Sum(CASE Hour WHEN 1 THEN Measure ELSE 0 END) [H1]
, Sum(CASE Hour WHEN 2 THEN Measure ELSE 0 END) [H2]
, Sum(CASE Hour WHEN 3 THEN Measure ELSE 0 END) [H3]
--ETC to
, Sum(CASE Hour WHEN 23 THEN Measure ELSE 0 END) [H23]
From Fact_discharge_planning_by_hour
GROUP BY
Edd_Month
,Edd_StartDate
,Edd_EndDate
,Edd_EffectiveDate
, AccountKey
This might give you a few ideas on a way forward.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply