Solved

Aggregate report

Posted on 2009-07-10
4
432 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
Comment Utility

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

10 Experts available now in Live!

Get 1:1 Help Now