asked on

Dynamic SQL Called for?

I just received a solution from a couple of experts for a PIVOT Query.

Works...I'm happy.

Is there a way to do this cleaner and with much less SQL with a Dynamic Query?

Here's the select

declare		@CallData table(CallDTTM datetime, [CallLength] NUMERIC(4,1), CallDirection VARCHAR(30));
insert into @CallData values ('2013-06-10 00:23:30.000', 100.0,'Outbound');
insert into @CallData values ('2013-06-10 00:24:30.000', 105.0,'Outbound');
insert into @CallData values ('2013-06-10 10:23:30.000', 223.0,'Outbound');
insert into @CallData values ('2013-06-10 12:23:30.000', 35.0,'Outbound');
insert into @CallData values ('2013-06-10 14:23:30.000', 44.0,'Outbound');
insert into @CallData values ('2013-06-11 00:23:30.000', 62.0,'Outbound');
insert into @CallData values ('2013-06-11 10:23:30.000', 75.0,'Outbound');
insert into @CallData values ('2013-06-11 12:23:30.000', 302.0,'Outbound');
insert into @CallData values ('2013-06-11 14:23:30.000', 44.0,'Outbound');


DECLARE @callDate AS DATETIME
SET @CallDate = CONVERT(VARCHAR(10),'06/09/2013',101)

DECLARE @CallDirection	VARCHAR(30)
SET		@CallDirection = 'Outbound'

SELECT	CallDate	
		, ISNULL([0],0) [0]
		, ISNULL([1],0) [1]
		, ISNULL([2],0) [2] 
		, ISNULL([3],0) [3]
		, ISNULL([4],0) [4]
		, ISNULL([5],0) [5] 
		, ISNULL([6],0) [6]
		, ISNULL([7],0) [7]
		, ISNULL([8],0) [8] 
		, ISNULL([9],0) [9] 
		, ISNULL([10],0) [10] 
		, ISNULL([11],0) [11]
		, ISNULL([12],0) [12] 
		, ISNULL([13],0) [13]
		, ISNULL([14],0) [14]
		, ISNULL([15],0) [15]
		, ISNULL([16],0) [16]
		, ISNULL([17],0) [17]
		, ISNULL([18],0) [18]
		, ISNULL([19],0) [19]
		, ISNULL([20],0) [20]
		, ISNULL([21],0) [21]
		, ISNULL([22],0) [22]
		, ISNULL([23],0) [23]
FROM	
		(
			SELECT	REPLACE(LEFT(CONVERT(VARCHAR, t1.CallDTTM, 13), 11), ' ', '-')	CallDate
					, DATEPART(HOUR,t1.CallDTTM) as HourFrom
					, ISNULL(t1.[CallLength],0) /60 [Minutes]
			FROM	@CallData t1
		) p1
PIVOT	
		(
			SUM([Minutes])
			FOR	HourFrom in 
				(
					[0], [1], [2], [3],
					[4], [5], [6], [7], 
					[8], [9], [10], [11], 
					[12], [13], [14], [15], 
					[16], [17], [18], [19],
					[20], [21], [22], [23])
		) AS pt1

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Thanks.
Yeah...I had the @CallData in here just so there was example data

It actually connects to CallData

Jim Horn

Thanks for the grade. Good luck with your project. -Jim

Brendt Hess

A tiny simplification (not related to your question): If you replace the 13 format in the Convert statement with a 106 format instead, you can remove the LEFT part of that statement. Alternately, by selecting a VARCHAR(11) format, you will only return the date portion of the 13 formatted datetime.

As for your question in a dynamically formatted query - that depends on how you look at things. The query below depends on some additional tables existing in permanent form - an Hours table containing the values 0 to 23 (or, alternately, an existing tally table with a limit of 0 to 23 on the SELECT statement), and a working table for the data extracted from the source table (called CallHoursReportData below.)

The CallHoursReportData table is defined as:

CREATE TABLE CallHoursReportData (
	SPID smallint,
	CallDate varchar(11),
	HourFrom smallint,
	CallLength decimal(9,4),
	CONSTRAINT pk_CallHoursReportData PRIMARY KEY (SPID, CallDate, HourFrom)
	)

Open in new window

The Hours table I worked with was defined as:

CREATE TABLE Hours (hr smallint PRIMARY KEY CLUSTERED,
	sHr AS (CAST(hr AS varchar(2))))

Open in new window

Assuming these tables exist, and the Hours table is populated, then you can use this query to generate your SQL and run the report:

DELETE FROM CallHoursReportData
	WHERE SPID = @@SPID

INSERT INTO CallHoursReportData
SELECT 
	@@SPID,
	CallDate, 
	HourFrom,
	SUM(Minutes) AS CallLength
FROM (
	SELECT	REPLACE(CONVERT(VARCHAR, t1.CallDTTM, 106), ' ','-')	CallDate
		, DATEPART(HOUR,t1.CallDTTM) as HourFrom
		, ISNULL(t1.[CallLength],0) /60 [Minutes]
	FROM callData AS t1
	)	src
GROUP BY 
	CallDate, 
	HourFrom

DECLARE @sql1 varchar(4000)
DECLARE @sql2 varchar(1000)

SET @SQL1 = 'SELECT CallDate '

SELECT 
	@SQL1 = @SQL1 + ',ISNULL([' + sHr + '],0) [' + sHr + '] ',
	@SQL2 = ISNULL(@SQL2 + ',', '') + '[' + sHr + '] '
	FROM HOURS h
	ORDER BY h.hr

SET @SQL1 = @SQL1 + 'FROM CallHoursReportData PIVOT(SUM(CallLength) FOR HourFrom in (' + @sql2 + ')) as pt1  WHERE SPID=@@SPID'

EXEC (@SQL1)

Open in new window

The query that this generates, when reformatted to be readable, should look familiar to you:

SELECT 
	CallDate ,
	ISNULL([0],0) [0] ,
	ISNULL([1],0) [1] ,
	ISNULL([2],0) [2] ,
	ISNULL([3],0) [3] ,
	ISNULL([4],0) [4] ,
	ISNULL([5],0) [5] ,
	ISNULL([6],0) [6] ,
	ISNULL([7],0) [7] ,
	ISNULL([8],0) [8] ,
	ISNULL([9],0) [9] ,
	ISNULL([10],0) [10] ,
	ISNULL([11],0) [11] ,
	ISNULL([12],0) [12] ,
	ISNULL([13],0) [13] ,
	ISNULL([14],0) [14] ,
	ISNULL([15],0) [15] ,
	ISNULL([16],0) [16] ,
	ISNULL([17],0) [17] ,
	ISNULL([18],0) [18] ,
	ISNULL([19],0) [19] ,
	ISNULL([20],0) [20] ,
	ISNULL([21],0) [21] ,
	ISNULL([22],0) [22] ,
	ISNULL([23],0) [23] 
FROM CallHoursReportData 
PIVOT(
	SUM(CallLength) 
	FOR HourFrom in (
		[0] ,[1] ,[2] ,[3] ,[4] ,[5] ,
		[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,
		[12] ,[13] ,[14] ,[15] ,[16] ,[17] ,
		[18] ,[19] ,[20] ,[21] ,[22] ,[23] 
		)
	) as pt1  
WHERE SPID=@@SPID

Open in new window

The biggest differences are the source of the data (the pre-summarized CallHoursReportData file) and the WHERE clause on the processes @@SPID value.@@SPID value.

Larry Brister

ASKER

bhess1,

Thanks for the follow-up