none
Run 2 queries depending on date?

    Question

  • How can I combine this into one SQL script?


    SELECT DATE, right('00000000' + SP_ID, 
    case when right(sp_id,1) > '9' then 9 else 8 end) AS VALUE
    FROM WP_CD
    WHERE DATE < 06/30/1994
    
    ELSE
    
    SELECT DATE, ISNULL(Sedol, Cusip) AS VALUE
    FROM WP_CD

    Thanks.



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 28, 2014 6:56 PM

Answers

  • Why you're writing using wrong syntax and expect the things to work? Why not check BOL if it's not clear?

    SELECT [DATE], CASE WHEN [DATE] < '20090101' THEN RIGHT('00000000' + SP_ID,

    CASE WHEN RIGHT(SP_ID,1) > '9' THEN 9 ELSE 8 END) ELSE ISNULL(SEDOL, CUSIP) END AS Value FROM WP_CD



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi NModerator Friday, March 28, 2014 8:23 PM
    • Marked as answer by ryguy72 Friday, March 28, 2014 8:52 PM
    Friday, March 28, 2014 8:21 PM
    Moderator

All replies

  • Whats the IF condition? I think you missed posting it.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 28, 2014 6:58 PM
  • IF DATE < 06/30/1994

    SELECT DATE, right('00000000' + SP_ID, 
    case when right(sp_id,1) > '9' then 9 else 8 end) AS VALUE

    ELSE

    SELECT DATE, ISNULL(Sedol, Cusip) AS VALUE
    FROM WP_CD


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Proposed as answer by Flemming Loth Friday, March 28, 2014 8:51 PM
    Friday, March 28, 2014 7:34 PM
  • Simply transform IF into CASE expression.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 28, 2014 7:37 PM
    Moderator
  • Thanks, but it still doesn't work.

    SELECT 
    CASE WHEN
        (DATE < '01/01/2009'
        (SELECT DATE, RIGHT('00000000' + SP_ID, CASE WHEN RIGHT(SP_ID,1) > '9' THEN 9 ELSE 8 END)) AS VALUE
        ELSE
    DATE, ISNULL(SEDOL, CUSIP) AS VALUE
    END)
    FROM WP_CD
    I changed the date . . . .


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Friday, March 28, 2014 8:08 PM
    Friday, March 28, 2014 8:05 PM
  • Why you're writing using wrong syntax and expect the things to work? Why not check BOL if it's not clear?

    SELECT [DATE], CASE WHEN [DATE] < '20090101' THEN RIGHT('00000000' + SP_ID,

    CASE WHEN RIGHT(SP_ID,1) > '9' THEN 9 ELSE 8 END) ELSE ISNULL(SEDOL, CUSIP) END AS Value FROM WP_CD



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi NModerator Friday, March 28, 2014 8:23 PM
    • Marked as answer by ryguy72 Friday, March 28, 2014 8:52 PM
    Friday, March 28, 2014 8:21 PM
    Moderator
  • Thanks!  That works!  I see Data really drives everything.

    I wonder if the same result can be achieved with a CTE.  I'll try it when I get a chance.

    Thanks again.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 28, 2014 8:52 PM
  • In this particular case this is a very simple statement so I do not see a need for CTE. May be you have a more complex scenario in mind where CTE may come handy.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 28, 2014 9:17 PM
    Moderator