Does anyone have this view or the script. Mine one is giving trouble - June is Period 1.. Please suggest ..thx
alter view Top_10_Customer_By_Sales_CA
as
Select
CM.CUSTNAME, RM.CUSTNMBR,sum(RM.[Sales_Amount]) as Sales, case month(RM.GLpostdt)
when 1 then 8
when 2 then 9
when 3 then 10
when 4 then 11
when 5 then 12
when 6 then 1
when 7 then 2
when 8 then 3
when 9 then 4
when 10 then 5
when 11 then 6
else 7 end as
'Period_Name',
case
when month(RM.GLpostdt) >=7
then year(RM.GLPOSTDT)+1
else year(RM.GLPOSTDT) end as 'Year'
from
(SELECT
CUSTNMBR,DOCDATE, GLPOSTDT,DOCNUMBR,RMDTYPAL, ORTRXAMT, CURTRXAM, SLSAMNT, FRTAMNT,MISCAMNT, TAXAMNT, TRDISAMT, VOIDSTTS,
case
when RMDTYPAL in (1,3,4) then ORTRXAMT when RMDTYPAL in (7,8) then -1*ORTRXAMT
else 0 end Sales_Amount
FROM RM20101) RM
Left outer join
RM00101 CM on CM.CUSTNMBR = RM.CUSTNMBR
group by month(RM.GLpostdt),Year(RM.GLPOSTdt), RM.CUSTNMBR, CM.CUSTNAME
Union all
Select
CM.CUSTNAME, RM.CUSTNMBR,sum(RM.[Sales_Amount]) as Sales , case month(RM.GLpostdt)
when 1 then 8
when 2 then 9
when 3 then 10
when 4 then 11
when 5 then 12
when 6 then 1
when 7 then 2
when 8 then 3
when 9 then 4
when 10 then 5
when 11 then 6
else 7 end as
'Period_Name',
case
when month(RM.GLpostdt) >=7 then year(RM.GLPOSTDT)+1 else year(RM.GLPOSTDT) end as 'Year'
from
(SELECT
CUSTNMBR,DOCDATE, GLPOSTDT,DOCNUMBR,RMDTYPAL, ORTRXAMT, CURTRXAM, SLSAMNT, FRTAMNT,MISCAMNT, TAXAMNT, TRDISAMT, VOIDSTTS,
case
when RMDTYPAL in (1,3,4) then ORTRXAMT when RMDTYPAL in (7,8) then -1*ORTRXAMT else 0 end Sales_Amount
FROM RM30101) RM
Left outer join
RM00101 CM on
CM.CUSTNMBR = RM.CUSTNMBR
group by month(RM.GLpostdt),Year(RM.GLPOSTdt), RM.CUSTNMBR, CM.CUSTNAME
