Post reply

case statement

  • March 23, 2010 at 10:13 am

    #100716

    Is it possible to do this in a case statement in T-SQL 2005?

    CASE SCE_AYRC WHEN '05/06' AND C.MAV_BEGW IN('36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52') THEN '06/07' WHEN '06/07' AND C.MAV_BEGW IN('01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35') THEN '06/07' AS FINANCIAL_YEAR,

    This is a part of the statement any idea how to get it to work;-)

  • March 23, 2010 at 10:19 am

    #1138370

    yes you can; the correct syntax is below;

    note i added a suggestion to make it cleaner and easier to read:

    select

    CASE

    WHEN SCE_AYRC = '05/06' AND C.MAV_BEGW IN('36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52')

    THEN '06/07'

    WHEN SCE_AYRC = '06/07' AND C.MAV_BEGW IN('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35')

    THEN '06/07'

    END AS FINANCIAL_YEAR

    --my sugestion

    select

    CASE

    WHEN SCE_AYRC = '05/06' AND CONVERT(int,C.MAV_BEGW) BETWEEN 36 AND 52

    THEN '06/07'

    WHEN SCE_AYRC = '06/07' AND CONVERT(int,C.MAV_BEGW) BETWEEN 1 AND 35

    THEN '06/07'

    END AS FINANCIAL_YEAR

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 24, 2010 at 5:00 am

    #1138791

    Hi Lowell,

    Thanks for the reply. your alternative statement saves so much space and works a treat:w00t:

  • July 2, 2010 at 12:07 am

    #1188014

    hello

    thank you for the code, it could help me with another sql-problem

    regards gg

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply