I am writing a qry in Toad for Oracle and single source. I need to determine the average call time between two times when a status is either active or in work:
this is what I have and I am stuck:
case when c.status = 'active' or c.status = 'In Work' then
(C.StartChatTime - C.RequestedTime) * 24 * 60 as "avgtimetoanswer"
ELSE end
any suggestions would be appreciated
avg(case when c.status = 'active' or c.status = 'In Work' then (C.StartChatTime - C.RequestedTime) * 24 * 60 end) as "avgtimetoanswer"
Please note that this is a Microsoft SQL Server forum, so you might find that folk here are not skilled (enough?!!) in Oracle.
@GBritton - will that raise warnings about any NULLs included (as ELSE condition) in the AVG() function? I normally go out of my way to avoid them 'coz it often screws up an APP if it is not expecting to have to handle warning messages 
sure it may raise warnings. I left the rest as an exercise.
That works for me too 
thank you for your response I am going to check out the oracle forums as well
ended up with null values
Then nothing is satisfying the case statement
or
C.StartChatTime or C.RequestedTime is null
I wonder if you could do something like
SUM(CASE WHEN c.status = 'active' or c.status = 'In Work'
THEN (C.StartChatTime - C.RequestedTime) * 24 * 60
ELSE 0
END) AS TotalValue,
SUM(CASE WHEN c.status = 'active' or c.status = 'In Work'
THEN 1
ELSE 0
END) AS CountOfItems
and then make your own average by dividing one by the other. I expect you should get the same result, but this way (if you output it as two values first, then modify the code to divide one by the other - and maybe also compare that against any value that you get from AVG() ) you can see what the intermediate values are, and from that figure out how to debug it if necessary.
e.g. If you get [CountOfItems] = 0 then none of the rows satisfied the
c.status = 'active' or c.status = 'In Work'
criteria, so you could then decide what to do about that.
This would, when you have it all working properly, also fix the issue of Warning about NULL that I raised earlier (although there is probably a better / smarter way to do that - personally I wouldn't know how to do it "smarter" in Oracle)
P.S. you might need to check if either C.StartChatTime or C.RequestedTime is NULL as that would propagate into the SUM'd or AVG'd result. If those columns were created as NOT NULL then I guess no such test is needed. Perhaps just COALESCE them to 0? (although IMO that will distort the AVERAGE by increasing the CountOfItems but not the TotalValues
You seem to be very, very confused. Did you know this is a SQL Forum, not Oracle?
There is no CASE statement in SQL. We have a CASE expression! This is a fundamental concept, regardless of what SQL product you have.
There is no such crap as a generic “status” in RDBMS-- marriage? Employment? Graduation?
I need to determine the average call time between two times when a status is either active or in work: <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
You do not know how to model an event, like a service call, with a pair of (start, end) timestamps. A NULL end timestamp would mean the event is still open. Download a free copy of the Snodgrass book on temporal SQL queries from the University of Arizona.
You also do know SQL shorthand. We would use the IN() predicate:
CASE WHEN service_call_status IN ('ACTIVE', 'IN WORK')
THEN ..
ELSE NULL END;
85 to 95% of the real work in SQL is in the DDL.
I will guess that those two service call status codes mean that the service_call_end_timestamp IS NULL to show it is unresolved. Oh, SQL Server does not use ANSI/ISO Standard temporal math; see why this is a bad place to ask your question? 
WITH Service_Call_Durations
AS
(SELECT service_ticket,
DATEDIFF (SECONDS, service_call_begin_timestamp,
COALESCE (service_call_end_timestamp,
CURRENT_TIMESTAMP) AS answer_duration)
FROM Foobar) --- we have no name! No DDL!
SELECT AVG(answer_duration) AS answer_duration_avg
FROM Service_Call_Durations;
while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused. I realized I posted to the wrong forum but not intentionally and I certainly do know the difference. I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the goru of programming. I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. I will not be posting again in this forum you have secured that decision with your ignorance.
thank you for the help I appreciate it.
To jcelko,
the above response was meant for you. No one in this thread deserved what you said to me.
thank you for your help
Is the below response really how this forum operates. OMG I am so insulted by ICELKO. Please realize how rude you are.
jcelko
19h
ď„‘
You seem to be very, very confused. Did you know this is a SQL Forum, not Oracle?
There is no CASE statement in SQL. We have a CASE expression! This is a fundamental concept, regardless of what SQL product you have.
There is no such crap as a generic “status” in RDBMS-- marriage? Employment? Graduation?
I need to determine the average call time between two times when a status is either active or in work: <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
You do not know how to model an event, like a service call, with a pair of (start, end) timestamps. A NULL end timestamp would mean the event is still open. Download a free copy of the Snodgrass book on temporal SQL queries from the University of Arizona.
You also do know SQL shorthand. We would use the IN() predicate:
CASE WHEN service_call_status IN ('ACTIVE', 'IN WORK')
THEN ..
ELSE NULL END;
85 to 95% of the real work in SQL is in the DDL.
I will guess that those two service call status codes mean that the service_call_end_timestamp IS NULL to show it is unresolved. Oh, SQL Server does not use ANSI/ISO Standard temporal math; see why this is a bad place to ask your question? 
thank you got it to work
while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused. I realized I posted to the wrong forum but not intentionally and I certainly do know the difference. I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the goru of programming. I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. I will not be posting again in this forum you have secured that decision with your ignorance
Definitely not.
Whilst Celko was an eminent authority on SQL, and has written many informative books, his posts are always rude and, these days, rarely contribute anything useful. He has been banned from the other SQL forums that I participate in, and had all his posts removed. He was banned from this one on the old software, but when the new software was installed and users invited to re-register that, sadly, included people who had been previously banned.
Why I don't understand, and you too I presume, is why ADMIN here still hasn't banned him..
Come to think of it, Celko seems to post each Saturday night ...
I'll second that