Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL Called for?

Posted on 2013-06-21
5
Medium Priority
?
359 Views
Last Modified: 2013-06-21
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

0
Comment
Question by:lrbrister
  • 2
  • 2
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39266732
With dynamic SQL, no not really.

It would make sense though to make @CallData into a physical table, so your SQL can be just one SELECT, and the values in CallData aren't hard-coded in this T-SQL.

Just my opinion.
Jim
0
 

Author Closing Comment

by:lrbrister
ID: 39266861
Thanks.
Yeah...I had the @CallData in here just so there was example data

It actually connects to CallData
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39266874
Thanks for the grade.  Good luck with your project.  -Jim
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 39267064
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.
0
 

Author Comment

by:lrbrister
ID: 39267100
bhess1,

  Thanks for the follow-up
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question