Solved

Aggregate report

Posted on 2009-07-10
4
440 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
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to show the last employee that updated a record 5 44
Incremental load example 2 52
How to query LOCK_ESCALATION 4 40
Passing value to a stored procedure 8 91
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now