Sunday January 21st 2018

Categories

Insider

Archives

Creating a dynamic Cross tab in using date add function

sqlIn this post I used dateadd function to dynamically generating 3 new columns for bill paid amount during 3 months as follows
Select
,sum(Case when date_format(b.dateCol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 0 MONTH) ,’%Y-%m-01′) then b.paid_AMNT else 0 end) as Month3
,sum(Case when date_format(b.dateCol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 1 MONTH) ,’%Y-%m-01′) then b.Paid_AMNT else 0 end) as Month2
,sum(Case when date_format(b.datecol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 2 MONTH) ,’%Y-%m-01′) then b.PaidD_AMNT else 0 end) as Month1
From bills b
where
(dateCol between
date(date_format( DATE_ADD(date({?endDate}),INTERVAL -3 MONTH) ,’%Y-%m-01′) )
and LAST_DAY(date({?endDate}))

;