Aggregate Report Question - Followup to Q_24560753

Hello, my original question is Q_24560753, the link is:http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24560753.html

I am posting a followup question to accomodate some changes to that solution. Attached is the updated document with updated output report.

1) The report above shows only for "Agreeddates" in the table for that month. However, I would like to present for all the dates for that month from 1st of that month till end of that month. I am passing input parameter in the stored procedure as "Mon-Yr". For example, if i am passing "Jul-09" as the parameter, i would like to see all the dates from Jul-1-2009 to Jul-31-2009  as agreeddates regardless they have data or not. If no data , show the counts as zeroes.

2) Also, insted of showing 12 months from January to December, I would like to show 12 months from the input parameter date. For example, if input parameter is 'Jul-09', then I would like to show 12 months from Jul-09 to Jul-10 instead of January to Decmeber.

Updated document/report attached.

Thanks
Solution provided by ralmada:
 
create table payments (
	pkey int identity(1,1),
	AgreeDate datetime,
	EndDate datetime,
	Frequency varchar(1),
	NPayments int
)
 
insert payments values('2009-07-01', '2009-10-01',	'M',	4)
insert payments values('2009-07-01', '2009-07-01',	'M',	1)
insert payments values('2009-07-02','2009-08-02',	'M',	2)
insert payments values('2009-07-02','2009-08-02',	'W',	6)
 
select * from payments
 
;with CTE as (
select pkey, AgreeDate, AgreeDate as sDate, Frequency, NPayments, 1 as olevel from payments
union all
select a.pkey, case when a.Frequency = 'M' then dateadd(m, 1, d.AgreeDate)
				    when a.Frequency = 'W' then dateadd(wk, 1, d.AgreeDate)
					else a.AgreeDate
				end as AgreeDate, a.AgreeDate as sDate, a.Frequency, a.NPayments, d.olevel + 1 from payments a
inner join CTE d on a.pkey = d.pkey
where olevel < a.NPayments
)
select	isnull(convert(varchar, sDate, 106), 'Total'),
		sum([January]) as January,
		sum([February]) as February,
		sum([March]) as March,
		sum([April]) as April,
		sum([May]) as May,
		sum([June]) as June,
		sum([July]) as July,
		sum([August]) as August,
		sum([September]) as September,
		sum([October]) as October,
		sum([November]) as November,
		sum([December]) as December
from
(select *, datename(m,AgreeDate) dmonth from CTE) o
pivot (count(dmonth) for dmonth in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))p
group by sDate
with rollup

Open in new window

Book1---Input-and-Report.xls
LVL 4
vblycosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Well to get the proper month names, have to use a bit of dynamic SQL....


Try the code below (uses the payments table in the example provided above). Will explain it all afterwards...
 
declare @d datetime
set @d = '01-'+'Jul-09'                -- the 'Jul-09' part would be replaced with the parameter value
 
declare @sql varchar(max)
set @sql = '
;with CTE as (
              select convert(datetime,'''+convert(varchar,@d,106)+''') as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE 
              where cdate < dateadd(month,datediff(month,-1,'''+convert(varchar,@d,106)+'''),0) - 1
)
 
select case when cdate is NULL then ''Total'' else convert(varchar,cdate,106) end as cdate
, sum(isnull([0],0)) as ['+substring(convert(varchar,@d,106),3,9) + ']
, sum(isnull([1],0)) as ['+substring(convert(varchar,dateadd(month,1,@d),106),3,9) + ']
, sum(isnull([2],0)) as ['+substring(convert(varchar,dateadd(month,2,@d),106),3,9) + ']
, sum(isnull([3],0)) as ['+substring(convert(varchar,dateadd(month,3,@d),106),3,9) + ']
, sum(isnull([4],0)) as ['+substring(convert(varchar,dateadd(month,4,@d),106),3,9) + ']
, sum(isnull([5],0)) as ['+substring(convert(varchar,dateadd(month,5,@d),106),3,9) + ']
, sum(isnull([6],0)) as ['+substring(convert(varchar,dateadd(month,6,@d),106),3,9) + ']
, sum(isnull([7],0)) as ['+substring(convert(varchar,dateadd(month,7,@d),106),3,9) + ']
, sum(isnull([8],0)) as ['+substring(convert(varchar,dateadd(month,8,@d),106),3,9) + ']
, sum(isnull([9],0)) as ['+substring(convert(varchar,dateadd(month,9,@d),106),3,9) + ']
, sum(isnull([10],0)) as ['+substring(convert(varchar,dateadd(month,10,@d),106),3,9) + ']
, sum(isnull([11],0)) as ['+substring(convert(varchar,dateadd(month,11,@d),106),3,9) + ']
from (
 select cdate, case when number is NULL then 0 else 1 end as Counter, datediff(month,agreedate,anniversary) as Period
 from cte
 left outer join (
 select pkey, agreedate, enddate, number ,case when frequency = ''W'' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = ''p'') n on n.number between 0 and Npayments - 1
 ) a on cdate = agreedate
) srce
pivot
(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) p
group by cdate
with rollup'
 
exec(@sql)

Open in new window

0
Mark WillsTopic AdvisorCommented:
OK, by way of explanation...


We use the CTE query to build the list of days. Simple query, just add 1 to the previous date until all days have happened.

Then we use that to generate a "vertical" list of dates :

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)
select * from CTE


Now, we also need the different months between agreedate and enddate. For this we use a small "trick". There is a Microsoft table with every SQL, it is there "general description" table for a range of numbers or settings which need a name. It is spt_values in the Master database. We use this to get the number of months between agreedate and enddate. We then use the individual "expanded" rows to get the anniversary dates :

 select pkey, agreedate, enddate, number ,case when frequency = 'W' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = 'p') n on n.number between 0 and Npayments - 1

So, now we have our vertical list of days and our horizontal columns using our "trick" to get months.

Now, we need to combine those two things...


declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

 select cdate, agreedate, enddate, frequency, npayments, anniversary, number, case when number is NULL then 0 else 1 end as Counter, datediff(month,agreedate,anniversary) as Period
 from cte
 left outer join (
 select pkey, agreedate, enddate, frequency, npayments, number ,case when frequency = 'W' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = 'p') n on n.number between 0 and Npayments - 1
 ) a on cdate = agreedate


And that is nearly all we need. In fact, because we are going to pivot, then, it is more than we need. In reality, we only need 3 things for a pivot. The row definition (in our case CDATE), something to aggregate (in our case the number of periods between agreedat and enddate) and something to pivot over (in our case the 12 different periods).

So, let's have a quick look at the pivot using the CTE, and just the "period" wiitout it being an actual date...

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

select *
from (
 select cdate, case when number is NULL then 0 else 1 end as Counter, datediff(month,agreedate,anniversary) as Period
 from cte
 left outer join (
 select pkey, agreedate, enddate, number ,case when frequency = 'W' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = 'p') n on n.number between 0 and Npayments - 1
 ) a on cdate = agreedate
) srce
pivot
(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) p


The other small challenge is getting the total. That is easy enough, we can use the ROLLUP function, one small problem, that requires a GROUP BY, which means we need an aggregate  function. So now we are going to get a "bigger" query just so we can get that total...

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

select case when cdate is NULL then 'Total' else convert(varchar,cdate,106) end as cdate
, sum(isnull([0],0)) as Current_Month
, sum(isnull([1],0)) as [Month + 1]
, sum(isnull([2],0)) as [Month + 2]
, sum(isnull([3],0)) as [Month + 3]
, sum(isnull([4],0)) as [Month + 4]
, sum(isnull([5],0)) as [Month + 5]
, sum(isnull([6],0)) as [Month + 6]
, sum(isnull([7],0)) as [Month + 7]
, sum(isnull([8],0)) as [Month + 8]
, sum(isnull([9],0)) as [Month + 9]
, sum(isnull([10],0)) as [Month + 10]
, sum(isnull([11],0)) as [Month + 11]
from (
 select cdate, case when number is NULL then 0 else 1 end as Counter, datediff(month,agreedate,anniversary) as Period
 from cte
 left outer join (
 select pkey, agreedate, enddate, number ,case when frequency = 'W' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = 'p') n on n.number between 0 and Npayments - 1
 ) a on cdate = agreedate
) srce
pivot
(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) p
group by cdate
with rollup


Now, the final problem with Pivot is you have to "know" the results. With a rolling 12 months, we do not actually know what those twelve columns will be... Well, we do, but its name is dependant on the parameter and that means the rolling twelve months could change every time...

That is where we have to introduce the dynamic SQL as the final result shown in the previous posting (except the substring should have been 4,8 not 3,8)...


And as a stored procedure, we get  (and there are lots of other ways to achieve the same result) :



alter procedure usp_Pivot_Payments(@period varchar(20))
as
 
declare @d datetime
set @d = '01-'+@period
 
declare @sql varchar(max)
set @sql = '
;with CTE as (
              select convert(datetime,'''+convert(varchar,@d,106)+''') as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE 
              where cdate < dateadd(month,datediff(month,-1,'''+convert(varchar,@d,106)+'''),0) - 1
)
 
select case when cdate is NULL then ''Total'' else convert(varchar,cdate,106) end as cdate
, sum(isnull([0],0)) as ['+substring(convert(varchar,@d,106),4,8) + ']
, sum(isnull([1],0)) as ['+substring(convert(varchar,dateadd(month,1,@d),106),4,8) + ']
, sum(isnull([2],0)) as ['+substring(convert(varchar,dateadd(month,2,@d),106),4,8) + ']
, sum(isnull([3],0)) as ['+substring(convert(varchar,dateadd(month,3,@d),106),4,8) + ']
, sum(isnull([4],0)) as ['+substring(convert(varchar,dateadd(month,4,@d),106),4,8) + ']
, sum(isnull([5],0)) as ['+substring(convert(varchar,dateadd(month,5,@d),106),4,8) + ']
, sum(isnull([6],0)) as ['+substring(convert(varchar,dateadd(month,6,@d),106),4,8) + ']
, sum(isnull([7],0)) as ['+substring(convert(varchar,dateadd(month,7,@d),106),4,8) + ']
, sum(isnull([8],0)) as ['+substring(convert(varchar,dateadd(month,8,@d),106),4,8) + ']
, sum(isnull([9],0)) as ['+substring(convert(varchar,dateadd(month,9,@d),106),4,8) + ']
, sum(isnull([10],0)) as ['+substring(convert(varchar,dateadd(month,10,@d),106),4,8) + ']
, sum(isnull([11],0)) as ['+substring(convert(varchar,dateadd(month,11,@d),106),4,8) + ']
from (
 select cdate, case when number is NULL then 0 else 1 end as Counter, datediff(month,agreedate,anniversary) as Period
 from cte
 left outer join (
 select pkey, agreedate, enddate, number ,case when frequency = ''W'' then dateadd(wk,n.number,agreedate) else dateadd(m,n.number,agreedate) end as anniversary
 from payments
 inner join (select * from master..spt_values where type = ''p'') n on n.number between 0 and Npayments - 1
 ) a on cdate = agreedate
) srce
pivot
(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p
group by cdate
with rollup'
 
exec(@sql)
Go
 
-- then to use it:
 
usp_pivot_payments 'Jul-09'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vblycosAuthor Commented:
Thank You mark_wills. I never understood that the original query was going to be so complicated by parameterizing the input date. I am so thrilled to learn new things and your solution along the explanation is one the excellent way to learn new stuff. Thank You so much for the solution.
0
Mark WillsTopic AdvisorCommented:
A pleasure, and very happy to be of assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.