SELECT IF ELSE - Help!

SELECT IF ELSE - Help!

Post by jamie moun » Fri, 27 Jul 2001 01:00:46



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

 
 
 

SELECT IF ELSE - Help!

Post by Steve Kas » Fri, 27 Jul 2001 01:12:31


Jamie,
  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



 
 
 

SELECT IF ELSE - Help!

Post by Zachary Well » Fri, 27 Jul 2001 01:01:35


Check out CASE in the BOL

--
Zach
---
When in doubt, check out the Books Online,
  its a GREAT source of information!


> 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


 
 
 

SELECT IF ELSE - Help!

Post by Darren Brinksneade » Fri, 27 Jul 2001 01:10:48


CASE
   WHEN [Resolve-Time] IS NULL THEN  DATEDIFF(day, [Arrival-Time],
GETDATE())
    ELSE DATEDIFF(day, [Arrival-Time], [Resolve-Time])
END AS Days_Open

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT


> 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


 
 
 

SELECT IF ELSE - Help!

Post by jamie moun » Fri, 27 Jul 2001 01:34:56


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


  Jamie,
    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

 
 
 

SELECT IF ELSE - Help!

Post by Kirk Dudle » Fri, 27 Jul 2001 01:36:53


Or

SELECT [Arrival-Time], Priority, [Resolve-Time],
 DATEDIFF(day, [Arrival-Time], ISNULL([Resolve-Time], GETDATE())) AS
 Days_Open
FROM    dbo.tblHelpDesk
GROUP BY [Assign-To- Group+], [Arrival-Time], [Priority], [Days_Open]


> 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


 
 
 

SELECT IF ELSE - Help!

Post by Steve Kas » Fri, 27 Jul 2001 01:45:33


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


 
 
 

SELECT IF ELSE - Help!

Post by jamie moun » Fri, 27 Jul 2001 01:58:28


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

 
 
 

1. MYSQL SELECT PROBS--PLS HELP ME

I have a database with the following table.

[Auto_NO][Names][age]
01               paul       21
02               adam     45
03               jeff        19
04               tim        24

Using the "select" statement how could I display row 03 only???

Thanks

Sol

2. Databse project available -- need quotes

3. SELECT from TEXT help please

4. Roles and Security - Newbie Stupid Question Alert

5. SELECT Question (Please Help!)

6. running a date dependent query

7. newbie(select into) please help

8. ConnectWrite Error

9. SELECT DISTINCT Syntax help

10. SELECT Permission denied HELP!!

11. Dynamic Select Statement-Please Help