Results 1 to 3 of 3

Thread: [RESOLVED] Stored Proc Calc Status "Select Case"?

  1. #1

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Resolved [RESOLVED] Stored Proc Calc Status "Select Case"?

    Hi,

    I'm using SQL Server 2000 to return a recordset with a person's booked leave

    At the moment I get the RS and loop though it comparing the dates with ASP to work out if it's leave in the future, currently on leave or historic

    I would like to move this logic into my query/stored proc. Does anyone know how best to achieve this?

    This is what I currently have but I can't get it to work?

    Code:
    SELECT 
      l.Leave_Id
      , l.Start_Date
      , l.End_Date
      , Status = CASE l.Start_Date
      WHEN l.Start_Date <= GETDATE AND l.End_Date >= GETDATE THEN 'RED' -- Currently on Leave
      WHEN l.Start_Date > GETDATE THEN 'BLUE' -- Future
      WHEN l.End_Date < GETDATE THEN 'BLACK' -- History/In the past
      ELSE 'Unknown'
    FROM Leave l
    WHERE l.User_ID=@User_ID
    ORDER BY l.Start_Date Desc
    Cheers Al

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Stored Proc Calc Status "Select Case"?

    Code:
    SELECT 
      l.Leave_Id
      , l.Start_Date
      , l.End_Date
      , CASE WHEN l.Start_Date <= GETDATE AND l.End_Date >= GETDATE THEN 'RED' -- Currently on Leave
      WHEN l.Start_Date > GETDATE THEN 'BLUE' -- Future
      WHEN l.End_Date < GETDATE THEN 'BLACK' -- History/In the past
      ELSE 'Unknown' END "Status"
    FROM Leave l
    WHERE l.User_ID=@User_ID
    ORDER BY l.Start_Date Desc
    Case When {condition} Then {Expression} End is the general syntax...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: Stored Proc Calc Status "Select Case"?

    szlamany,

    That's bang on thanks a whole bunch for your help, most appreciated!!

    Cheers Al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width