asked on

Aggregate Report Question - Followup to Q_24560753

Hello, my original question is Q_24560753, the link is:https://www.experts-exchange.com/questions/24560753/Aggregate-report.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

Mark Wills

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

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vblycos

ASKER

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.

Mark Wills

A pleasure, and very happy to be of assistance.