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 paymentsunion allselect 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 ainner join CTE d on a.pkey = d.pkeywhere 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 Decemberfrom(select *, datename(m,AgreeDate) dmonth from CTE) opivot (count(dmonth) for dmonth in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))pgroup by sDatewith rollup

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 datetimeset @d = '01-'+'Jul-09' -- the 'Jul-09' part would be replaced with the parameter valuedeclare @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) srcepivot(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])) pgroup by cdatewith rollup'exec(@sql)

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))asdeclare @d datetimeset @d = '01-'+@perioddeclare @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) srcepivot(sum(counter) for period in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pgroup by cdatewith rollup'exec(@sql)Go-- then to use it:usp_pivot_payments 'Jul-09'

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.

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

So every once in a while at work I am asked to export data from one table and insert it into another on a different server. I hate doing this. There's so many different tables and data types. Some column data needs quoted and some doesn't. What …

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…