SQLTeam.com | Weblogs | Forums

Pivot Help Required

sql2012

#1

I am trying to convert a standard query into a pivot so it is easier to use (I am going to create a graph of it).

The standard query produces a table that looks like this: (Sorry I can't upload images as a new user so it's a bit messy)

MonthNo Month Year New Referrals
1 January 2014 51
2 February 2014 56
3 March 2014 42
4 April 2014 35
5 May 2014 42
6 June 2014 53
7 July 2014 65
8 August 2014 68
9 September 2014 49
10 October 2014 60
11 November 2014 50
12 December 2014 29
1 January 2015 44
2 February 2015 33
3 March 2015 44
4 April 2015 35

The Query:

use [exoMashTrust-test]
declare @StartDate date , @EndDate date , @TestDateStart date, @TestDateEnd date
set @StartDate = '20140101 00:00:00.000'
set @EndDate= '20150430 23:59:59.999'
set @TestDateStart = '20150401 00:00:00.000'
set @TestDateEnd = '20150430 23:59:59.999'
--Number of New Referrals Per Month
SELECT
datepart(month,ReferralStartDate) as MonthNo
,datename(month,ReferralStartDate) as Month
,datename(year,ReferralStartDate) as Year
,count(seqno) as [New Referrals]
FROM
	PATIENT_PRIMHD_REFERRAL
WHERE
	ReferralTeamID=29
	and	ReferralStartDate between @StartDate and @EndDate
group by
	datename(year,ReferralStartDate)
	,datepart(month,ReferralStartDate)
	,datename(month,ReferralStartDate) 
order by
	datename(year,ReferralStartDate)
	,datepart(month,ReferralStartDate)
   ;

But I want it to produce a PIVOT table - so I looked at the code above and rewrote the query as:

use [exoMashTrust-test]
declare @StartDate date , @EndDate date , @TestDateStart date, @TestDateEnd date
set @StartDate = '20140101 00:00:00.000'
set @EndDate= '20150430 23:59:59.999'
set @TestDateStart = '20150401 00:00:00.000'
set @TestDateEnd = '20150430 23:59:59.999'
;

--Number of New Referrals Per Month
WITH NewReferrals AS (
SELECT
COUNT(SeqNo) as [New Referrals]
,ReferralStartDate as [Start Date]
FROM
	PATIENT_PRIMHD_REFERRAL
WHERE
	ReferralTeamID=29
	and	ReferralStartDate between @StartDate and @EndDate
GROUP BY
	ReferralStartDate
	)
SELECT 	
[Start Date]
,January, February, March, April, May, June, July 
,August, September, October, November, December
FROM NewReferrals
PIVOT 
	(
	COUNT([New Referrals]) FOR [Start Date]
	IN (January, February, March, April, May, June, July, 
		August, September, October, November, December)
	)
	as PivotTable
;

I get these errors (sorry I don't know how to copy in the line numbers)

Msg 8114, Level 16, State 1, Line 30 - this is the line beginning August, September... four lines from the bottom
Error converting data type nvarchar to datetime.

Msg 473, Level 16, State 1, Line 30
The incorrect value "January" is supplied in the PIVOT operator.

Msg 207, Level 16, State 1, Line 23 - this is the first mention of January in the Select Statement
Invalid column name 'Start Date'.

Disclaimer - I have only been using SQL for less than a month, and learned by watching YouTube and Microsoft training videos, so my knowledge is not as complete as i would like - but I am learning every day.

I am using SQL Server Express 2012

I hope you can help, it is bound to be a simple solution smile

Margo


#2

remove [Start Date] from

SELECT 	
[Start Date]
,January, February, ...

#3

OK so I think I have changed what you told me to, the query now reads:

use [exoMashTrust-test]
declare @StartDate date , @EndDate date , @TestDateStart date, @TestDateEnd date
set @StartDate = '20140101 00:00:00.000'
set @EndDate= '20150430 23:59:59.999'
--set @TestDateStart = '20150401 00:00:00.000'
--set @TestDateEnd = '20150430 23:59:59.999'
;
--Number of New Referrals Per Month
WITH NewReferrals AS (
SELECT
COUNT(SeqNo) as [New Referrals]
,ReferralStartDate as [Start Date]
FROM
PATIENT_PRIMHD_REFERRAL as ppr
WHERE
ReferralTeamID=29
and ReferralStartDate between @StartDate and @EndDate
GROUP BY
ReferralStartDate
)
SELECT
January, February, March, April, May, June, July
,August, September, October, November, December
FROM NewReferrals
PIVOT
(
COUNT([New Referrals]) for [Start Date]
IN (January, February, March, April, May, June, July,
August, September, October, November, December)
)
as PivotTable
;

But I get these errors:

Referring to the line starting IN (January, February... from the PIVOT command (four lines from the bottom
Msg 8114, Level 16, State 1, Line 28
Error converting data type nvarchar to datetime.

Referring to the same line
Msg 473, Level 16, State 1, Line 28
The incorrect value "January" is supplied in the PIVOT operator.


#4

Option 1:

select *
  from (select year(referralstartdate) as [year]
              ,datename(month,referralstartdate) as [monthname]
              ,seqno
          from patient_primhd_referral
         where referralstartdate>=@StartDate
           and referralstartdate<@EndDate
           and referralteamid=29
       ) as a
 pivot (count(seqno) for [monthname]
        in (january
           ,february
           ,march
           ,april
           ,may
           ,june
           ,july
           ,august
           ,september
           ,october
           ,november
           ,december
           )
       ) as p
 order by [year]
;

Option 2:

select *
  from (select year(referralstartdate) as [year]
              ,datename(month,referralstartdate) as [monthname]
              ,count(seqno) as seqno
          from patient_primhd_referral
         where referralstartdate>=@StartDate
           and referralstartdate<@EndDate
           and referralteamid=29
         group by year(referralstartdate)
                 ,datename(month,referralstartdate)
       ) as a
 pivot (sum(seqno) for [monthname]
        in (january
           ,february
           ,march
           ,april
           ,may
           ,june
           ,july
           ,august
           ,september
           ,october
           ,november
           ,december
           )
       ) as p
 order by [year]
;

#6

Thanks, it worked :smile: