quote:

---

Originally posted by MuMu88

What does your input look like?

---

SELECT  *
FROM    ( SELECT    CONVERT(VARCHAR(12), orderdate, 101) AS orderdate ,
                    COALESCE(CASE WHEN orders_extended.productprice < 0
                                  THEN 'DISCOUNT'
                                  ELSE orders_extended.productnumber
                             END,
                             CASE WHEN orders_extended.productnumber LIKE '%AB%'
                                  THEN 'PRODUCTGROUPAB'
                                  ELSE orders_extended.productnumber
                             END,
                             CASE WHEN orders_extended.productnumber LIKE '%CD%'
                                  THEN 'PRODUCTGROUPCD'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > 0
                                       AND orders.shippingtype NOT LIKE '10%'
                                  THEN 'SHIPPING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > '5.5'
                                       AND orders.shippingtype LIKE '10%'
                                  THEN 'SHIPPING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > '5.5'
                                       AND orders.shippingtype LIKE '10%'
                                  THEN 'HANDLING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.salestax > '0' THEN 'SalesTax'
                                  ELSE NULL
                             END) AS productgroup ,
                    COALESCE(orders_extended.productprice,
                             CASE WHEN orders.shippingcost > 0
                                       AND orders.shippingtype NOT LIKE '10%'
                                  THEN orders.shippingcost
                                  ELSE 0
                             END,
                             CASE WHEN orders.shippingcost > 5.50
                                       AND orders.shippingtype LIKE '10%'
                                  THEN orders.shippingcost - 5.50
                                  ELSE 0
                             END) AS totalsales
---i need this data to sum below but not as a column in the results
                    ,
                    CASE WHEN orders.paymentmethodid LIKE '4'
                              OR orders.paymentmethodid LIKE '5'
                              OR orders.paymentmethodid LIKE '6'
                         THEN 'Visa-MC-Disc'
                         WHEN orders.paymentmethodid LIKE '6' THEN 'Amex'
                         WHEN orders.paymentmethodid LIKE '7' THEN 'PayPal'
                         ELSE 'Unknown'
                    END AS PaymentMethod
          FROM      orders
                    LEFT JOIN orders_extended ON orders.orderid = orders_extended.orderid
          WHERE     1 = 1
                    AND orders.orderdate > '8/1/2013'
        ) s PIVOT( SUM(totalsales) FOR PaymentMethod IN ( [Visa-MC-Disc],
                                                          [Amex], [PayPal] ) ) P 

quote:

---

Originally posted by Lamprey

I don't have time to look into your actual question, but it appears you are going a lot of implicit conversions that you might want to avoid. For example, you comparring a string vlaue '5.5' to the shippingcost column, which I assume is money/decimal datatype.

---

quote:

---

Originally posted by Lamprey

So you store your shippingcost as a string?

---

quote:

---

Originally posted by James K

Try this. Since I don't have consumable data, I am simply guessing what you need. If this does not work, if you can post consumable data that someone can copy and paste to run in an SSMS window. This page describes how to create consumable data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT  *
FROM    ( SELECT    CONVERT(VARCHAR(12), orderdate, 101) AS orderdate ,
                    COALESCE(CASE WHEN orders_extended.productprice < 0
                                  THEN 'DISCOUNT'
                                  ELSE orders_extended.productnumber
                             END,
                             CASE WHEN orders_extended.productnumber LIKE '%AB%'
                                  THEN 'PRODUCTGROUPAB'
                                  ELSE orders_extended.productnumber
                             END,
                             CASE WHEN orders_extended.productnumber LIKE '%CD%'
                                  THEN 'PRODUCTGROUPCD'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > 0
                                       AND orders.shippingtype NOT LIKE '10%'
                                  THEN 'SHIPPING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > '5.5'
                                       AND orders.shippingtype LIKE '10%'
                                  THEN 'SHIPPING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.shippingcost > '5.5'
                                       AND orders.shippingtype LIKE '10%'
                                  THEN 'HANDLING'
                                  ELSE NULL
                             END,
                             CASE WHEN orders.salestax > '0' THEN 'SalesTax'
                                  ELSE NULL
                             END) AS productgroup ,
                    COALESCE(orders_extended.productprice,
                             CASE WHEN orders.shippingcost > 0
                                       AND orders.shippingtype NOT LIKE '10%'
                                  THEN orders.shippingcost
                                  ELSE 0
                             END,
                             CASE WHEN orders.shippingcost > 5.50
                                       AND orders.shippingtype LIKE '10%'
                                  THEN orders.shippingcost - 5.50
                                  ELSE 0
                             END) AS totalsales
---i need this data to sum below but not as a column in the results
                    ,
                    CASE WHEN orders.paymentmethodid LIKE '4'
                              OR orders.paymentmethodid LIKE '5'
                              OR orders.paymentmethodid LIKE '6'
                         THEN 'Visa-MC-Disc'
                         WHEN orders.paymentmethodid LIKE '6' THEN 'Amex'
                         WHEN orders.paymentmethodid LIKE '7' THEN 'PayPal'
                         ELSE 'Unknown'
                    END AS PaymentMethod
          FROM      orders
                    LEFT JOIN orders_extended ON orders.orderid = orders_extended.orderid
          WHERE     1 = 1
                    AND orders.orderdate > '8/1/2013'
        ) s PIVOT( SUM(totalsales) FOR PaymentMethod IN ( [Visa-MC-Disc],
                                                          [Amex], [PayPal] ) ) P 

---

quote:

---

Originally posted by katethegreat

quote:

---

Originally posted by Lamprey

So you store your shippingcost as a string?

---

Hi, it's CURRENCY - so does that mean I should be using '5.50' in my CASE instead of '5.5' ? I didn't think the trailing 0 made a difference.

Thanks,

Kate the Great

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by katethegreat

quote:

---

Originally posted by Lamprey

So you store your shippingcost as a string?

---

Hi, it's CURRENCY - so does that mean I should be using '5.50' in my CASE instead of '5.5' ? I didn't think the trailing 0 made a difference.

Thanks,

Kate the Great

---

Neither. Putting a value in a single quotes denotes a string. So what is happening is that the STRING value is being IMPLICITLY converted to a CURRENCY value since the currency datatype has a higher precedence that a string. If you want to denote a value as money/currency you should be using the dollar-sign character: $5.50

Perhaps, more importantly is that, in my opinion, you have a bad achitecture on your hands. Having to check to see if a dollar portion of a currency value is equal to 10 in order to tell what type of transaction it is seems like a bad idea. Rather, I'd suggest that you should have line items to represent charges and fees like sales tax, Shipping, handeling, etc.. Then those can be classified more easily in your database and, more importantly, constrainted.

---

quote:

---

Originally posted by Lamprey

Opps, I miss read something above about checking the shipping type and though it was the shipping cost. But, glad you got it sorted out. :)

---