Distinct Counts and Groups By Shift

Question

Distinct Counts and Groups By Shift

SSSolice

SSCommitted

Points: 1907
More actions
February 25, 2010 at 1:41 pm

#141630

I want to get a distinct count of my LID as well as distinguish between Shift Groups using the LTime associated with each distinct LID. I get the correct count but the SUM on each Shift accounts for ALL rows not the distinct LIDs. Any help is much appreciated.
SELECT
DateName(weekday, LDate)
, COUNT(DISTINCT LID) AS LIDCount
, SUM(CASEWHENLTime < '16:00'THEN1END)ASShift1
, SUM(CASEWHENLTime >='16:00'THEN1END)ASShift2
, SUM(EstimatedPts) AS Pts
FROM dbo.AllSchedLoads
WHERE DepartmentID = '938BEA18-B9B3-4DFA-9F0D-9EBC2586C233'
AND LDate >= '08/01/2009'
AND LDate <= '01/31/2010'
GROUP BY DateName(weekday, LDate)
For example The query above returns:
Day LIDCOUNT Shift1 Shift2 Pts
Friday 1,625 79 1,746 50,117
But it should return:
Day LIDCOUNT Shift1 Shift2 Pts
Friday 1,625 150 1,475 50,117
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Some sample data and table definitions would go a long way towards helping us help you. You can check out the first article in my signature for ideas on how to get this to us.

It looks like your issue is the the Shift 1 and shift 2 numbers is that correct? Is everything else correct? How are the LTime values stored and in what type of column. It might be that you are not getting the results you want because of conversion issues...

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

Have you eyeballed the data? Try this:

SELECT

[Day] = DateName(weekday, LDate),

LID,

Shift1 = CASE WHEN LTime < '16:00' THEN 1 ELSE 0 END,

Shift2 = CASE WHEN LTime >='16:00' THEN 1 ELSE 0 END,

EstimatedPts

FROM dbo.AllSchedLoads

WHERE DepartmentID = '938BEA18-B9B3-4DFA-9F0D-9EBC2586C233'

AND LDate >= '08/01/2009'

AND LDate <= '01/31/2010'

You could if necessary use this as a derived table and run the aggregate against it, the performance would be similar. My guess though, as Luke suggests, is that the data is not quite the same shape as you expect.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

i am not sure but it seems that your "Count" depends on "group by" but you sum includes some case statement which is creating difference/wrong data. Still i would say without sample data , i am not sure

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)