Hi I have created following on script level, Now I would like to create WTD,MTD,YTD and LTD sales and units sold.
Sales:
load
*,
MonthName([Created DateTime]) as MonthYear,
Year([Created DateTime]) as Year,
Month([Created DateTime]) as Month,
Week([Created DateTime]) as Week,
WeekEnd([Created DateTime]) as Weekend,
WeekStart([Created DateTime]) as Weekstart,
MonthStart([Created DateTime]) as Monthstart,
MonthEnd([Created DateTime]) as Monthend,
QuarterName([Created DateTime]) as QuarterName,
Date([Created DateTime])as DateNum
;
sql
SELECT OrderNo
, ln.[Item No_]
,itm.[EAN]
,ln.[Category]
,ln.[Total Price]
,ln.[Quantity]
,ord.[Created DateTime]
,ofr.[Brand]
,[RRP]
FROM [Echo Trading$Order] ord
inner join [Echo Trading$Order Line] ln
on ord.No_=ln.[Order No_]
inner join [dbo].[Echo Trading$Offer] ofr
on ln.[Offer Id]=ofr.[Offer ID]
inner join
[dbo].[Echo Trading$Item] itm
on ln.[Item No_]=itm.[Item No_];
I would like to produce below table:
| Brand | Item No | WTD units | WTD Sales | MTD Units | MTD Sales | YTD Units | YTD Sales | LTD Units | LTD Sales |
|---|
| | | | | | | | | |
Do I have to create any variables for calculation?
Thank you in advance.
Madhu
