SELECT ID
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = EOMONTH(getdate()) THEN NETAMT ELSE 0 END) as [CurrentMonth]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = EOMONTH(getdate()) THEN NETAMT ELSE 0 END)
+ SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-1,eomonth(getdate())) THEN NETAMT ELSE 0 END)
+ SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-2,eomonth(getdate())) THEN NETAMT ELSE 0 END)
+ SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-3,eomonth(getdate())) THEN NETAMT ELSE 0 END)
+ SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-4,eomonth(getdate())) THEN NETAMT ELSE 0 END)
+ SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-5,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Total_Previous_6Month]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-1,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Previous_M1]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-2,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Previous_M2]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-3,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Previous_M3]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-4,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Previous_M4]
, SUM(CASE WHEN EOMONTH([BOOKDATE]) = dateadd(MM,-5,eomonth(getdate())) THEN NETAMT ELSE 0 END) as [Previous_M5]
FROM [TABLE]
group by ID
order by ID

TG Facebook Comments