SQL Counting 2

[jontout]

Hi Folks,

Access 2003.

I'd be grateful for a bit of a kickstart here.
I've got a table with records that have a process day column, anything under 3 and we've met our SLA, 4 days or more and it's a fail.

Here's the relevant data...

RecDate RefNo ProcessBy CompDate ProcessDays
26/09/2007 77920 Joe 27/09/2007 2
26/09/2007 77128 Joe 01/10/2007 4
26/09/2007 21885 Joe 27/09/2007 2
26/09/2007 77841 Joe 01/10/2007 4
26/09/2007 43522 Joe 27/09/2007 2

and my query, which isn't quite right.
It splits the data into 2 rows but doesn't count the ProcessDays.

SELECT tblClaims.ProcessBy, Count(tblClaims.ProcessBy) AS [Count], tblClaims.RecDate,
IIf([tblClaims.ProcessDays]) <3 ,1,0) AS [SLA MET],
IIf([tblClaims.ProcessDays] >3 ,1,0) AS [SLA NOT MET]
FROM tblClaims
WHERE (((tblClaims.RecDate) Between #9/24/2007# And #9/27/2007#))
AND ((tblClaims.ProcessBy)="Joe")
GROUP BY tblClaims.ProcessBy, tblClaims.RecDate, ProcessDays
ORDER BY tblClaims.RecDate;

ProcessBy Count RecDate SLA MET SLA NOT MET
Joe 3 26/09/2007 1 0
Joe 2 26/09/2007 0 1


Sorry if this post looks a bit messy.

Cheers,

Jon

 

[PHV]

What is the expected result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

SELECT ProcessBy, 
       Count(*) AS [Counted], 
       RecDate,
       IIf(ProcessDays <3,'SLA MET',IIf(ProcessDays >3, 'SLA NOT  MET', '3 days'))
FROM tblClaims
WHERE RecDate Between #9/24/2007# And #9/27/2007# AND
      ProcessBy='Joe'
GROUP BY ProcessBy, RecDate
ORDER BY RecDate;

 

[jontout]

Thanks Jerry, I've been pacing the gardens trying to come up with a solution, I expected a Nested IIF was needed.
I'll have a play and see if I can't get the result in 1 row.

Cheers,

Jon

 

[PHV]

What about this ?

SELECT ProcessBy, RecDate, Count(*) AS [Count], 
, Sum(IIf(ProcessDays<=3,1,0)) AS [SLA MET]
, Sum(IIf(ProcessDays >3,1,0)) AS [SLA NOT  MET]
FROM tblClaims
WHERE RecDate Between #2007-09-24# And #2007-09-27#
AND ProcessBy='Joe'
GROUP BY ProcessBy, RecDate
ORDER BY RecDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jontout]

Thanks PHV & Jerry for 2 genius responses!