Post reply

select statement

  • March 4, 2004 at 2:12 am

    #107224

    hi I have the following select statement:-

    select customer,warehouse,sum(CASE DATEPART(yyyy, dated)

    WHEN 1999 THEN val ELSE null END) as y1999,

    sum(CASE DATEPART(yyyy, dated) when 2000 THEN val

    ELSE null END) as y2000  from sales table where warehouse='E' or warehouse='EC'

    group by customer,warehouse

    order by customer

    this works fine however I need to refine the datepart section to tell sql that y1999 is actually between 1st november 1999 and 31 october 2000 and so on.

    Can this statement be amended to do this?

    Thanks for any help!

  • March 4, 2004 at 2:34 am

    #497182

    This syntax ought to work (haven't tested it!)

    sum(CASE 
      when dated >= '1-nov-1999' and dated < '1-nov-2000' then val
      else null
    END)  as y1999,

  • March 4, 2004 at 2:45 am

    #497185

    I tried this but got an error:-

    Error: Incorrect syntax near the keyword 'CASE'. (State:37000, Native Code: 9C)

     

    Any ideas why?

  • March 4, 2004 at 2:49 am

    #497186

    Well what is the format in which the dated field is saved in present context.

    Prasad Bhogadi
    www.inforaise.com

  • March 4, 2004 at 2:52 am

    #497188

     

    I tried this but got an error:-

    Error: Incorrect syntax near the keyword 'CASE'. (State:37000, Native Code: 9C)

    Any ideas why?

    The syntax should be pretty fine unless you forgot to include SELECT in front.

     

    Prasad Bhogadi
    www.inforaise.com

  • March 4, 2004 at 2:54 am

    #497192

    As a certain Mr Simpson would say - Doh!

     

    Thanks for your help!!

Viewing 6 posts - 1 through 5 (of 5 total)

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