locked
if statement within a SELECT to change SUM totals? RRS feed

  • Question

  • I have a VERY simple SELECT statement which (amongst other things) gets a SUM from a total column in an orders table.
    I group it by a datepart Week so I get a total per week..

    all works well..
    however now I need to edit this so dependant upon the individual orderdate I divide the individual total by one of three numbers
    (I'm basically working with GROSS figures and need to re-work them back to NET, but here in the UK, the VAT rate will be changing soon as did again about 13 months ago..)

    here is my existing Statement:

    SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek
    
    FROM dbo.Orders_Retail
    
    WHERE (account = @Account) AND (YEAR(OrderDate) = @YeartoUse) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND 
    
    (OrderStatus <> 'Refunded')
    
    GROUP BY DATEPART(wk, OrderDate)
    



    That sum total I need to change so that if the ORDERDATE field is before Dec 1st 2008 then I divide the total by 17.5,  between DEc 1st 2008 and Dec 31st 2009  divide by 15, and then after Dec 31st 2009, back to divide by 17.5

    Any ideas how to do this?

    thanks!! for any replies

    Mtm81

    Monday, November 23, 2009 3:02 PM

Answers

  • Something like this:

    create table #orders
    (id int, val int, dt datetime)
    
    insert into #orders select 1, 1750, '1 sep 2008'
    insert into #orders select 2, 1750, '2 sep 2008'
    insert into #orders select 3, 1500, '1 nov 2009'
    insert into #orders select 4, 1500, '2 nov 2009'
    
    select sum(case when dt between '1 dec 2008' and '1 dec 2009' then val/15 else val/17.5 end), count(*), datepart(wk, dt)
    from #orders
    group by datepart(wk, dt)
    
    drop table #orders

    every day is a school day
    • Proposed as answer by Melissa Suciadi Tuesday, November 24, 2009 7:32 AM
    • Marked as answer by mtm81 Tuesday, November 24, 2009 9:51 AM
    Monday, November 23, 2009 3:36 PM

All replies

  • Something like this:

    create table #orders
    (id int, val int, dt datetime)
    
    insert into #orders select 1, 1750, '1 sep 2008'
    insert into #orders select 2, 1750, '2 sep 2008'
    insert into #orders select 3, 1500, '1 nov 2009'
    insert into #orders select 4, 1500, '2 nov 2009'
    
    select sum(case when dt between '1 dec 2008' and '1 dec 2009' then val/15 else val/17.5 end), count(*), datepart(wk, dt)
    from #orders
    group by datepart(wk, dt)
    
    drop table #orders

    every day is a school day
    • Proposed as answer by Melissa Suciadi Tuesday, November 24, 2009 7:32 AM
    • Marked as answer by mtm81 Tuesday, November 24, 2009 9:51 AM
    Monday, November 23, 2009 3:36 PM
  • excellent! - thank you for the very quick answer!!

    :-)
    Tuesday, November 24, 2009 9:52 AM