Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot

Author  Topic 

Sacha
Starting Member

10 Posts

Posted - 2013-07-19 : 08:57:46
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

52326 Posts

Posted - 2013-07-20 : 08:19:13
[code]
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
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-20 : 08:20:19
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

10 Posts

Posted - 2013-07-22 : 04:18:00
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

52326 Posts

Posted - 2013-07-22 : 04:22:10
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

30421 Posts

Posted - 2013-07-22 : 06:48:49
[code]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;[/code]

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

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-22 : 07:15:56
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

52326 Posts

Posted - 2013-07-22 : 07:30:51
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

10 Posts

Posted - 2013-07-22 : 08:41:41
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-22 : 08:48:55
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

10 Posts

Posted - 2013-07-22 : 10:55:24
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

52326 Posts

Posted - 2013-07-22 : 11:02:45
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

30421 Posts

Posted - 2013-07-22 : 11:25:20
[code]-- 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;[/code]


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

Sacha
Starting Member

10 Posts

Posted - 2013-09-04 : 09:59:42
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
   

- Advertisement -