September 23, 2011 at 7:24 am
Hi all,
New to SQL and I thought I had a solution in the following query, but realized that when only one open record exists against a particular product the case statement doesn't evaluate properly:
SELECT p.escProduct AS ep, count(*) as totEsc,
CASE o.ocDateClosed
WHEN NULL THEN
avg(DATEDIFF(DAY, o.ocDateOpened, GETDATE()))
ELSE avg(DATEDIFF(DAY, o.ocDateOpened, o.ocDateClosed))
End AS 'srAge'
FROM tblocMain o INNER JOIN tblescProduct p ON
o.escProductID = p.escProductID
GROUP BY p.escProduct
ORDER BY srAge DESC
What I've come to find is that is that the "simple" version of the syntax assumes that the operator is =, so in my case - NULL=NULL, which by definition of NULL returns a result of UNKNOWN. So the case would fail.
If I don't use the simple case and define o.ocDateClosed individually then I have to include it in the group by, which also fubars the output. Is there a way to accomplish what I'm trying to do in SQL, or do I have to perform it in the application?
I am looking to just have output grouped by product and evaluate open and closed cases for the average length of time they are/were open.
Thanks!
September 23, 2011 at 7:44 am
Hi and welcome to SSC. I have a pretty good idea of where you are heading with this but it is really hard to know for sure. It would be about a million times easier to help if you can provide some ddl, sample data and desired output based on the sample data along with an explanation of exactly what you are trying to do. Take a look at the link in my signature for details about to gather all this information if you are having problems.
Basically it looks like all you need is a couple create table and a few inserts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 7:58 am
I think you can code round this using IsNull():
avg(DATEDIFF(DAY, o.ocDateOpened, IsNull(o.ocDateClosed,GETDATE())))
September 23, 2011 at 8:07 am
You just need to use slightly different syntax.
SELECT p.escProduct AS ep, count(*) as totEsc,
CASE WHEN o.ocDateClosed IS NULL THEN
avg(DATEDIFF(DAY, o.ocDateOpened, GETDATE()))
ELSE avg(DATEDIFF(DAY, o.ocDateOpened, o.ocDateClosed))
End AS 'srAge'
FROM tblocMain o INNER JOIN tblescProduct p ON
o.escProductID = p.escProductID
GROUP BY p.escProduct
ORDER BY srAge DESC
I also think that you may have your AVG() in the wrong scope. I think that your CASE should be inside your AVG() rather than the other way around.
SELECT p.escProduct AS ep, count(*) as totEsc,
Avg(CASE WHEN o.ocDateClosed IS NULL THEN
DATEDIFF(DAY, o.ocDateOpened, GETDATE())
ELSE DATEDIFF(DAY, o.ocDateOpened, o.ocDateClosed)
End) AS 'srAge'
FROM tblocMain o INNER JOIN tblescProduct p ON
o.escProductID = p.escProductID
GROUP BY p.escProduct
ORDER BY srAge DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 23, 2011 at 8:16 am
Thanks all very much!
Sean, I will remember in the future to add the appropriate data, thanks for the pointer to faq.
Drew, that was the ticket, thank you! I tried to use Is NULL in my query, but it didn't like it. Never dawned on me it was because I had the avg in the wrong scope. Much obliged all!
September 23, 2011 at 8:21 am
Glad you figured out the issue and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy