Solved

Aggregate Report Question - Followup to Q_24560753

Posted on 2009-07-11
4
210 Views
Last Modified: 2012-05-07
Hello, my original question is Q_24560753, the link is:http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24560753.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
0
Comment
Question by:vblycos
  • 3
4 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24837072
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

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 24837958
OK, by way of explanation...


We use the CTE query to build the list of days. Simple query, just add 1 to the previous date until all days have happened.

Then we use that to generate a "vertical" list of dates :

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)
select * from CTE


Now, we also need the different months between agreedate and enddate. For this we use a small "trick". There is a Microsoft table with every SQL, it is there "general description" table for a range of numbers or settings which need a name. It is spt_values in the Master database. We use this to get the number of months between agreedate and enddate. We then use the individual "expanded" rows to get the anniversary dates :

 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

So, now we have our vertical list of days and our horizontal columns using our "trick" to get months.

Now, we need to combine those two things...


declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

 select cdate, agreedate, enddate, frequency, npayments, anniversary, number, 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, frequency, npayments, 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


And that is nearly all we need. In fact, because we are going to pivot, then, it is more than we need. In reality, we only need 3 things for a pivot. The row definition (in our case CDATE), something to aggregate (in our case the number of periods between agreedat and enddate) and something to pivot over (in our case the 12 different periods).

So, let's have a quick look at the pivot using the CTE, and just the "period" wiitout it being an actual date...

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

select *
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


The other small challenge is getting the total. That is easy enough, we can use the ROLLUP function, one small problem, that requires a GROUP BY, which means we need an aggregate  function. So now we are going to get a "bigger" query just so we can get that total...

declare @d datetime
set @d = '01-'+'Jul-09'

;with CTE as (
              select @d as cdate
              union all
              select dateadd(day,1,cdate)
              from CTE
              where cdate < dateadd(month,datediff(month,-1,@d),0) - 1
)

select case when cdate is NULL then 'Total' else convert(varchar,cdate,106) end as cdate
, sum(isnull([0],0)) as Current_Month
, sum(isnull([1],0)) as [Month + 1]
, sum(isnull([2],0)) as [Month + 2]
, sum(isnull([3],0)) as [Month + 3]
, sum(isnull([4],0)) as [Month + 4]
, sum(isnull([5],0)) as [Month + 5]
, sum(isnull([6],0)) as [Month + 6]
, sum(isnull([7],0)) as [Month + 7]
, sum(isnull([8],0)) as [Month + 8]
, sum(isnull([9],0)) as [Month + 9]
, sum(isnull([10],0)) as [Month + 10]
, sum(isnull([11],0)) as [Month + 11]
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


Now, the final problem with Pivot is you have to "know" the results. With a rolling 12 months, we do not actually know what those twelve columns will be... Well, we do, but its name is dependant on the parameter and that means the rolling twelve months could change every time...

That is where we have to introduce the dynamic SQL as the final result shown in the previous posting (except the substring should have been 4,8 not 3,8)...


And as a stored procedure, we get  (and there are lots of other ways to achieve the same result) :



alter procedure usp_Pivot_Payments(@period varchar(20))

as
 

declare @d datetime

set @d = '01-'+@period
 

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),4,8) + ']

, sum(isnull([1],0)) as ['+substring(convert(varchar,dateadd(month,1,@d),106),4,8) + ']

, sum(isnull([2],0)) as ['+substring(convert(varchar,dateadd(month,2,@d),106),4,8) + ']

, sum(isnull([3],0)) as ['+substring(convert(varchar,dateadd(month,3,@d),106),4,8) + ']

, sum(isnull([4],0)) as ['+substring(convert(varchar,dateadd(month,4,@d),106),4,8) + ']

, sum(isnull([5],0)) as ['+substring(convert(varchar,dateadd(month,5,@d),106),4,8) + ']

, sum(isnull([6],0)) as ['+substring(convert(varchar,dateadd(month,6,@d),106),4,8) + ']

, sum(isnull([7],0)) as ['+substring(convert(varchar,dateadd(month,7,@d),106),4,8) + ']

, sum(isnull([8],0)) as ['+substring(convert(varchar,dateadd(month,8,@d),106),4,8) + ']

, sum(isnull([9],0)) as ['+substring(convert(varchar,dateadd(month,9,@d),106),4,8) + ']

, sum(isnull([10],0)) as ['+substring(convert(varchar,dateadd(month,10,@d),106),4,8) + ']

, sum(isnull([11],0)) as ['+substring(convert(varchar,dateadd(month,11,@d),106),4,8) + ']

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],[12])) p

group by cdate

with rollup'
 

exec(@sql)

Go
 

-- then to use it:
 

usp_pivot_payments 'Jul-09'

Open in new window

0
 
LVL 4

Author Closing Comment

by:vblycos
ID: 31602502
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24839544
A pleasure, and very happy to be of assistance.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 36
index  - last use and update 8 50
Truncate vs Delete 63 87
Problem with SqlConnection 5 110
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

17 Experts available now in Live!

Get 1:1 Help Now