In northwind DB there are three dates:
1.order_date
2.shipping_date
3.required_date
We need to create a common calendar that would b common to all d dates if v
create a normal calendar using only order_date or so we will get the
corresponding shipping_date and required_date for that order date or so on
thus to select a date and get all order_date,shipping_date,required_date)
for that date v need to create a common calendar using concatenation
ex:
common_time:
LOAD OrderID,
OrderDate,
day(OrderDate) as day_of_OD,
week(OrderDate) as week_of_OD,
month(OrderDate) as Month_of_OD,
year(OrderDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);
concatenate
LOAD OrderID,
ShippedDate,
day(ShippedDate) as day_of_OD,
week(ShippedDate) as week_of_OD,
month(ShippedDate) as Month_of_OD,
year(ShippedDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);
concatenate
LOAD OrderID,
RequiredDate,
day(RequiredDate) as day_of_OD,
week(RequiredDate) as week_of_OD,
month(RequiredDate) as Month_of_OD,
year(RequiredDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);
//notice that v use d same names for all date columns this will
concatenate the columns and create a table which holds all dates and each
date selected in this table will correspond to d same for all the three
dates
output:
RequiredDate,
day(RequiredDate) as day_of_OD,
week(RequiredDate) as week_of_OD,
month(RequiredDate) as Month_of_OD,
year(RequiredDate) as year_of_OD
FROM
[C:\Documents and Settings\11050.TCPL\Desktop\Almas\Orders.qvd]
(qvd);
//notice that v use d same names for all date columns this will
concatenate the columns and create a table which holds all dates and each
date selected in this table will correspond to d same for all the three
dates
output:
|
OrderDate
|
RequiredDate
|
ShippedDate
|
|
1/9/1998
|
1/5/1998
|
1/5/1998
|
|
1/8/1998
|
1/6/1998
|
1/6/1998
|
|
1/7/1998
|
1/7/1998
|
1/7/1998
|
|
1/6/1998
|
1/8/1998
|
1/8/1998
|
|
1/5/1998
|
1/9/1998
|
1/9/1998
|
No comments:
Post a Comment