Storing Periods or a split by day of periods in a Data Warehouse

I had this issue at the client.

Imagine that you have to model a datwarehouse for an hotel group.

In the operationnal system, you get entries like this:

From To Name Amount journey
1/01/2010 10/01/2010 Ralph Kimball 1800 9 days
3/06/2012 15/07/2012 Jose garcia 20976 44 days

My first error was to store the data like in the operationnal system and when the client asked me, statistical on the room occupation by month, I was not able to found the correct value because I had only 2 links with the dimension date that were From_Date_ID and To_Date_ID.

The customer ‘Ralph kimball’ is not causing any problem for statistics by month but customer ‘Jose garcia’ is :

I have to calculate the occupation rate by month:

– For Customer Ralph Kimball there is no problem, it’s 9 for January

– For Customer José Garcia, I have to split the period like this : 30 days in June and 7 days in July! And as you see in the table bellow, I only have one value for the journey that is 44 and 2 dates linked to it (from and to). So If I Filter on from date for january, I will get 44 and same for To date!!!! and of course, that’s not what we want.

So As I don’t have the split by month, except by doing very complicated calulations, it’s not possible to get the correct result. It means that if the customer stays a year, the amount of days he stays will be summed to month (from–> begin of the period) or to the month (to –> end of the period).

The solution of this architecture issue is to change the granularity of your fact table and not to store periods in it but a split of each journey by day.

Then you will have as many rows in your data warehouse as days in each journey.

The query of the split will be something like this:

select From, Date, To, Name , Amount

from journey inner join

Date_dimension on From <= Date and Date >= To

This will be the result for the first journey of the person ‘Ralph Kimball’:

From Day To Name Amount days nights
1/01/2010 1/01/2010 10/01/2010 Ralph Kimball 0 1 0
1/01/2010 2/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 3/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 4/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 5/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 6/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 7/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 8/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 9/01/2010 10/01/2010 Ralph Kimball 200 1 1
1/01/2010 10/01/2010 10/01/2010 Ralph Kimball 200 1 1

SQL Server Convert from varchar to datetime, datetime format

Here are some queries ready for varchar to datetime or datetime to varchar conversion:

Think that a datetime is a fix format that depends of the date format  in your Windows OS and if you want to customize the way to show a datetime, use a varchar. 🙂

Varchar   format From   Varchar –> datetime from   date time –> ‘Varchar’
mon dd yyyy hh:mmAM convert(datetime,’Varchar’,100) convert(Varchar,’Date’,100)
mon dd yy hh:mmAM convert(datetime,”Varchar”,0) convert(Varchar,’Date’,0)
mm/dd/yy convert(datetime,’Varchar’,1) convert(Varchar,’Date’,1) convert(datetime,’Varchar’,2) convert(Varchar,’Date’,2)
dd/mm/yy convert(datetime,’Varchar’,3) convert(Varchar,’Date’,3) convert(datetime,’Varchar’,4) convert(Varchar,’Date’,4)
dd-mm-yy convert(datetime,’Varchar’,5) convert(Varchar,’Date’,5)
dd mmm yy convert(datetime,’Varchar’,6) convert(Varchar,’Date’,6)
mmm dd, yy convert(datetime,’Varchar’,7) convert(Varchar,’Date’,7)
HH:mm:ss convert(datetime,’Varchar’,8) convert(Varchar,’Date’,8)
mon dd yyyy hh:mm:ss:fffAM convert(datetime,’Varchar’,9) convert(Varchar,’Date’,9)
mm-dd-yy convert(datetime,’Varchar’,10) convert(Varchar,’Date’,10)
yy/mm/dd convert(datetime,’Varchar’,11) convert(Varchar,’Date’,11)
yymmdd convert(datetime,’Varchar’,12) convert(Varchar,’Date’,12)
dd mon yyyy HH:mm:ss:fff convert(datetime,’Varchar’,13) convert(Varchar,’Date’,13)
HH:mm:ss:fff convert(datetime,’Varchar’,14) convert(Varchar,’Date’,14)
yyyy-mm-dd HH:mm:ss convert(datetime,’Varchar’,20) convert(Varchar,’Date’,20)
yyyy-mm-dd HH:mm:ss.fff convert(datetime,’Varchar’,21) convert(Varchar,’Date’,21)
mm/dd/yyyy convert(datetime,’Varchar’,101) convert(Varchar,’Date’,101) convert(datetime,’Varchar’,102) convert(Varchar,’Date’,102)
dd/mm/yyyy convert(datetime,’Varchar’,103) convert(Varchar,’Date’,103) convert(datetime,’Varchar’,104) convert(Varchar,’Date’,104)
dd-mm-yyyy convert(datetime,’Varchar’,105) convert(Varchar,’Date’,105)
dd mmm yyyy convert(datetime,’Varchar’,106) convert(Varchar,’Date’,106)
mmm dd, yyyy convert(datetime,’Varchar’,107) convert(Varchar,’Date’,107)
mm-dd-yyyy convert(datetime,’Varchar’,110) convert(Varchar,’Date’,110)
yyyy/mm/dd convert(datetime,’Varchar’,111) convert(Varchar,’Date’,111)
yyyymmdd convert(datetime,’Varchar’,112) convert(Varchar,’Date’,112)
yyy-mm-ddThh:mm:ss convert(datetime,’Varchar’,126) convert(Varchar,’Date’,126)