November 9, 2012 at 6:52 am
Hello everyone. I was sidetracked with another project but have finally returned to this problem. After a few considerations I believe my dilemma can be solved with the PIVOT operator. I have tried employing the below code but keep coming back with error messages. I am thinking I have a concept of the pivot operator not correct. Can someone help point out where I'm going wrong?
Keep in mind this code does not work
select 'AvgCasesByWeekDay' AS CasesByWeekDay
, [1] AS Monday
, [2] AS Tuesday
, [3] AS Wednesday
, [4] AS Thursday
, [5] AS Friday
, [6] AS Saturday
, [7] AS Sunday
From
(Select DATEPART(DW,CaseDate) FROM CaseData) CD
Pivot
(
Count(CaseDate)
For DATEPART(DW,CaseDate) IN ([1],[2],[3],[4],[5],[6],[7])
)
AS PivotTable;
November 9, 2012 at 7:45 am
try this...
select 'AvgCasesByWeekDay' AS CasesByWeekDay
, [1] AS Monday
, [2] AS Tuesday
, [3] AS Wednesday
, [4] AS Thursday
, [5] AS Friday
, [6] AS Saturday
, [7] AS Sunday
From
(Select DATEPART(DW,CaseDate) AS [Day] FROM CaseData) CD
Pivot
(
Count([Day])
For [Day] IN ([1],[2],[3],[4],[5],[6],[7])
)
AS PivotTable;
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 9, 2012 at 8:22 am
Greg,
Thanks. The results came back and provided a count. I see what I was missing as well.
Now I want to get the Average of the count.
When I try
AVG(count([Day]))
in the Pivot, SQL doesn't like it and tells me incorrect syntax.
Is there some aggregation I need to do in the subquery first?
November 9, 2012 at 8:57 am
kwoznica (11/9/2012)
Greg,Thanks. The results came back and provided a count. I see what I was missing as well.
Now I want to get the Average of the count.
When I try
AVG(count([Day]))
in the Pivot, SQL doesn't like it and tells me incorrect syntax.
Is there some aggregation I need to do in the subquery first?
I think I need to create a while loop for each day. For instance if I run just
Select DATEPART(DW,CaseDate) AS [Day] FROM CaseData
I will get values such as
2
2
2
2
3
5
3
2
3
3
3
4
4
2
5
6
The Pivot tells me that for Day 2 which is Monday I have a count of 6.
Now what I need to know is that on each date that was a monday what was the average number of cases logged.
For instance on 11/5 a count of 4 cases were logged.
On 10/29 only a count of 2 cases were logged.
My Average is therefore 3.
How do I get that extra step of aggregating for the average? I am confused.
November 9, 2012 at 10:15 am
kwoznica (11/9/2012)
How do I get that extra step of aggregating for the average? I am confused.
I think I know what you are looking for, but just so I don't get confused too, it would help if you provide the results you are trying to achieve. And I am about 99.99% certain you will not need a WHILE loop.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 9, 2012 at 10:33 am
Something that looks like the below result set. Where we have an aggregate average on the day of the week
DayOfWeek Sunday Monday Tuesday Wednesday Thursday Friday Saturday
AvgCasesPerDay 1 12 11.2 13 10.9 9.8 2
These results tell me that Wednesday generates the greatest number of cases on average.
November 9, 2012 at 11:07 am
kwoznica (11/9/2012)
Something that looks like the below result set. Where we have an aggregate average on the day of the week
DayOfWeek Sunday Monday Tuesday Wednesday Thursday Friday Saturday
AvgCasesPerDay 1 12 11.2 13 10.9 9.8 2
These results tell me that Wednesday generates the greatest number of cases on average.
This will get you rather close. by using a CTE to generate your pivot you can have a week by week and then an average of all the weeks by day. I also included my setup code to generate some test data
CREATE TABLE CaseData (
CaseDataID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CaseDate DATETIME,
CustomerID INT, --using id since a random number is easier to generate
CategoryID INT, --again random numbers are easier
)
INSERT INTO CaseData (CaseDate, CustomerID, CategoryID)
SELECT TOP 1000 DATEADD(DD,ABS(CHECKSUM(NEWID())) % 21,'2012-11-04') AS CaseDate,
ABS(CHECKSUM(NEWID())) % 100 + 1 AS CustomerID,
ABS(CHECKSUM(NEWID())) % 5 + 1 AS CategoryID
FROM Tally
;WITH basePVT AS (
SELECT WeekNum AS [Week]
, [1] AS Monday
, [2] AS Tuesday
, [3] AS Wednesday
, [4] AS Thursday
, [5] AS Friday
, [6] AS Saturday
, [7] AS Sunday
FROM(
SELECT DATEPART(WW,CaseDate) AS WeekNum, DATEPART(DW,CaseDate) AS DayWeek
FROM CaseData
) p
PIVOT
(
COUNT(DayWeek)
FOR DayWeek IN ([1],[2],[3],[4],[5],[6],[7])
)pvt
)
SELECT CAST([Week] AS VARCHAR), Monday,
Tuesday,
Wednesday,
Thursday,
Friday,
Saturday,
Sunday
FROM basePVT
UNION
SELECT 'Average', AVG(Monday),
AVG(Tuesday),
AVG(Wednesday),
AVG(Thursday),
AVG(Friday),
AVG(Saturday),
AVG(Sunday)
FROM basePVT
I use my tally table because it is an easy way to have rows and not rely on one of the sys. tables.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 9, 2012 at 11:56 am
Thanks Jeremy. I will give this a try and see if I am successful.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply