Here's the code and result:
SELECT [Arrival-Time], [Assign-To- Group], Priority,
DATEDIFF(day, [Arrival-Time], COALESCE ([Resolve-Time], GETDATE())) AS Days_Open
Arrival-Time Assign-To-Group Priority Days_Open
4/23/2001 3:17:00 PM HD-System Administration Sev-4 Request 1
5/25/2001 12:15:00 PM HD-Helpdesk Sev-3 Medium 0
4/26/2001 11:24:00 AM HD-SMO Telecom Sev-3 Medium 0
4/23/2001 1:13:00 PM HD-SAC Desktop/Hardware Sev-3 Medium 0
6/22/2001 7:00:00 AM HD-SMO Application Support Sev-3 Medium 6
6/7/2001 1:10:00 PM FTCI-HelpDesk Sev-3 Medium 0
Everyone's help is greatly appreciated!
jem
Can you provide your exact query and data on which it fails?
Steve
I like the Coalesce solution: clean and simple. Unfortunately, if the Resolve date is null, it is not calculating using getdate...the delta is 0. It's accepting the syntax, but not returning a valid value. Any ideas? jem
IF THEN ELSE is not an expression in SQL Server, and it can't be used this way. You can always use CASE in these situations, since CASE is an expression - here, COALESCE is useful also. For the Days_Open you want, try either
DATEDIFF
(day, [Arrival-Time]
CASE WHEN [Resolve-Time] IS NULL THEN GETDATE()
ELSE [Resolve-Time]
END)
or more simply, in this situation
DATEDIFF(day, [Arrival-Time], COALESCE([Resolve-Time],GETDATE()))
Steve Kass
Drew University
I need to create a simple select statement that returns the number of days a
"ticket" has been open (CloseDate-OpenDate). But if there is no close date,
I want to calculate the delta based on today's date. I can't seem to get
the syntax right. Can anyone help? Here's what I have:
SELECT [Arrival-Time], Priority, [Resolve-Time],
IF ([Resolve-Time] IS NULL THEN
DATEDIFF(day, [Arrival-Time], GETDATE())
ELSE DATEDIFF(day, [Arrival-Time], [Resolve-Time])) AS
Days_Open
FROM dbo.tblHelpDesk
GROUP BY [Assign-To- Group+], [Arrival-Time], [Priority], [Days_Open]
Jamie Mount
by