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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy