Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Fact Table in Datawarehouse : WTD, MTD, QTD, YTD

Author  Topic 

bryanket
Starting Member

1 Post

Posted - 2013-11-21 : 15:33:32
Hello Experts,

Right now i am working on my first datawarehouse project, and i need your help for creating new fact table.

My Transactional data looks like :
Date PID CID DID BU Vol
03-Jan-06 A 111 zzz B01 20
03-Jan-06 A 112 zzz B01 30
03-Jan-06 A 113 zzz B02 80
04-Jan-06 A 111 zzz B01 50
04-Jan-06 A 112 zzz B01 60
04-Jan-06 A 113 zzz B02 30
03-Jan-07 A 111 zzz B01 50
03-Jan-07 A 112 zzz B01 40
03-Jan-07 A 113 zzz B02 20
04-Jan-07 A 111 zzz B01 50
04-Jan-07 A 112 zzz B01 60
04-Jan-07 A 113 zzz B02 20


Here We have aroung 60 different products(PID), more than 0.5 million customers (CID), 31 Business Units (BUID). Volume is nothing but Quantity.

From this table i want to create one fact table like :

Date PID CID DID BU Vol CY_WTD CY_MTD CY_QTD CY_YTD LY_WTD LY_MTD LY_QTD LY_YTD
03-Jan-06 A 111 zzz B01 20 20 20 20 20
03-Jan-06 A 112 zzz B01 30 30 30 30 30
03-Jan-06 A 113 zzz B02 80 80 80 80 80
04-Jan-06 A 111 zzz B01 50 70 70 70 70
04-Jan-06 A 112 zzz B01 60 90 90 90 90
04-Jan-06 A 113 zzz B02 30 110 110 110 110
02-Jan-07 A 111 zzz B01 50 50 50 50 50
02-Jan-07 A 112 zzz B01 40 40 40 40 40
02-Jan-07 A 113 zzz B02 20 20 20 20 20
03-Jan-07 A 111 zzz B01 50 100 100 100 100 20 20 20 20
03-Jan-07 A 112 zzz B01 60 80 80 80 80 30 30 30 30
03-Jan-07 A 113 zzz B02 20 40 40 40 40 80 80 80 80


I need to calculate Current Year Week Till Date (CY_WTD) , Current Year Month Till Date (CY_MTD), Current Year Quarter till date (CY_QTD) , Current Year Year Till Date (CY_YTD), as all these figs for Previous Year. (PY_WTD = Previous Year Week Till Date)

I have a calender table in datawarehouse. Details are as follows :
Financial Year : 1st Jan - 31 Dec
Week Starts from Saturday
Quarter structure is : 4 - 4 - 5 Weeks.

My Calender Table fields are :
PeriodType PeriodName PeriodCode StartDate EndDate
Month MN0601 601 20060101 20060127
Month MN0602 602 20060128 20060224
Month MN0603 603 20060225 20060331
Month MN0604 604 20060401 20060428
Month MN0605 605 20060429 20060526
Month MN0606 606 20060527 20060630
Month MN0607 607 20060701 20060728
Month MN0608 608 20060729 20060825
Month MN0609 609 20060826 20060929
Month MN0610 610 20060930 20061027
Month MN0611 611 20061028 20061124
Month MN0612 612 20061125 20061231
Qtr QR0601 601 20060101 20060331
Qtr QR0602 602 20060401 20060630
Qtr QR0603 603 20060701 20060929
Qtr QR0604 604 20060930 20061231
Week WK0601 601 20060101 20060106
Week WK0602 602 20060107 20060113
Week WK0603 603 20060114 20060120
Week WK0604 604 20060121 20060127
Week WK0605 605 20060128 20060203
Week WK0606 606 20060204 20060210
.
.
.


Basically i have a record of starting and ending date of Week, Month, Quarter....


Requesting you to kindly help me to build CY and PY WTD, MTD, QTD, YTD figures.


Kindly Note that : For the previous year MTD : i Need to consider Day of the week.

In Above example : for 3rd Jan 2007 rows : it should calculate the day of the week i.e. 3rd-jan-07 is the Wednesday of week 1
so for Previous year WTD is should calculate Previous Year same week i.e. 1st week Wednesday figures which is 4th Jan 2006 figures.

I have a admin rights , so in case if i need to add any new table / columns in tables then it is possible.

Many Thanks in advance.
   

- Advertisement -