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.