Solved

Dynamic SQL Called for?

Posted on 2013-06-21
5
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

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 66

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

729 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