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.