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
Solved

Aggregate Report Question - Followup to Q_24560753

Posted on 2009-07-11
4
218 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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 This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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