Solved

Dynamic SQL Called for?

Posted on 2013-06-21
5
346 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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

Expert Comment

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

Expert Comment

by:bhess1
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

947 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now