September 19, 2010 at 11:16 pm
Hi,
Been a while since I have visited here. I have got a query which gives me a result set shown below.
SEQ GROUPNAME DATADATE DATADAY TOTALCOUNT
1 Mail In 2010-08-16 MON 540
1 Mail In 2010-08-17 TUE 576
1 Mail In 2010-08-18 WED 480
1 Mail In 2010-08-19 THU 198
1 Mail In 2010-08-20 FRI 510
1 Mail In 2010-09-20 TODAY 265
2 Mail Out 2010-08-16 MON 348
2 Mail Out 2010-08-17 TUE 282
2 Mail Out 2010-08-18 WED 462
2 Mail Out 2010-08-19 THU 384
2 Mail Out 2010-08-20 FRI 72
2 Mail Out 2010-09-20 TODAY 307
How do I PIVOT it to get a final result set shown below?
GROUPNAME MON TUE WED THU FRI TODAY TOTAL (ex. TODAY) AVG
Mail In 540 576 480 198 510 265 2304 460.8
Mail Out 348 282 462 384 72 307 1548 309.6
Any help would be appreciated. Just to clarify, "Mail In" and "Mail Out" are just examples, there are more than 10 categories but I didn't feel it was necessary to post all categories and thus making this too long to use.
WRACK
CodeLake
September 19, 2010 at 11:21 pm
Any chance you can post the original query?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 11:30 pm
Sure. I am not sure if this is what you want but here you go.
WITH Rolling5Summary(MatrixOrder, MatrixName, OriginalDate, [DayOfWeek], TotalCount)
AS
(
SELECT CASE keyMatrix
WHEN 'E4B59C9E-FE52-4038-A08B-1BF5958FFB89' THEN 1
WHEN '6FD750DC-128D-4440-ADAE-695927C1C2E9' THEN 2
END AS [MatrixOrder],
CASE keyMatrix
WHEN 'E4B59C9E-FE52-4038-A08B-1BF5958FFB89' THEN 'Mail In'
WHEN '6FD750DC-128D-4440-ADAE-695927C1C2E9' THEN 'Mail Out'
END AS [MatrixName],
kpiDate AS OriginalDate,
CASE
WHEN kpiDate < @Today THEN UPPER(LEFT(DATENAME(WEEKDAY, kpiDate), 3))
WHEN kpiDate = @Today THEN 'TODAY'
END AS [DayOfWeek],
SUM(kpiCount) AS [TotalCount]
FROM kpiData
WHERE keySite = @Site
AND (kpiDate >= @Rolling10StartDate AND kpiDate < @Rolling5EndDate OR kpiDate = @Today)
AND kpiBalancing = 0
AND keyMatrix IN ('E4B59C9E-FE52-4038-A08B-1BF5958FFB89', '6FD750DC-128D-4440-ADAE-695927C1C2E9')
GROUP BY keyMatrix, kpiDate
I am trying to do PIVOT like this but I get syntex error.
SELECT MatrixName, [MON] AS 'MON', [TUE] AS 'TUE', [WED] AS 'WED', [THU] AS 'THU', [FRI] AS 'FRI', [TODAY] AS 'TODAY'
FROM (SELECT MatrixName, [DayOfWeek], TotalCount FROM Rolling5Summary)
PIVOT
(
MIN(TotalCount)
FOR [DayOfWeek] IN ([MON], [TUE], [WED], [THU], [FRI], [TODAY])
) AS Rolling5SummaryPivot
Ideally I don't want a MIN or MAX or SUM but I want the original TotalCount values.
WRACK
CodeLake
September 19, 2010 at 11:46 pm
I felt nice... and yes, that's what I was looking for. 🙂
CREATE TABLE #Rolling5Summary
(MatrixOrder INT,
MatrixName VARCHAR(50),
OriginalDate DATETIME,
[DayOfWeek] VARCHAR(50),
TotalCount INT
)
GO
INSERT INTO #Rolling5Summary
SELECT 1,'Mail In',CAST( '08/16/2010' AS DATETIME ), 'MON', 540 UNION ALL
SELECT 1,'Mail In',CAST( '08/17/2010' AS DATETIME ), 'TUE', 576 UNION ALL
SELECT 1,'Mail In',CAST( '08/18/2010' AS DATETIME ), 'WED', 480 UNION ALL
SELECT 1,'Mail In',CAST( '08/19/2010' AS DATETIME ), 'THU', 198 UNION ALL
SELECT 1,'Mail In',CAST( '08/20/2010' AS DATETIME ), 'FRI', 510 UNION ALL
SELECT 1,'Mail In',CAST( '09/20/2010' AS DATETIME ), 'TODAY', 265 UNION ALL
SELECT 2,'Mail Out',CAST( '08/16/2010' AS DATETIME ), 'MON', 348 UNION ALL
SELECT 2,'Mail Out',CAST( '08/17/2010' AS DATETIME ), 'TUE', 282 UNION ALL
SELECT 2,'Mail Out',CAST( '08/18/2010' AS DATETIME ), 'WED', 462 UNION ALL
SELECT 2,'Mail Out',CAST( '08/19/2010' AS DATETIME ), 'THU', 384 UNION ALL
SELECT 2,'Mail Out',CAST( '08/20/2010' AS DATETIME ), 'FRI', 72 UNION ALL
SELECT 2,'Mail Out',CAST( '09/20/2010' AS DATETIME ), 'TODAY', 307
Get back to you on the rest soonish.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 19, 2010 at 11:51 pm
Thanks Craig.
I am also looking and searching myself and reading this http://qa.sqlservercentral.com/articles/T-SQL/63681/
The query below seems to work. I am still working on the TOTAL and AVERAGE columns.
SELECT MatrixName, [MON] AS 'MON', [TUE] AS 'TUE', [WED] AS 'WED', [THU] AS 'THU', [FRI] AS 'FRI', [TODAY] AS 'TODAY'
FROM (SELECT MatrixOrder, MatrixName, [DayOfWeek], TotalCount FROM Rolling5Summary) AS SourceTable
PIVOT
(
MIN(TotalCount)
FOR [DayOfWeek] IN ([MON], [TUE], [WED], [THU], [FRI], [TODAY])
) AS Rolling5SummaryPivot
ORDER BY MatrixOrder;
As I mentioned earlier, I am doing MIN(TotalCount) but I am hoping that the uniqueness of the value will be ok.
Someone, please correct me if I am going on a wrong path.
WRACK
CodeLake
September 19, 2010 at 11:59 pm
This isn't pretty, but it works:
SELECT MatrixName, [MON] AS 'MON', [TUE] AS 'TUE', [WED] AS 'WED', [THU] AS 'THU', [FRI] AS 'FRI', [TODAY] AS 'TODAY'
, [mon]+[tue]+[wed]+[thu]+[fri]+[today] AS SumCnt, ([mon]+[tue]+[wed]+[thu]+[fri]+[today])/6 AS AvgCnt
FROM (SELECT MatrixOrder, MatrixName, [DayOfWeek], TotalCount FROM #Rolling5Summary) AS SourceTable
PIVOT
(
MIN(TotalCount) -- SUM(TotalCount) works as well.
FOR [DayOfWeek] IN ([MON], [TUE], [WED], [THU], [FRI], [TODAY])
) AS Rolling5SummaryPivot
ORDER BY MatrixOrder;
Also, I think your totals/averages were off.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 12:06 am
Thanks Craig.
I have modified it a little. TOTAL needed to be excluding today as it mentions in the column header of the example data in the first post so AVG changes too.
SELECT MatrixName, [MON] AS 'MON', [TUE] AS 'TUE', [WED] AS 'WED', [THU] AS 'THU', [FRI] AS 'FRI', [TODAY] AS 'TODAY', ([MON] + [TUE] + [WED] + [THU] + [FRI]) AS TOTAL, CAST((CAST(([MON] + [TUE] + [WED] + [THU] + [FRI]) AS DECIMAL) / 5) AS DECIMAL(9, 1)) AS AVERAGE
FROM (SELECT MatrixOrder, MatrixName, [DayOfWeek], TotalCount FROM Rolling5Summary) AS SourceTable
PIVOT
(
MIN(TotalCount)
FOR [DayOfWeek] IN ([MON], [TUE], [WED], [THU], [FRI], [TODAY])
) AS Rolling5SummaryPivot
ORDER BY MatrixOrder;
Thanks for the help again.
WRACK
CodeLake
September 20, 2010 at 12:17 am
WRACK (9/20/2010)
I have modified it a little. TOTAL needed to be excluding today as it mentions in the column header of the example data in the first post so AVG changes too.
Glad to help, and I must have missed this comment somewhere.
There's an alternate way to do it, but I don't think you'll get performance bonus from it. It's unioning on the Total and Average rows to the dataset and then pivoting those as well. I usually find math works faster then more rows, but it might be worth a try, especially since you've already built out the CTE.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 12:26 am
How about this :
Thanks Craig for the wonderful test-setup..
DECLARE @COLS VARCHAR(MAX);
DECLARE @SQL_CASE_STMT VARCHAR(MAX);
DECLARE @SQL_FINAL VARCHAR(MAX);
SET @SQL_CASE_STMT = '' ;
SET @SQL_FINAL = '' ;
SET @COLS = '' ;
SELECT
@SQL_CASE_STMT = @SQL_CASE_STMT
+ ', SUM( CASE WHEN R.[DayOfWeek] = ''' + [DayOfWeek] + ''' THEN TotalCount END ) AS [' + [DayOfWeek] + ']'
+ CHAR(10)
FROM
#Rolling5Summary
GROUP BY
[DayOfWeek] ;
--SELECT @SQL_CASE_STMT
IF (
NULLIF ( ISNULL(@SQL_CASE_STMT ,'') , '') IS NOT NULL
)
BEGIN
SELECT @SQL_FINAL = ' SELECT MatrixName ' + CHAR(10) + @SQL_CASE_STMT + ' , AVG(1.00 * TotalCount) AverageTotalCount FROM #Rolling5Summary R
GROUP BY MatrixName' ;
PRINT @SQL_FINAL ;
EXEC (@SQL_FINAL) ;
END ;
September 20, 2010 at 12:37 am
NP, Coffee, I rarely get a chance to play with pivot so I figured I'd leverage a little MS Excel training. 🙂
Coffee, the result set I get from that is this:
SELECT MatrixName
, SUM( CASE WHEN R.[DayOfWeek] = 'FRI' THEN TotalCount END ) AS [FRI]
, SUM( CASE WHEN R.[DayOfWeek] = 'MON' THEN TotalCount END ) AS [MON]
, SUM( CASE WHEN R.[DayOfWeek] = 'THU' THEN TotalCount END ) AS [THU]
, SUM( CASE WHEN R.[DayOfWeek] = 'TODAY' THEN TotalCount END ) AS [TODAY]
, SUM( CASE WHEN R.[DayOfWeek] = 'TUE' THEN TotalCount END ) AS [TUE]
, SUM( CASE WHEN R.[DayOfWeek] = 'WED' THEN TotalCount END ) AS [WED]
, AVG(1.00 * TotalCount) AverageTotalCount FROM #Rolling5Summary R
GROUP BY MatrixName
So, removing the out of order issues, and to deal with the fact that TODAY shouldn't be in the count... I think this would work a little better: (I also have a thing about dynamic SQL, just bugs me on an instinctual level, dunno why).
SELECT MatrixName
, SUM( CASE WHEN R.[DayOfWeek] = 'MON' THEN TotalCount END ) AS [MON]
, SUM( CASE WHEN R.[DayOfWeek] = 'TUE' THEN TotalCount END ) AS [TUE]
, SUM( CASE WHEN R.[DayOfWeek] = 'WED' THEN TotalCount END ) AS [WED]
, SUM( CASE WHEN R.[DayOfWeek] = 'THU' THEN TotalCount END ) AS [THU]
, SUM( CASE WHEN R.[DayOfWeek] = 'FRI' THEN TotalCount END ) AS [FRI]
, SUM( CASE WHEN R.[DayOfWeek] = 'TODAY' THEN TotalCount END ) AS [TODAY]
, SUM( CASE WHEN r.[DayOfWeek] <> 'TODAY' THEN 1.0 * TotalCount ELSE 0 END ) AS SumCnt
, AVG(CASE WHEN r.[DayOfWeek] <> 'TODAY' THEN 1.0 * TotalCount ELSE 0 END ) AverageTotalCount
FROM #Rolling5Summary R
GROUP BY MatrixName
The only problem with this is the Averages get suckerpunched because of the 'spare' row:
Mail In 540 576 480 198 510 265 2304.0 384.000000
Mail Out 348 282 462 384 72 307 1548.0 258.000000
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 20, 2010 at 2:23 am
WRACK (9/20/2010)
Thanks Craig and ColdCoffee
Our pleasure, WRACK..One more thing i want to add is, just look at how Craig set up the base for others to work on with his CREATE TABLE and INSERT INTO Statements.. You have provided the visual form of your test sample, but haven't provided them in ready-to-use format.. if u do so, then i can promise you, you will get a lot more tested,optimized and revolutionary codes from our fantastic friends here..
@Craig, thanks for pointing it up; i initially thot of aligning the column names (in line with the days of the week) , but was busy with something else, so i just threw a pointer to WRACK on dynamic-SQL.. And thanks for pointing the prob that "TOTAL" "spare" rows caused.. Thanks...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply