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.
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 TotalFROM dbo.InteractionSummaryWHERE (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 LastAssignedWorkgroupIDHAVING (LastAssignedWorkgroupID IN (N'Test1',N'Test2'))The result is:Group TotalTest1 500Test2 600So 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 150Is 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 TotalFROM dbo.InteractionSummaryWHERE (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'))tPIVOT(SUM(Amount) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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'))tBut that does not help.Any idea's? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 04:22:10
|
trySELECT *FROM(SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS TotalFROM dbo.InteractionSummaryWHERE (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'))tPIVOT(SUM(Amount) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 cteSourceGROUP BY theGroup;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-22 : 07:15:56
|
quote: Originally posted by visakh16 trySELECT *FROM(SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS TotalFROM dbo.InteractionSummaryWHERE (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'))tPIVOT(SUM(AmountTotal) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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" |
|
|
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 trySELECT *FROM(SELECT LastAssignedWorkgroupID AS Group,DATEADD(dd,DATEDIFF(dd,0,StartDateTimeUTC),0) AS DateVal , COUNT(RemoteNumberFmt) AS TotalFROM dbo.InteractionSummaryWHERE (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'))tPIVOT(SUM(AmountTotal) FOR DateVal IN ([2013-07-15],[2013-07-16],[2013-07-17],[2013-07-18],[2013-07-19]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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...... |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
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 tellerFROM dbo.InteractionSummaryWHERE (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'))tPIVOT(SUM(teller) FOR DateVal IN ([2013-07-01],[2013-07-02],[2013-07-3],[2013-07-4],[2013-07-5]))pThis 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? |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 11:25:20
|
[code]-- User supplied parameter valuesDECLARE @FromDate DATETIME = '20130715 04:00:00', @StopDate DATETIME = '20130719 23:00:00';-- Prepare staging tableCREATE TABLE #Stage ( theGroup NVARCHAR(20) NOT NULL, theDay CHAR(5) NOT NULL, Items INT NOT NULL );-- Populate staging tableINSERT #Stage ( theGroup, theDay, Items )SELECT LastAssignedWorkgroupID AS theGroup, CONVERT(CHAR(5), StartDateTimeUTC, 110) AS theDay, COUNT(*) AS ItemsFROM dbo.InteractionSummaryWHERE 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 pivotDECLARE @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 statementSET @SQL += @SQL + N' FROM #Stage GROUP BY theGroup ORDER BY theGroup;'; -- Display the resultEXEC (@SQL);-- Clean upDROP TABLE #Stage;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
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])) pAnd 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? |
|
|
|
|
|
|
|