SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Pivot in T-SQL


Help with Pivot in T-SQL

Author
Message
AlexSQLForums
AlexSQLForums
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2265
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85835 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 615
I wrote a little CLR stored proc that pivots anything by date. Should be released in the scripts section on Halloween!

Louis
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85835 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
lnardozi 61862
lnardozi 61862
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 615
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
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7263 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
AlexSQLForums
AlexSQLForums
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2265
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
AlexSQLForums
AlexSQLForums
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2265
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85835 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
AlexSQLForums
AlexSQLForums
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2265
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search