Solved

Dynamic SQL Called for?

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
SQL Syntax 6 32
using a SELECT statement in a  THEN  of a CASE statement. 5 14
A question about syntax 5 27
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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