IIF and Then........................Struggling. 1

[houstonbill]

I am struggling with a query and looking for a bit of help. Below is the SQL of my 1st query. What I am trying to do is create a 2nd query that will tell me how many cases were closed by [CSR Name] in 7 days. Date1 and status1 (I059)Should be when case comes into the department. Date2 and status2 (I082) Should be when it is assigned to [CSR Name]. In reviewing the data, I find that in some cases the 1st date and status is I082 rather than the normal I059 status. I need to measure the time from when it was in status I082 (Date1 or Date2)and when it was closed [Closed Dt] by the [CSR Name]. I can do everything but the expression which I think will be "IIF and Then".


FROM RespCdeLst RIGHT JOIN (tblLastUpdated LEFT JOIN tblDeskCodes ON tblLastUpdated.[Desk Cd] = tblDeskCodes.[Desk Code]) ON RespCdeLst.RespCde = tblLastUpdated.[Resp Cd]
GROUP BY RespCdeLst.RespMain, tblLastUpdated.[Closed Dt], tblDeskCodes.[CSR Name], tblLastUpdated.Date1, tblLastUpdated.Status1, tblLastUpdated.Date2, tblLastUpdated.Status2
HAVING (((tblLastUpdated.[Closed Dt]) Like "*2007*"));

 

[SkipVought]

Hi,

I think that you missed the Select clause in your post.

Skip,

_[red][/red]

 

[houstonbill]

I'm sorry, appears my cut and past didn't work. Here it is.

SELECT Count(tblLastUpdated.[Closed Dt]) AS [CountOfClosed Dt], RespCdeLst.RespMain, tblLastUpdated.[Closed Dt], tblDeskCodes.[CSR Name], tblLastUpdated.Date1, tblLastUpdated.Status1, tblLastUpdated.Date2, tblLastUpdated.Status2
FROM RespCdeLst RIGHT JOIN (tblLastUpdated LEFT JOIN tblDeskCodes ON tblLastUpdated.[Desk Cd]=tblDeskCodes.[Desk Code]) ON RespCdeLst.RespCde=tblLastUpdated.[Resp Cd]
GROUP BY RespCdeLst.RespMain, tblLastUpdated.[Closed Dt], tblDeskCodes.[CSR Name], tblLastUpdated.Date1, tblLastUpdated.Status1, tblLastUpdated.Date2, tblLastUpdated.Status2
HAVING (((tblLastUpdated.[Closed Dt]) Like "*2007*"));

 

[r937]

is this what you wanted?

SELECT Count(U.[Closed Dt]) AS [CountOfClosed Dt]
     , R.RespMain
     , U.[Closed Dt]
     , D.[CSR Name]
     , U.Date1
     , U.Status1
     , U.Date2
     , U.Status2
     , [b]U.[Closed Dt] -
         IIF(U.Status1 = 'I082',U.Date1,
         IIF(U.Status2 = 'I082',U.Date2,
         U.[Closed Dt])) as days_diff[/b]
  FROM (
       tblLastUpdated as U
LEFT OUTER 
  JOIN tblDeskCodes as D
    ON D.[Desk Code] = U.[Desk Cd]
       )
LEFT OUTER
  JOIN RespCdeLst as R
    ON R.RespCde = U.[Resp Cd]
 WHERE U.[Closed Dt] Like '*2007*'  
GROUP 
    BY R.RespMain
     , U.[Closed Dt]
     , D.[CSR Name]
     , U.Date1
     , U.Status1
     , U.Date2

r937.com | rudy.ca

 

[houstonbill]

That does it! I did have to add Status2 at the end but it does exactly what I was looking for, but obviously going about it in a different manor. You have been a life saver and this is but another addition to my sample file for future reference.

 

[r937]

I did have to add Status2 at the end

thanks for spotting that -- faulty copy/paste from the SELECT into the GROUP BY


did you notice also that i moved your HAVING condition into the WHERE clause? stupid %#$*%* Access!!!

r937.com | rudy.ca

 

[lespaul]

Here's more on what Rudy's talking about regarding the WHERE/HAVING clause:

you only need to use a HAVING clause to filter on an aggregate function. Say you wanted to see total orders by customer:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customer

this will return:
[tt]
1 10000
2 20000
[/tt]
if you only want customers who live in California, you would put that in a WHERE clause:

SELECT Customer, SUM(OrderTOtal) From Orders WHERE State = "CA" Group by Customer

If you only want customers who have a OrderTotal sum of greater than 15000 then you would use a HAVING clause:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customers HAVING Sum(OrderTOtal) > 15000

To get customers in California with an OrderTotal > 15000 then:

SELECT Customer, SUM(OrderTotal) FROM Orders WHERE State = "CA" GROUP BY Customer HAVING Sum(OrderTotal) > 15000

Hope you have a better understanding of the difference between a WHERE clause and the HAVING clause.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins