October 25, 2013 at 2:26 pm
Hi All
How can i convert below table into a crosstab using pivot?
I tried but something is missing from my query
This table is the generated from a subset of pivot T-SQL
SELECT
COUNT(tbl.iTBVACLogID) AS 'Total Calls'
,SUM(DATEPART(hh, CAST(tbl.tTimeTotal AS DATETIME))* 60 + DATEPART(MINUTE, CAST(tbl.tTimeTotal AS DATETIME))) AS 'Total Minutes'
,CAST(DATEPART(MONTH, tbl.[dtdate]) AS INT) AS [MONTH]
,tc.szFullName
FROM [dbo].[tblTBVACLog] tbl
INNER JOIN tblXrefCrewTBVAC txref ON tbl.iTBVACLogID = txref.iTBVACLogID
INNER JOIN tblCrew tc ON txref.iCrewID = tc.iCrewID
GROUP BY tc.szFullName, tbl.[dtdate]
CallsMinutesMonthName
1010NAME 1
1010NAME 4
1010NAME 4
1010NAME 4
1010NAME 2
1010NAME 3
112010NAME 3
112010NAME 4
112010NAME 4
112010NAME 4
112010NAME 3
16010NAME 2
118010NAME 3
16010NAME 1
118010NAME 1
219510NAME 2
119110NAME 1
Into this Result
NameJanFebMarAprMayJunJulAugSepOctNovDecTotal CallsTotal Hours
NAME 200000000030034:25:00
NAME 300000000040047:00:00
NAME 100000000040047:18:00
NAME 400000000060066:00:00
I tried with this code:
SELECT piv.szFullName
,[1] AS [January]
,[2] AS [February]
,[3] AS [March]
,[4] AS [April]
,[5] AS [May]
,[6] AS [June]
,[7] AS [July]
,[8] AS [August]
,[9] AS [September]
,[10] AS [October]
,[11] AS [November]
,[12] AS [December]
,piv.[Total Calls]
,RIGHT('0' + CAST((piv.[Total Minutes] / 60) AS VARCHAR(8)),2)+ ':' +
RIGHT('0' + CAST((piv.[Total Minutes] % 60) AS VARCHAR(2)), 2)+ ':00' AS 'Total Hours'
FROM (
SELECT
COUNT(tbl.iTBVACLogID) AS 'Total Calls'
,SUM(DATEPART(hh, CAST(tbl.tTimeTotal AS DATETIME))* 60 + DATEPART(MINUTE, CAST(tbl.tTimeTotal AS DATETIME))) AS 'Total Minutes'
,CAST(DATEPART(MONTH, tbl.[dtdate]) AS INT) AS [MONTH]
,tc.szFullName
FROM [dbo].[tblTBVACLog] tbl
INNER JOIN tblXrefCrewTBVAC txref ON tbl.iTBVACLogID = txref.iTBVACLogID
INNER JOIN tblCrew tc ON txref.iCrewID = tc.iCrewID
GROUP BY tc.szFullName, tbl.[dtdate]
) src
PIVOT(COUNT([MONTH]) FOR [MONTH] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) piv
But not getting it right
NameJanFebMarAprMayJunJulAugSepOctNovDecTotal CallsTotal Hours
NAME 200000000010010:00:00
NAME 300000000010010:00:00
NAME 100000000010010:00:00
NAME 300000000010010:00:00
NAME 400000000010010:00:00
NAME 400000000010010:00:00
NAME 200000000010011:00:00
NAME 100000000010011:00:00
NAME 100000000010012:00:00
NAME 400000000010012:00:00
NAME 400000000010012:00:00
NAME 400000000010012:00:00
NAME 400000000010012:00:00
NAME 300000000010013:00:00
NAME 100000000010013:00:00
NAME 100000000010013:11:00
NAME 200000000010023:15:00
Any help appreciated.
Thank you
sample data:
DECLARE @tblCrew AS TABLE (
[iCrewID] [int] NOT NULL,
[iEMTID] [int] NULL,
[szUserName] [varchar](100) NOT NULL,
[szPassword] [nchar](10) NOT NULL,
[szFullName] [varchar](100) NOT NULL,
[szType] [varchar](1) NOT NULL)
INSERT INTO @tblCrew([iCrewID], [iEMTID], [szUserName], [szPassword], [szFullName], [szType])
SELECT 1, 597936, N'NAME 1', N'9290 ', N'NAME, 1', N'U' UNION ALL
SELECT 2, 588993, N'NAME 2', N'9290 ', N'NAME, 2', N'U' UNION ALL
SELECT 3, 356754, N'NAME 3', N'9290 ', N'NAME, 3', N'U' UNION ALL
SELECT 4, 511650, N'NAME 4', N'9290 ', N'NAME, 4', N'U'
DECLARE @tblTBVACLog AS TABLE (
[iTBVACLogID] [int] NOT NULL,
[iSeqNumID] [int] NOT NULL,
[dtDate] [datetime] NOT NULL,
[iDispatchTypeID] [int] NOT NULL,
[iCallTypeID] [int] NOT NULL,
[dtTimeStart] [datetime] NOT NULL,
[dtTimeEnd] [datetime] NOT NULL,
[tTimeTotal] [time](7) NOT NULL,
[dMilesStart] [bigint] NOT NULL,
[dMilesEnd] [bigint] NOT NULL,
[dMilesTotal] [bigint] NOT NULL,
[iTownID] [int] NOT NULL,
[iHospitalID] [int] NOT NULL,
[iRigID] [int] NOT NULL,
[tTimeDispatch] [time](7) NOT NULL,
[iAlsTypeID] [int] NOT NULL,
[iCrewID] [int] NOT NULL,
[iCPRID] [int] NOT NULL,
[bEpiPen] [bit] NOT NULL,
[bStork] [bit] NOT NULL,
[bIncidentReport] [bit] NOT NULL,
[bNoTransport] [bit] NOT NULL
)
INSERT INTO @tblTBVACLog([iTBVACLogID], [iSeqNumID], [dtDate], [iDispatchTypeID], [iCallTypeID], [dtTimeStart], [dtTimeEnd], [tTimeTotal], [dMilesStart], [dMilesEnd], [dMilesTotal], [iTownID], [iHospitalID], [iRigID], [tTimeDispatch], [iAlsTypeID], [iCrewID], [iCPRID], [bEpiPen], [bStork], [bIncidentReport], [bNoTransport])
SELECT 1, 4, '10/17/2013 00:00:00.000', 1, 1, '10/17/2013 16:04:00.000', '10/17/2013 18:00:00.000', '00:00:00', 0, 0, 0, 5, 3, 1, '14:00:00', 1, 2, 2, 2, 0, 0, 1 UNION ALL
SELECT 2, 5, '10/18/2013 00:00:00.000', 2, 1, '10/18/2013 12:00:00.000', '10/18/2013 13:00:00.000', '00:00:00', 1000, 1020, 20, 1, 6, 5, '11:00:00', 1, 3, 1, 0, 0, 0, 0 UNION ALL
SELECT 5, 8, '10/18/2013 00:00:00.000', 1, 2, '10/18/2013 05:00:00.000', '10/18/2013 07:00:00.000', '02:00:00', 11, 22, 0, 5, 1, 3, '03:00:00', 2, 1, 3, 2, 0, 0, 0 UNION ALL
SELECT 6, 9, '10/18/2013 00:00:00.000', 3, 2, '10/18/2013 07:00:00.000', '10/18/2013 10:00:00.000', '03:00:00', 0, 0, 0, 4, 1, 2, '03:00:00', 2, 4, 1, 0, 2, 0, 0 UNION ALL
SELECT 7, 10, '10/21/2013 00:00:00.000', 1, 1, '10/21/2013 02:00:00.000', '10/21/2013 12:00:00.000', '10:00:00', 330, 4444, 0, 4, 1, 3, '00:03:00', 1, 4, 1, 0, 0, 0, 0 UNION ALL
SELECT 8, 11, '10/21/2013 00:00:00.000', 2, 1, '10/21/2013 05:00:00.000', '10/21/2013 06:00:00.000', '01:00:00', 1000, 1010, 0, 2, 3, 2, '04:00:00', 5, 2, 1, 0, 0, 0, 0 UNION ALL
SELECT 9, 12, '10/21/2013 00:00:00.000', 1, 2, '10/21/2013 10:00:00.000', '10/21/2013 13:00:00.000', '03:00:00', 44, 55, 11, 5, 5, 3, '06:00:00', 4, 2, 1, 3, 0, 0, 0 UNION ALL
SELECT 10, 13, '10/21/2013 00:00:00.000', 3, 2, '10/21/2013 03:00:00.000', '10/21/2013 05:00:00.000', '02:00:00', 1000, 1010, 10, 1, 4, 2, '02:00:00', 5, 3, 1, 0, 0, 0, 0 UNION ALL
SELECT 11, 14, '10/22/2013 00:00:00.000', 5, 1, '10/22/2013 07:02:00.000', '10/22/2013 09:00:00.000', '01:58:00', 33, 44, 11, 5, 6, 1, '03:03:00', 4, 4, 1, 0, 0, 0, 0 UNION ALL
SELECT 12, 15, '10/22/2013 00:00:00.000', 1, 1, '10/22/2013 08:00:00.000', '10/22/2013 11:00:00.000', '03:00:00', 22, 444, 422, 4, 4, 3, '02:00:00', 5, 3, 1, 0, 0, 0, 0 UNION ALL
SELECT 13, 16, '10/23/2013 00:00:00.000', 2, 1, '10/23/2013 09:00:00.000', '10/23/2013 12:11:00.000', '03:11:00', 11, 22, 11, 5, 12, 3, '06:00:00', 4, 2, 1, 0, 0, 0, 0 UNION ALL
SELECT 14, 17, '10/23/2013 00:00:00.000', 7, 3, '10/23/2013 05:06:00.000', '10/23/2013 05:10:00.000', '00:04:00', 0, 0, 0, 12, 2, 2, '05:00:00', 5, 4, 1, 0, 0, 0, 0 UNION ALL
SELECT 15, 18, '10/25/2013 00:00:00.000', 9, 1, '10/25/2013 03:08:00.000', '10/25/2013 04:00:00.000', '00:52:00', 11, 44, 33, 9, 8, 2, '02:00:00', 4, 3, 1, 0, 0, 0, 0
DECLARE @tblXrefCrewTBVAC as TABLE ([iTBVACLogID] int, [iCrewTypeID] int, [iCrewID] int)
INSERT INTO @tblXrefCrewTBVAC([iTBVACLogID], [iCrewTypeID], [iCrewID])
SELECT 1, 1, 4 UNION ALL
SELECT 1, 1, 3 UNION ALL
SELECT 1, 1, 4 UNION ALL
SELECT 1, 2, 1 UNION ALL
SELECT 2, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 5, 1, 4 UNION ALL
SELECT 5, 1, 2 UNION ALL
SELECT 5, 1, 3 UNION ALL
SELECT 5, 1, 4 UNION ALL
SELECT 5, 2, 1 UNION ALL
SELECT 8, 1, 1 UNION ALL
SELECT 8, 1, 4 UNION ALL
SELECT 9, 1, 2 UNION ALL
SELECT 12, 1, 4 UNION ALL
SELECT 13, 1, 4 UNION ALL
SELECT 13, 2, 1 UNION ALL
SELECT 14, 2, 1 UNION ALL
SELECT 15, 2, 4
SELECT TOP 1000 [iTBVACLogID]
,[iSeqNumID]
,[dtDate]
,[iDispatchTypeID]
,[iCallTypeID]
,[dtTimeStart]
,[dtTimeEnd]
,[tTimeTotal]
,[dMilesStart]
,[dMilesEnd]
,[dMilesTotal]
,[iTownID]
,[iHospitalID]
,[iRigID]
,[tTimeDispatch]
,[iAlsTypeID]
,[iCrewID]
,[iCPRID]
,[bEpiPen]
,[bStork]
,[bIncidentReport]
,[bNoTransport]
FROM @tblTBVACLog
SELECT [iTBVACLogID]
,[iCrewTypeID]
,[iCrewID]
FROM @tblXrefCrewTBVAC
SELECT * FROM @tblCrew
October 25, 2013 at 2:57 pm
Hey Alex,
Throw your test data into some readily consumable code and I'll be happy to show you how to do this. See the article at the first "Helpful Link" in my signature line below for how to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2013 at 5:43 pm
I wrote a little CLR stored proc that pivots anything by date. Should be released in the scripts section on Halloween!
Louis
October 26, 2013 at 9:28 am
lnardozi 61862 (10/25/2013)
I wrote a little CLR stored proc that pivots anything by date. Should be released in the scripts section on Halloween!Louis
Will that include some performance testing? Also, will it include the script to build the CLR without C# or will we need to compile it?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2013 at 1:17 pm
No, it's just the script. I figure if you don't already have a CLR library, you probably aren't interested anyway. As far as performance goes it should be pretty linear - it's not doing the grouping of things into months (or whatever period you prefer). It's just to remove the drudgery of writing all those PIVOTS.
You give it some data, it groups by the string fields, pivots the date field (there can only be one), and sums the numeric fields.
You didn't think I'd be THAT helpful, did you? :w00t:
October 27, 2013 at 6:33 pm
Jeff Moden (10/25/2013)
Hey Alex,Throw your test data into some readily consumable code and I'll be happy to show you how to do this.
I'll be a little more charitable than Jeff (who usually even exceeds me in this area) and help you out with some sample data and a solution that might be close to what you need:
WITH SampleData (Calls, [Minutes],[Month],[Name]) AS
(
SELECT 1,0,10,'NAME 1'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 2'
UNION ALL SELECT 1,0,10,'NAME 3'
UNION ALL SELECT 1,120,10,'NAME 3'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 3'
UNION ALL SELECT 1,60,10,'NAME 2'
UNION ALL SELECT 1,180,10,'NAME 3'
UNION ALL SELECT 1,60,10,'NAME 1'
UNION ALL SELECT 1,180,10,'NAME 1'
UNION ALL SELECT 2,195,10,'NAME 2'
UNION ALL SELECT 1,191,10,'NAME 1'
)
SELECT [Name]
,[Jan]=COUNT(CASE WHEN [Month]= 1 THEN Calls END)
,[Feb]=COUNT(CASE WHEN [Month]= 2 THEN Calls END)
,[Mar]=COUNT(CASE WHEN [Month]= 3 THEN Calls END)
,[Apr]=COUNT(CASE WHEN [Month]= 4 THEN Calls END)
,[May]=COUNT(CASE WHEN [Month]= 5 THEN Calls END)
,[Jun]=COUNT(CASE WHEN [Month]= 6 THEN Calls END)
,[Jul]=COUNT(CASE WHEN [Month]= 7 THEN Calls END)
,[Aug]=COUNT(CASE WHEN [Month]= 8 THEN Calls END)
,[Sep]=COUNT(CASE WHEN [Month]= 9 THEN Calls END)
,[Oct]=COUNT(CASE WHEN [Month]=10 THEN Calls END)
,[Nov]=COUNT(CASE WHEN [Month]=11 THEN Calls END)
,[Dec]=COUNT(CASE WHEN [Month]=12 THEN Calls END)
,[Total Calls]=COUNT(Calls)
,[Total Hours]=STUFF(CONVERT(VARCHAR(8), CAST(DATEADD(minute, SUM([Minutes])%60, 0) AS DATETIME), 108),1,2,
SUM([Minutes]/60))
FROM SampleData
GROUP BY [Name];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 27, 2013 at 11:03 pm
Jeff Moden (10/25/2013)
Hey Alex,Throw your test data into some readily consumable code and I'll be happy to show you how to do this. See the article at the first "Helpful Link" in my signature line below for how to do that.
Jeff i added sample data
October 27, 2013 at 11:03 pm
dwain.c (10/27/2013)
Jeff Moden (10/25/2013)
Hey Alex,Throw your test data into some readily consumable code and I'll be happy to show you how to do this.
I'll be a little more charitable than Jeff (who usually even exceeds me in this area) and help you out with some sample data and a solution that might be close to what you need:
WITH SampleData (Calls, [Minutes],[Month],[Name]) AS
(
SELECT 1,0,10,'NAME 1'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 4'
UNION ALL SELECT 1,0,10,'NAME 2'
UNION ALL SELECT 1,0,10,'NAME 3'
UNION ALL SELECT 1,120,10,'NAME 3'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 4'
UNION ALL SELECT 1,120,10,'NAME 3'
UNION ALL SELECT 1,60,10,'NAME 2'
UNION ALL SELECT 1,180,10,'NAME 3'
UNION ALL SELECT 1,60,10,'NAME 1'
UNION ALL SELECT 1,180,10,'NAME 1'
UNION ALL SELECT 2,195,10,'NAME 2'
UNION ALL SELECT 1,191,10,'NAME 1'
)
SELECT [Name]
,[Jan]=COUNT(CASE WHEN [Month]= 1 THEN Calls END)
,[Feb]=COUNT(CASE WHEN [Month]= 2 THEN Calls END)
,[Mar]=COUNT(CASE WHEN [Month]= 3 THEN Calls END)
,[Apr]=COUNT(CASE WHEN [Month]= 4 THEN Calls END)
,[May]=COUNT(CASE WHEN [Month]= 5 THEN Calls END)
,[Jun]=COUNT(CASE WHEN [Month]= 6 THEN Calls END)
,[Jul]=COUNT(CASE WHEN [Month]= 7 THEN Calls END)
,[Aug]=COUNT(CASE WHEN [Month]= 8 THEN Calls END)
,[Sep]=COUNT(CASE WHEN [Month]= 9 THEN Calls END)
,[Oct]=COUNT(CASE WHEN [Month]=10 THEN Calls END)
,[Nov]=COUNT(CASE WHEN [Month]=11 THEN Calls END)
,[Dec]=COUNT(CASE WHEN [Month]=12 THEN Calls END)
,[Total Calls]=COUNT(Calls)
,[Total Hours]=STUFF(CONVERT(VARCHAR(8), CAST(DATEADD(minute, SUM([Minutes])%60, 0) AS DATETIME), 108),1,2,
SUM([Minutes]/60))
FROM SampleData
GROUP BY [Name];
thank you dwain
October 28, 2013 at 6:38 pm
Sorry Alex,
I just didn't have the time to warp the data into a table.
Dwain's solution is pretty much the same (just missing pre-aggregation but not a big thing) as what I was going to demonstrate so you're good to go there. The question is, do you understand the code and why it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2013 at 12:32 pm
Jeff Moden (10/28/2013)
Sorry Alex,I just didn't have the time to warp the data into a table.
Dwain's solution is pretty much the same (just missing pre-aggregation but not a big thing) as what I was going to demonstrate so you're good to go there. The question is, do you understand the code and why it works?
Yes i do. it's clear now.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply