Dynamic SQL PIVOT and a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL PIVOT and a view

Author

Topic

Wozer
Starting Member

29 Posts

Posted - 2009-04-02 : 15:18:07

I am trying to make a dynamic PIVOT and am having some issues with it. To give some detail, I have created a view that has all the data that I need. The columns of the view are as follows:

ClaimNumber (int not null)
Contract_Name (nvarchar(20) not null)
Month_Open (nvarchar(30), null)
Year_Open(nvarchar(4), null)
EntryDate (datetime, null)

The point of my PIVOT is to create a table that lists the number of claims opened for each month/year combo for each contract, per the start/end date parameter entered by a user. So, for example the columns would look something like this (Parameter entered being 1/1/07 and 3/20/07:

Contract Jan 07 Feb 07 March 07

The Stored Procedure I am using to do this is:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER  Procedure [dbo].[spOpenMonthly]
	@prmStartDate datetime,
	@prmEndDate datetime
AS

DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
							'],[' + ltrim(Month_Open+ ' '+Year_Open)
							FROM ClaimsOpenMonthly_View01
							ORDER BY '],['+ ltrim(Month_Open+ ' '+Year_Open)
							FOR XML PATH('')
										), 1, 2, '') + ']'

SET @query=
'SELECT * FROM
	(SELECT Contract_Name,
		Year# =Month_Open +'+ ' '+' + Year_Open,
		ClaimNumber
	FROM ClaimsOpenMonthly_View01
	WHERE EntryDate > '+@prmStartDate+' 
	AND EntryDate < '+@prmEndDate+') src
PIVOT (count(ClaimNumber) FOR Year# IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

When I run it I am getting the following error:

Msg 241, Level 16, State 1, Procedure spOpenMonthly, Line 17
Conversion failed when converting datetime from character string.

Does anyone know what might be causing this?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-02 : 17:55:19

can you somehow show us what is in @Query by doing PRINT @query
in SSMS
declare @prmStartDate datetime, @prmEndDate datetime
Set @prmStartDate = DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
SET @prmEndDate = DATEADD(dd,DATEDIFF(dd,0, GETDATE()+7),0)

DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
'],[' + ltrim(Month_Open+ ' '+Year_Open)
FROM ClaimsOpenMonthly_View01
ORDER BY '],['+ ltrim(Month_Open+ ' '+Year_Open)
FOR XML PATH('')
), 1, 2, '') + ']'

SET @query=
'SELECT * FROM
(SELECT Contract_Name,
Year# =Month_Open +'+ ' '+' + Year_Open,
ClaimNumber
FROM ClaimsOpenMonthly_View01
WHERE EntryDate > '+@prmStartDate+'
AND EntryDate < '+@prmEndDate+') src
PIVOT (count(ClaimNumber) FOR Year# IN ('+@listCol+')) AS pvt'

PRITN @query and post the string, might have the answer to yoru question.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 08:13:17

When I first ran it I got the same error as in the OP, but I added PRINT @listCol before I formed the @Query String and this is what showed up:

[April 2006],[August 2007],[February 2006],[January 2005],[January 2006],[July 2006],[June 2006],[March 2006],[May 2006]
Msg 241, Level 16, State 1, Line 17
Conversion failed when converting datetime from character string.

Edit: I ran my original SP with the PRINT @listcol added in, entering the dates of 1/1/06 for startdate and 5/1/06 for enddate the listCol had the exact same values as the one you had me run. For some reason I think that might be significant

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-03 : 09:37:38

April 2006 is not a valid date, therefore, it cannot convert it.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 09:45:42

Could you explain please. Why is April 2006 not a valid date and the other ones are Valid Dates?

Which part is trying to convert April 2006 into a date from a string?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:47:30

how are you passing values for @prmEndDate & @prmStartDate?

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 13:54:08

Right now I am Right Clicking and Doing Execute Stored Procedure and then entering dates into the fields. Eventually it is going to go onto a report where a user would be entering the dates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 14:02:09

ok..try inserting date values in yyyy-mm-dd format and check if it works

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 14:17:25

Same Result, I tried a years span and the ListCol only returned 9 months.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 14:44:18

I think the problem is here:


	WHERE EntryDate > '+@prmStartDate+' 
	AND EntryDate < '+@prmEndDate+') src

you need to convert your parameters to varchar when you concatenate them into @query. They currently are [datetime]

Be One with the Optimizer
TG

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 14:52:44

But EntryDate in the view is a datetime, so wouldn't converting the parameters to nvarchar cause problems?

On another note, I looked through the data of the view and found out the reason I keep getting those months listed no matter what parameters I put in is because it is pulling every month/year combo in the view, not restricting it based on the entry date (But that might be fixed once the other issue is fixed)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 14:55:08

This works:


create table #t (
ClaimNumber int not null
,Contract_Name nvarchar(20) not null
,Month_Open nvarchar(30) null
,Year_Open nvarchar(4) null
,EntryDate datetime null
)

insert #t
select 1, 'tg', 'April', '2009', 'Jan 2 2007 12:00AM' union all
select 2, 'tg', 'April', '2009', 'Jan 2 2007 12:00AM' union all
select 1, 'tg', 'May', '2009', 'Jan 2 2007 12:00AM' union all
select 2, 'tg', 'May', '2009', 'Jan 2 2007 12:00AM' 

go

declare
       @prmStartDate datetime,
	@prmEndDate datetime
select @prmStartDate =  '1/1/07'
       ,@prmEndDate = '3/20/07'


DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
							'],[' + ltrim(Month_Open+ ' '+Year_Open)
							FROM #t
							ORDER BY '],['+ ltrim(Month_Open+ ' '+Year_Open)
							FOR XML PATH('')
										), 1, 2, '') + ']'

SET @query=
'SELECT * FROM
	(SELECT Contract_Name,
		Year# =Month_Open +'+ ''' '''+' + Year_Open, 
		ClaimNumber
	FROM #t
	WHERE EntryDate > '''+ convert(varchar, @prmStartDate) +'''
	AND EntryDate < '''+ convert(varchar, @prmEndDate)+''') src 
PIVOT (count(ClaimNumber) FOR Year# IN ('+@listCol+')) AS pvt'

--print @query
EXECUTE (@Query)

go
drop table #t

OUTPUT:
Contract_Name        April 2009  May 2009
-------------------- ----------- -----------
tg                   2           2

EDIT:
This is what IChanged from your original code

Be One with the Optimizer
TG

Wozer
Starting Member

29 Posts

Posted - 2009-04-03 : 15:00:17

That did work. Thanks a lot for your help. I am still getting all month year combos listed, not just the ones between the months I entered, but I will wrap my head around that one and try to figure it out soon.

Thanks again.

Woz

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 15:03:43

You're welcome...
If you can't figure it out just add some rows to the #t table from my last post that will illustrate the problem.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources