September 14, 2015 at 10:08 pm
The query returns the data that I need, but it looks rather sloppy. There are only a few hundred rows in the table at the moment, but expect to reach hundreds of thousands. Performance is a concern in the future. Thank you in advance!
SELECT
CharacterName,
RaidMonth = MONTH(RaidDate),
RaidYear = YEAR(RaidDate),
OnTime = COUNT(CASE WHEN AttendanceReason = 0 THEN 1 END),
LatePost = COUNT(CASE WHEN AttendanceReason = 1 THEN 1 END),
PostOut = COUNT(CASE WHEN AttendanceReason = 2 THEN 1 END),
LateNoPost = COUNT(CASE WHEN AttendanceReason = 3 THEN 1 END),
NoShow = COUNT(CASE WHEN AttendanceReason = 4 THEN 1 END),
Points = SUM(CASE WHEN AttendanceReason = 0 THEN 1.0 WHEN AttendanceReason = 1 THEN .25 WHEN AttendanceReason = 2 THEN 0.0 WHEN AttendanceReason = 3 THEN -.75 WHEN AttendanceReason = 4 THEN -1.0 END),
MaximumPoints = CAST(COUNT(1) AS DECIMAL(18,2)),
Grade = CAST((SUM(CASE WHEN AttendanceReason = 0 THEN 1.0 WHEN AttendanceReason = 1 THEN .25 WHEN AttendanceReason = 2 THEN 0.0 WHEN AttendanceReason = 3 THEN -.75 WHEN AttendanceReason = 4 THEN -1.0 END) / CAST(COUNT(1) AS DECIMAL(18,2))) * 100 AS DECIMAL(18, 2))
FROM RaidHistory
JOIN RaidHistoryAttendees ON RaidHistory.RaidHistoryID = RaidHistoryAttendees.RaidHistoryID
JOIN Characters ON RaidHistoryAttendees.CharacterID = Characters.CharacterID
GROUP BY
CharacterName,
MONTH(RaidDate),
YEAR(RaidDate)
ORDER BY
CharacterName,
MONTH(RaidDate),
YEAR(RaidDate)
September 14, 2015 at 10:53 pm
john_rowles (9/14/2015)
The query returns the data that I need, but it looks rather sloppy. There are only a few hundred rows in the table at the moment, but expect to reach hundreds of thousands. Performance is a concern in the future. Thank you in advance!
SELECT
CharacterName,
RaidMonth = MONTH(RaidDate),
RaidYear = YEAR(RaidDate),
OnTime = COUNT(CASE WHEN AttendanceReason = 0 THEN 1 END),
LatePost = COUNT(CASE WHEN AttendanceReason = 1 THEN 1 END),
PostOut = COUNT(CASE WHEN AttendanceReason = 2 THEN 1 END),
LateNoPost = COUNT(CASE WHEN AttendanceReason = 3 THEN 1 END),
NoShow = COUNT(CASE WHEN AttendanceReason = 4 THEN 1 END),
Points = SUM(CASE WHEN AttendanceReason = 0 THEN 1.0 WHEN AttendanceReason = 1 THEN .25 WHEN AttendanceReason = 2 THEN 0.0 WHEN AttendanceReason = 3 THEN -.75 WHEN AttendanceReason = 4 THEN -1.0 END),
MaximumPoints = CAST(COUNT(1) AS DECIMAL(18,2)),
Grade = CAST((SUM(CASE WHEN AttendanceReason = 0 THEN 1.0 WHEN AttendanceReason = 1 THEN .25 WHEN AttendanceReason = 2 THEN 0.0 WHEN AttendanceReason = 3 THEN -.75 WHEN AttendanceReason = 4 THEN -1.0 END) / CAST(COUNT(1) AS DECIMAL(18,2))) * 100 AS DECIMAL(18, 2))
FROM RaidHistory
JOIN RaidHistoryAttendees ON RaidHistory.RaidHistoryID = RaidHistoryAttendees.RaidHistoryID
JOIN Characters ON RaidHistoryAttendees.CharacterID = Characters.CharacterID
GROUP BY
CharacterName,
MONTH(RaidDate),
YEAR(RaidDate)
ORDER BY
CharacterName,
MONTH(RaidDate),
YEAR(RaidDate)
Quick thought, can you add a month-year column to the table instead of the functions on the group and order columns?
😎
Are you doing month on month comparison or what is the reason for the month-year but not year-month order?
September 14, 2015 at 11:37 pm
Basically, there should only be one row per month-year. I want to know the pointsEarned/maxPoints for a given calendar month.
September 15, 2015 at 1:24 am
Without any further information on the data, structure or even an execution plan, it obviously is a pure guesswork. Can you supply some more information such as DDL, sample data as an insert statement and the actual execution plan for the query?
😎
As a start, this is more like what I would use
;WITH BASE_DATA AS
(
SELECT
CHR.CharacterName
,(YEAR(RH.RaidDate) * 100 ) + MONTH(RaidDate) AS YYYYMM
,CASE WHEN RH.AttendanceReason = 0 THEN 1 END AS OnTime
,CASE WHEN RH.AttendanceReason = 1 THEN 1 END AS LatePost
,CASE WHEN RH.AttendanceReason = 2 THEN 1 END AS PostOut
,CASE WHEN RH.AttendanceReason = 3 THEN 1 END AS LateNoPost
,CASE WHEN RH.AttendanceReason = 4 THEN 1 END AS NoShow
,CASE WHEN RH.AttendanceReason = 0 THEN 1.0
WHEN RH.AttendanceReason = 1 THEN .25
WHEN RH.AttendanceReason = 2 THEN 0.0
WHEN RH.AttendanceReason = 3 THEN -.75
WHEN RH.AttendanceReason = 4 THEN -1.0
END AS Points
,1 AS MaximumPoints
,CASE WHEN RH.AttendanceReason = 0 THEN 1.0
WHEN RH.AttendanceReason = 1 THEN .25
WHEN RH.AttendanceReason = 2 THEN 0.0
WHEN RH.AttendanceReason = 3 THEN -.75
WHEN RH.AttendanceReason = 4 THEN -1.0
END AS GradeBase
FROM RaidHistory RH
JOIN RaidHistoryAttendees RHA
ON RH.RaidHistoryID = RHA.RaidHistoryID
JOIN Characters CHR
ON RHA.CharacterID = CHR.CharacterID
)
SELECT
BD.CharacterName
,BD.YYYYMM
,OnTime = COUNT(BD.OnTime )
,LatePost = COUNT(BD.LatePost )
,PostOut = COUNT(BD.PostOut )
,LateNoPost = COUNT(BD.LateNoPost )
,NoShow = COUNT(BD.NoShow )
,Points = SUM (BD.Points )
,MaximumPoints = CAST(SUM(BD.MaximumPoints) AS DECIMAL(18,2))
,Grade = CAST((SUM(GradeBase) / CAST(SUM(BD.GradeBase) AS DECIMAL(18,2))) * 100 AS DECIMAL(18, 2))
FROM BASE_DATA BD
GROUP BY BD.CharacterName
,BD.YYYYMM
ORDER BY BD.CharacterName
,BD.YYYYMM;
September 15, 2015 at 1:25 am
i agree with Eirikur suggestions.
when you say
john_rowles (9/14/2015)
Basically, there should only be one row per month-year. I want to know the pointsEarned/maxPoints for a given calendar month.
'CharacterName' column in the group that means for each 'CharacterName' you need month-year right? If not then you need to get the comma - separated list of 'CharacterName' columns to get only one.
September 15, 2015 at 2:19 am
john_rowles (9/14/2015)
OnTime = COUNT(CASE WHEN AttendanceReason = 0 THEN 1 END),LatePost = COUNT(CASE WHEN AttendanceReason = 1 THEN 1 END),
PostOut = COUNT(CASE WHEN AttendanceReason = 2 THEN 1 END),
LateNoPost = COUNT(CASE WHEN AttendanceReason = 3 THEN 1 END),
NoShow = COUNT(CASE WHEN AttendanceReason = 4 THEN 1 END),
Should those be SUM() rather than COUNT() ?
September 15, 2015 at 3:05 am
Kristen-173977 (9/15/2015)
john_rowles (9/14/2015)
OnTime = COUNT(CASE WHEN AttendanceReason = 0 THEN 1 END),LatePost = COUNT(CASE WHEN AttendanceReason = 1 THEN 1 END),
PostOut = COUNT(CASE WHEN AttendanceReason = 2 THEN 1 END),
LateNoPost = COUNT(CASE WHEN AttendanceReason = 3 THEN 1 END),
NoShow = COUNT(CASE WHEN AttendanceReason = 4 THEN 1 END),
Should those be SUM() rather than COUNT() ?
No, though SUM will give the same result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2015 at 3:50 am
GilaMonster (9/15/2015)
Kristen-173977 (9/15/2015)
john_rowles (9/14/2015)
OnTime = COUNT(CASE WHEN AttendanceReason = 0 THEN 1 END),LatePost = COUNT(CASE WHEN AttendanceReason = 1 THEN 1 END),
PostOut = COUNT(CASE WHEN AttendanceReason = 2 THEN 1 END),
LateNoPost = COUNT(CASE WHEN AttendanceReason = 3 THEN 1 END),
NoShow = COUNT(CASE WHEN AttendanceReason = 4 THEN 1 END),
Should those be SUM() rather than COUNT() ?
No, though SUM will give the same result.
Aggghhhhh ... misread it as we don't write CASE without ELSE ... so this is relying on COUNT not counting NULL. Doesn't a COUNT that includes NULLs raise a WARNING (if not using some ANSI WARNINGS setting)?
September 15, 2015 at 4:01 am
Test and see?
CREATE TABLE #Test (
SomeValue INT
)
INSERT INTO #Test (SomeValue)
VALUES (1), (1),(1),(NULL),(NULL),(1)
PRINT 'Count'
SELECT COUNT(SomeValue) FROM #Test AS t
PRINT 'Sum'
SELECT SUM(SomeValue) FROM #Test AS t
-----------
Count
-----------
4
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
-----------
Sum
-----------
4
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2015 at 7:55 am
I meant to say there should be one row per CharacterName per calendar month.
Example output: http://imgur.com/wbCO78F
Actual Execution Plan: http://s000.tinyupload.com/download.php?file_id=76029707988028268110&t=7602970798802826811042019
I will provide the other information when I am able to later today. Thanks again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply