Help with PIVOT 1

[IanWaterman]

I have a PIVOT query which works fine for Year to date.

However, users now want a rolling 12 months pivot

In simplified form my current query looks like

Select 'DAYS_COUNT' as Measure, [1] as Mth1, [2] as Mth2, [3] as Mth3, [4] as Mth4, [5] as Mth5, .....
from 
(select ID, CalYearNo, CalYearMonthNo from dbo.D_DatePeriod
where CalYearNo = 2014 ) as s
PIVOT
(Count(ID)
FOR  CalYearMonthNo in ([1], [2], [3], [4], [5]...)
) as P

This works fine
However, they now want to change the main query to a range, which rolls forward for each export month

select ID, CalYearNo, CalYearMonthNo from dbo.D_DatePeriod
where date >= '1 March 2013'
and date < '1 March 2014'

I then need to display colums so that 2013-03 = Mth1, 2013-04 = Mth2 etc to 2014-2 Mth12

I assume I also change the PIVOT to
(Count(ID)
FOR date >= '1 March 2013'
and date < '1 March 2014')
) as P

Ideally I should retain the Mth1, Mth2 headers as its going to CSV, but I can live with 2013-03, 2013-04 and sort that out during the export.

THanks for your help

Ian

 

[IanWaterman]

Sorry forgot to mention we have SQL Server 2008

Ian

 

[imex]

One question... which the value of CalYearMonthNo column for the months of 2013-03, 2013-04 and 2014-02?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[IanWaterman]

Calyearmonthno for 2013-03 = 3, it will also have a field calyrno = 2013

Ian

 

[imex]

Try:

Select 'DAYS_COUNT' as Measure, [1] as Mth1, [2] as Mth2, [3] as Mth3, [4] as Mth4, [5] as Mth5, .....
from 
(
    select ID, DATEDIFF(MONTH, '1 March 2013', DtCadsss) + 1 as CalYearMonthNo 
    from dbo.D_DatePeriod
    where date >= '1 March 2013'
    and date < '1 March 2014'
) as s
PIVOT
(
    Count(ID) FOR CalYearMonthNo in ([1], [2], [3], [4], [5]...)
) as P

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[imex]

ps: replace DtCadsss by Date

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[IanWaterman]

Brilliant!

Worked perfectly, thank you

Ian