Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Aggregate report

Posted on 2009-07-10
4
Medium Priority
?
473 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:vblycos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 24828650

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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 2000 total points
ID: 24828673
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
 
LVL 4

Author Comment

by:vblycos
ID: 24832809
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
 
LVL 4

Author Closing Comment

by:vblycos
ID: 31602199
Awesome solution. Thank You, I learned about recursive CTE with pivot. Great help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question