Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Pivot in T-SQL Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 2:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:14 PM
Points: 1,298, Visits: 1,886
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]

Calls Minutes Month Name
1 0 10 NAME 1
1 0 10 NAME 4
1 0 10 NAME 4
1 0 10 NAME 4
1 0 10 NAME 2
1 0 10 NAME 3
1 120 10 NAME 3
1 120 10 NAME 4
1 120 10 NAME 4
1 120 10 NAME 4
1 120 10 NAME 3
1 60 10 NAME 2
1 180 10 NAME 3
1 60 10 NAME 1
1 180 10 NAME 1
2 195 10 NAME 2
1 191 10 NAME 1

Into this Result
Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total Calls	Total Hours
NAME 2 0 0 0 0 0 0 0 0 0 3 0 0 3 4:25:00
NAME 3 0 0 0 0 0 0 0 0 0 4 0 0 4 7:00:00
NAME 1 0 0 0 0 0 0 0 0 0 4 0 0 4 7:18:00
NAME 4 0 0 0 0 0 0 0 0 0 6 0 0 6 6: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
Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total Calls	Total Hours
NAME 2 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 3 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 1 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 3 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 0:00:00
NAME 2 0 0 0 0 0 0 0 0 0 1 0 0 1 1:00:00
NAME 1 0 0 0 0 0 0 0 0 0 1 0 0 1 1:00:00
NAME 1 0 0 0 0 0 0 0 0 0 1 0 0 1 2:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 2:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 2:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 2:00:00
NAME 4 0 0 0 0 0 0 0 0 0 1 0 0 1 2:00:00
NAME 3 0 0 0 0 0 0 0 0 0 1 0 0 1 3:00:00
NAME 1 0 0 0 0 0 0 0 0 0 1 0 0 1 3:00:00
NAME 1 0 0 0 0 0 0 0 0 0 1 0 0 1 3:11:00
NAME 2 0 0 0 0 0 0 0 0 0 1 0 0 2 3: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




Alex S
Post #1508593
Posted Friday, October 25, 2013 2:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 35,615, Visits: 32,210
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1508601
Posted Friday, October 25, 2013 5:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 6:31 AM
Points: 107, Visits: 514
I wrote a little CLR stored proc that pivots anything by date. Should be released in the scripts section on Halloween!

Louis
Post #1508630
Posted Saturday, October 26, 2013 9:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 35,615, Visits: 32,210
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1508662
Posted Saturday, October 26, 2013 1:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 6:31 AM
Points: 107, Visits: 514
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?
Post #1508671
Posted Sunday, October 27, 2013 6:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1508722
Posted Sunday, October 27, 2013 11:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:14 PM
Points: 1,298, Visits: 1,886
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


Alex S
Post #1508746
Posted Sunday, October 27, 2013 11:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:14 PM
Points: 1,298, Visits: 1,886
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


Alex S
Post #1508747
Posted Monday, October 28, 2013 6:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 35,615, Visits: 32,210
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509162
Posted Tuesday, October 29, 2013 12:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:14 PM
Points: 1,298, Visits: 1,886
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.


Alex S
Post #1509524
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse