SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sacha
Starting Member

5 Posts

Posted - 07/19/2013 :  08:57:46  Show Profile  Reply with Quote
Hello,
I'm trying to make a query.
I made this:

SELECT TOP (100) PERCENT LastAssignedWorkgroupID AS Group, COUNT(RemoteNumberFmt) AS Total
FROM dbo.InteractionSummary
WHERE (Direction = '1') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-15 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-19 23:00:00',
102))
GROUP BY LastAssignedWorkgroupID
HAVING (LastAssignedWorkgroupID IN (N'Test1',N'Test2'))

The result is:
Group Total
Test1 500
Test2 600

So far so good. But i want it like a pivottable with the dates.
Like this:

07-15 07-16 07-17 07-18 07-19
Test1 125 125 75 150 125
Test2 200 100 0 150 150

Is this possible?
I'm using SQL 2008 and MS SQL sever management studio.
Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/20/2013 :  08:19:13  Show Profile  Reply with Quote

SELECT *
FROM
(
SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS Total
FROM dbo.InteractionSummary
WHERE (Direction = '1') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-15 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-19 23:00:00', 
102))
WHERE LastAssignedWorkgroupID IN (N'Test1',N'Test2')
)t
PIVOT(SUM(Amount) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/20/2013 :  08:20:19  Show Profile  Reply with Quote
to make dates dynamic use

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sacha
Starting Member

5 Posts

Posted - 07/22/2013 :  04:18:00  Show Profile  Reply with Quote
Thank you visakh16!
But when i run the query i get an error message;
Incorrect syntax near the keyword Where.
So i change this line in: Where (LastAssignedWorkgroupID IN (N'Test1, N'Test2')
)t
But that does not help.
Any idea's?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/22/2013 :  04:22:10  Show Profile  Reply with Quote
try

SELECT *
FROM
(
SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS Total
FROM dbo.InteractionSummary
WHERE (Direction = '1') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-15 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-19 23:00:00', 
102))
WHERE LastAssignedWorkgroupID IN (N'Test1,Test2')
)t
PIVOT(SUM(Amount) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/22/2013 :  06:48:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WITH cteSource(theGroup, theDay)
AS (
	SELECT	LastAssignedWorkgroupID AS theGroup,
		CONVERT(CHAR(5), StartDateTimeUTC, 110) AS theDay
	FROM	dbo.InteractionSummary
	WHERE	Direction = '1'
		AND StartDateTimeUTC > '20130715 04:00:00'
		AND StartDateTimeUTC < '20130719 23:00:00'
		AND LastAssignedWorkgroupID IN (N'Test1', N'Test2')
)
SELECT		theGroup,
		SUM(CASE WHEN theDay = '07-15' THEN 1 ELSE 0 END) AS [07-15],
		SUM(CASE WHEN theDay = '07-16' THEN 1 ELSE 0 END) AS [07-16],
		SUM(CASE WHEN theDay = '07-17' THEN 1 ELSE 0 END) AS [07-17],
		SUM(CASE WHEN theDay = '07-18' THEN 1 ELSE 0 END) AS [07-18],
		SUM(CASE WHEN theDay = '07-19' THEN 1 ELSE 0 END) AS [07-19]
FROM		cteSource
GROUP BY	theGroup;


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 07/22/2013 06:49:51
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  07:15:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

try

SELECT *
FROM
(
SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS Total
FROM dbo.InteractionSummary
WHERE (Direction = '1') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-15 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-19 23:00:00', 
102))
WHERE LastAssignedWorkgroupID IN (N'Test1,Test2')
)t
PIVOT(SUM(AmountTotal) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



visakh,
please do not be angry and furious.
but sounds like this to me you typed "Amount" instead of "Total"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/22/2013 :  07:30:51  Show Profile  Reply with Quote
quote:
Originally posted by sigmas

quote:
Originally posted by visakh16

try

SELECT *
FROM
(
SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS Total
FROM dbo.InteractionSummary
WHERE (Direction = '1') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-15 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-19 23:00:00', 
102))
WHERE LastAssignedWorkgroupID IN (N'Test1,Test2')
)t
PIVOT(SUM(AmountTotal) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



visakh,
please do not be angry and furious.
but sounds like this to me you typed "Amount" instead of "Total"



I'm neither angry nor furious
Just wanted you to get essence of posts rather than picking on smaller things!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sacha
Starting Member

5 Posts

Posted - 07/22/2013 :  08:41:41  Show Profile  Reply with Quote
The error was causes by the content of the column i used. I change that and now i get results i want the see.
To get it dynamic will now be the challenge......
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 07/22/2013 :  08:48:55  Show Profile  Reply with Quote
You can refer this link for dynamic Pivot
http://ermahblerg.com/2013/05/01/dynamic-pivot-table-sql-server-2008/

--
Chandu
Go to Top of Page

Sacha
Starting Member

5 Posts

Posted - 07/22/2013 :  10:55:24  Show Profile  Reply with Quote
My query look like this;

select * from (
SELECT LastAssignedWorkgroupID AS Group
, DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal
,1 as teller
FROM dbo.InteractionSummary
WHERE (Direction = '1')
AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-07-1 04:00:00', 102))
AND (StartDateTimeUTC < CONVERT(DATETIME, '2013-07-5 23:00:00', 102))
And LastAssignedWorkgroupID IN (N'Test1')
)t
PIVOT(SUM(teller) FOR DateVal IN ([2013-07-01],[2013-07-02],[2013-07-3],[2013-07-4],[2013-07-5]))p

This works fine. But when i want to see another week i have to change the pivot line and also the StartDateTime lines.Is it possible to do that more easier?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/22/2013 :  11:02:45  Show Profile  Reply with Quote
yep..you need to make the pivot list dynamic. see the posted link. it has example link.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/22/2013 :  11:25:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- User supplied parameter values
DECLARE	@FromDate DATETIME = '20130715 04:00:00',
	@StopDate DATETIME = '20130719 23:00:00';

-- Prepare staging table
CREATE TABLE	#Stage
		(
			theGroup NVARCHAR(20) NOT NULL,
			theDay CHAR(5) NOT NULL,
			Items INT NOT NULL
		);

-- Populate staging table
INSERT		#Stage
		(
			theGroup,
			theDay,
			Items
		)
SELECT		LastAssignedWorkgroupID AS theGroup,
		CONVERT(CHAR(5), StartDateTimeUTC, 110) AS theDay,
		COUNT(*) AS Items
FROM		dbo.InteractionSummary
WHERE		Direction = '1'
		AND StartDateTimeUTC > @FromDate
		AND StartDateTimeUTC < @StopDate
		AND LastAssignedWorkgroupID IN (N'Test1', N'Test2')
GROUP BY	LastAssignedWorkgroupID,
		CONVERT(CHAR(5), StartDateTimeUTC, 110);

-- Make the pivot
DECLARE	@SQL NVARCHAR(MAX) = STUFF(
					(
						SELECT		', MAX(CASE WHEN theDay = ' + QUOTENAME(theDay, '''') + ' THEN Items ELSE 0 END) AS ' + QUOTENAME(theDay)
						FROM		(
									SELECT DISTINCT	theDay
									FROM		#Stage
								) AS d
						ORDER BY	theDay
						FOR XML		PATH('')
					), 1, 2, 'SELECT theGroup, ');

-- Build the statement
SET	@SQL += @SQL + N' FROM #Stage GROUP BY theGroup ORDER BY theGroup;'; 

-- Display the result
EXEC	(@SQL);

-- Clean up
DROP TABLE	#Stage;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Sacha
Starting Member

5 Posts

Posted - 09/04/2013 :  09:59:42  Show Profile  Reply with Quote
Ok, THX to you all!

I managed to make this:

SELECT *
FROM (SELECT LastLocalUserID AS Naam, DATEADD(dd, DATEDIFF(dd, 0, StartDateTimeUTC), 0) AS DateVal, 1 AS teller
FROM dbo.InteractionSummary
WHERE (Direction = '2') AND (StartDateTimeUTC > CONVERT(DATETIME, '2013-09-2 04:00:00', 102)) AND (StartDateTimeUTC < CONVERT(DATETIME,
'2013-09-6 23:00:00', 102)) AND LastLocalUserID IN (N'DLE') t PIVOT (SUM(teller) FOR DateVal IN ([2013-09-2],
[2013-09-3], [2013-09-4], [2013-09-5], [2013-09-6])) p

And that works, but in the results when there is no value i get NULL in the view. Looks no so nice, How can i avoid that?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000