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.