CODEWITHSUNDEEP
sql
deccypher
deccypher

Reputation: 15

Transact SQL 2 sum 1 query using case

HI I`m probably over complicating my code so looking for a better method.

i have an invoice table and I'm comparing 2 date ranges I'm looking for a simple sql statement that will look at the date is in range 1 add to ordertotal1 else if in date range 2 add to ordertotal2

this is my attempt

    SELECT     CUST_CODE,

    CASE
        WHEN  ORDER_DATE >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102) AND ORDER_DATE <= CONVERT(DATETIME, '2011-07-31 23:59:59', 102) THEN  SUM(INV_AMOUNT) AS OrderTotal, SUM(PAID_AMT) AS paidTotal
WHEN  ORDER_DATE >= CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND ORDER_DATE <= CONVERT(DATETIME, '2012-07-31 23:59:59', 102)  THEN SUM(INV_AMOUNT) AS OrderTotal2, SUM(PAID_AMT) AS paidTotal2
    END
    FROM         INVOICES
    WHERE (STATUS = 9)
    GROUP BY CUST_CODE

of course this is really wrong and throws up a lot of errors, i think I'm doomed to running several queries in my php to achieve it, but I`m sure I remember doing something like the above in the past.

FYI: running php 5 Mssql 2000 (not by choice) on apache 2.2 for windows.

Thanks for any help offered

Upvotes: 1

Views: 433

Answers (3)

Taryn
Taryn

Reputation: 247840

It appears that your CASE statement are missing the END:

SELECT CUST_CODE,
    CASE
        WHEN  ORDER_DATE >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102) 
            AND ORDER_DATE <= CONVERT(DATETIME, '2011-07-31 23:59:59', 102) 
        THEN  SUM(INV_AMOUNT) END AS OrderTotal, 
        SUM(PAID_AMT) AS paidTotal,
    CASE
        WHEN  ORDER_DATE >= CONVERT(DATETIME, '2012-01-01 00:00:00', 102) 
            AND ORDER_DATE <= CONVERT(DATETIME, '2012-07-31 23:59:59', 102)  
        THEN SUM(INV_AMOUNT) END AS OrderTotal2 
    ---SUM(PAID_AMT) AS paidTotal2 not needed
FROM         INVOICES
WHERE (STATUS = 9)
GROUP BY CUST_CODE

Or better yet:

SELECT CUST_CODE,
    SUM(
        CASE
        WHEN  ORDER_DATE >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102) 
            AND ORDER_DATE <= CONVERT(DATETIME, '2011-07-31 23:59:59', 102) 
        THEN  INV_AMOUNT END) AS OrderTotal, 
        SUM(PAID_AMT) AS paidTotal,
    SUM(
        CASE
        WHEN  ORDER_DATE >= CONVERT(DATETIME, '2012-01-01 00:00:00', 102) 
            AND ORDER_DATE <= CONVERT(DATETIME, '2012-07-31 23:59:59', 102)  
        THEN INV_AMOUNT END ) AS OrderTotal2 
    ---SUM(PAID_AMT) AS paidTotal2 not needed
FROM         INVOICES
WHERE (STATUS = 9)
GROUP BY CUST_CODE

Upvotes: 1

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

You cant use two column in your CASE WHEN ... THEN THEN SUM(INV_AMOUNT) AS OrderTotal, SUM(PAID_AMT) AS paidTotal

IF I understand your question well, you need a solution which is splitting the INV_AMOUNT into two value OrderTotal and OrderTotal2. If you want to know how much was the value in 2011 and in 2012 separetly, you need something like this:

 SELECT CUST_CODE,
        SUM(CASE
            WHEN  ORDER_DATE >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102) 
                AND ORDER_DATE <= CONVERT(DATETIME, '2011-07-31 23:59:59', 102) 
            THEN  INV_AMOUNT ELSE 0 END) AS OrderTotal, 
            SUM(PAID_AMT) AS paidTotal,
        SUM(CASE
            WHEN  ORDER_DATE >= CONVERT(DATETIME, '2012-01-01 00:00:00', 102) 
                AND ORDER_DATE <= CONVERT(DATETIME, '2012-07-31 23:59:59', 102)  
            THEN INV_AMOUNT ELSE 0 END) AS OrderTotal2, 
        SUM(PAID_AMT) AS paidTotal2
    FROM         INVOICES
    WHERE (STATUS = 9)
    GROUP BY CUST_CODE

Upvotes: 0

Farhan
Farhan

Reputation: 2575

What exactly is the datatype of ORDER_DATE? Why aren't you using CAST:

cast('2011-01-01 00:00:00' as DATETIME)

Anyways, use this as your two CASE clauses:

case
    when ORDER_DATE between cast('2011-01-01 00:00:00' as datetime) and cast('2011-07-31 23:59:59' as datetime)
    then...
end

For the rest, you can use bluefeet's query, as it has been simplified.

Upvotes: 0

Related Questions