Aggregate report

I have following data in my input sql server 2005 database table:
a) Agreed date
b) End date
c) Frequency - Monthly/Weekly
d) Number of payments

Ex: When agreed date = 7/1/2009, Frequency is Monthly, and Number of Payments is 4, it indicates that 4 future payments are expected and they are on: 7/1/2009, 8/1/2009, 9/1/2009 and 10/10/2009 (which is the End date also).

I need to aggregate this data into a report having:

X axis - Months (from current month to 12 months)
Y axis - Current month dates.

My sample input data and output report are attached here. How can I achieve this in a stored procedure in Sql Server 2005?

Thanks
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.

ralmadaCommented:

Attached is my attempt with your sample data.

If you want to use a stored procedure just put the query that starts in ;with .... in the stored procedure

create procedure aggregated_output
as
;with CTE as (.
.....

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, EndDate, 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.EndDate, a.Frequency, a.NPayments, d.olevel + 1 from payments a
inner join CTE d on a.pkey = d.pkey
where olevel < a.NPayments
)
select	sDate,
		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

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
ralmadaCommented:
Here's an updated query just adding "total" at the bottom

;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

0
vblycosAuthor Commented:
Vow..the solution helps. It took me some time to understand the solution, but I am glad that I learned new stuff with recursive queries and pivot. I accept the solution.

I am posting a followup question to this solution to accomodate these changes,
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24562902.html

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.

Thanks
0
vblycosAuthor Commented:
Awesome solution. Thank You, I learned about recursive CTE with pivot. Great help.
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.