CODEWITHSUNDEEP
sqlsql-server-2005
Jessica
Jessica

Reputation: 239

SQL Select not grouping properly

Another newbie question here,

I have the following Select:

SELECT    
    DATEADD(dd, 0, DATEDIFF(dd, 0, CIC.StatusDateTime)) AS ST_Date,
    CIC.UserId,
    MIN(CIC.StatusDateTime) as Beginning,
    MAX(CIC.EndDateTime) as ending,
    DATEPART(hour,MAX(CIC.EndDateTime) - MIN(CIC.StatusDateTime)) * 3600 
     + DATEPART(minute, MAX(CIC.EndDateTime) - MIN(CIC.StatusDateTime)) * 60 
     + DATEPART(second,MAX(CIC.EndDateTime) - MIN(CIC.StatusDateTime)) AS Login_Time, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 5 THEN StateDuration ELSE 0 END) AS Lunch, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 3 THEN StateDuration ELSE 0 END) AS Break_, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 4 THEN StateDuration ELSE 0 END) AS Coaching, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 10 THEN StateDuration ELSE 0 END) AS SYS_Down, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 7 THEN StateDuration ELSE 0 END) AS Training, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 6 THEN StateDuration ELSE 0 END) AS Meeting, 
    SUM(CASE WHEN RS.AGT_STATE_GRP = 11 THEN StateDuration ELSE 0 END) AS Default_
FROM dbo.CIC_AgentActivityLog_TMP AS CIC
INNER JOIN dbo.REF_AGT_STATES AS RS 
ON CIC.StatusKey = RS.AGT_STATE_DESC
Where CIC.StatusDateTime >='2011-09-01'
GROUP BY CIC.StatusDateTime, CIC.UserId, CIC.EndDateTime
ORDER BY ST_Date,UserID,StatusDateTime;

Using this, my results do not seem to be grouping at all. What I am trying to have is a single row with date,userid,firstlogin,lastlogin,totallogintime,and then a bunch of codes with their duration in seconds. I have something very similar for another data source which works fine.

I have tried converting and using cast with the first date thinking the 00:00:00 at the end might be conflicting, but to no avail. I am unsure what more info you guys might need, just let me know.

*Edit1 - My MIN(CIC.StatusDateTime) as Beginning,MAX(CIC.EndDateTime) as ending seem to not be helping. For an agent, i have three rows that all have different start and end times. The min and max are not giving me the actual min and max for the whole day.

Here is the source Data:

userID  StatusDateTime  StatusKey   EndDateTime StateDuration
bBarsby 9/1/2011 11:36  Out of the office   9/1/2011 11:36  0
bBarsby 9/1/2011 11:36  Out of the office   9/1/2011 11:36  5
bBarsby 9/1/2011 11:36  Available   9/1/2011 14:18  9711
bBarsby 9/1/2011 14:18  Away from desk  9/1/2011 14:39  1261
bBarsby 9/1/2011 14:39  Available   9/1/2011 19:51  18693
bBarsby 9/1/2011 19:51  Out of the office   9/1/2011 19:51  4
bBarsby 9/1/2011 19:51  Out of the office   9/1/2011 19:51  0

Here is the results

ST_Date UserID  Beginning   Ending  LoginTime   Lunch   Break   Coaching      Etc.
9/1/2011 0:00   bBarsby 9/1/2011 11:36  9/1/2011 14:18  9711    0   0   0   0
9/1/2011 0:00   bBarsby 9/1/2011 14:18  9/1/2011 14:39  1261    0   0   0   0
9/1/2011 0:00   bBarsby 9/1/2011 14:39  9/1/2011 19:51  18693   0   0   0   0

Upvotes: 0

Views: 153

Answers (3)

Amy B
Amy B

Reputation: 110091

Aaron Bertrand has the right idea. You absolutely should not define groups by the enddate.

Here's the query re-written with that group by.

SELECT
  sub.ST_Date,
  sub.UserId,
  MIN(sub.StatusDateTime) as Beginning,
  MAX(sub.EndDateTime) as ending,
  DATEPART(hour,MAX(sub.EndDateTime) - MIN(sub.StatusDateTime)) * 3600
  + DATEPART(minute, MAX(sub.EndDateTime) - MIN(sub.StatusDateTime)) * 60
  + DATEPART(second,MAX(sub.EndDateTime) - MIN(sub.StatusDateTime)) AS Login_Time,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 5 THEN StateDuration ELSE 0 END) AS Lunch,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 3 THEN StateDuration ELSE 0 END) AS Break_,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 4 THEN StateDuration ELSE 0 END) AS Coaching,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 10 THEN StateDuration ELSE 0 END) AS SYS_Down,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 7 THEN StateDuration ELSE 0 END) AS Training,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 6 THEN StateDuration ELSE 0 END) AS Meeting,
  SUM(CASE WHEN sub.AGT_STATE_GRP = 11 THEN StateDuration ELSE 0 END) AS Default_
FROM
(
  SELECT 
    DATEADD(dd, 0, DATEDIFF(dd, 0, CIC.StatusDateTime)) AS ST_Date
    CIC.UserId,
    CIC.StatusDateTime,
    CIC.EndDateTime,
    RS.AGT_STATE_GRP,
    StateDuration
  FROM
    dbo.CIC_AgentActivityLog_TMP AS CIC
    JOIN dbo.REF_AGT_STATES AS RS
    ON CIC.StatusKey = RS.AGT_STATE_DESC
  WHERE CIC.StatusDateTime >='2011-09-01'
) sub
GROUP BY sub.ST_Date, sub.UserId
ORDER BY sub.ST_Date, sub.UserId

Upvotes: 1

TheTechGuy
TheTechGuy

Reputation: 17354

use ST_Date in group by as this is the column name in your table.

GROUP BY CIC.StatusDateTime, CIC.UserId, CIC.EndDateTime
to
GROUP BY CIC.ST_DATE, CIC.UserId, CIC.EndDateTime

Edit:

group by DATEADD(dd, 0, DATEDIFF(dd, 0, CIC.StatusDateTime)),CIC.UserId, MAX(CIC.EndDateTime)

Upvotes: 0

anon
anon

Reputation:

You're grouping by CIC.StatusDateTime and CIC.EndDateTime ... try just:

GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, CIC.StatusDateTime)), CIC.UserId

Upvotes: 4

Related Questions