StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL CASE Product Price
text
Body
copied!<p>I have the following query Which works great, but I also want to determine the revenue for each product (Name) which has a different price based on the distribution centre:</p> <pre><code>SELECT NAME, ISNULL([UK - BOLTON], 0) [UK Website], ISNULL([USA - FLORIDA], 0) [USA Website], ISNULL([CANADA], 0) [Canada Website], ISNULL([AUSTRALIA], 0) [Australia Website], ISNULL([AMAZON (USA)], 0) [Amazon (USA)], ISNULL([AMAZON (UK)], 0) [Amazon (UK)], ISNULL([AMAZON (FR)], 0) [Amazon (FR)], ISNULL([AMAZON (CA)], 0) [Amazon (CA)], ISNULL([AMAZON (JP)], 0) [Amazon (JP)] FROM (SELECT p.NAME, ps.UNITSSOLD, CASE WHEN dc.[DESCRIPTION] = 'UK Website' THEN p.UKWEBPRICE END Revenue, dc.[DESCRIPTION] FROM DBO.PRODUCTSALES ps INNER JOIN DBO.PRODUCT p ON ps.PRODUCTID = p.PRODUCTID INNER JOIN DBO.DISTRIBUTIONCENTRE dc ON ps.DISTRIBUTIONCENTREID = dc.DISTRIBUTIONCENTREID WHERE ps.ORDERDATE >= CONVERT(DATETIME, '2013-08-01 00:00:00', 102) AND ps.ORDERDATE <= CONVERT(DATETIME, '2013-08-20 00:00:00', 102) AND p.PRODUCTCLASSIFICATIONID = 2) [SourceTable] PIVOT ( SUM(UNITSSOLD) FOR [DESCRIPTION] IN ([UK - Bolton], [USA - Florida], [Canada], [Australia], [Amazon (USA)], [Amazon (UK)], [Amazon (FR)], [Amazon (CA)], [Amazon (JP)]) ) AS PIVOTTABLE </code></pre> <p>As you can see I have tried using a CASE/WHEN but I think I'm getting confused on how to do it. Basically UK - Bolton should pick up the UKWebPRice from the Product table (p) etc for each distribution centre, and It's revenue, so it'd be UnitsSold * UKWebPrice etc for each center.</p> <p>Hope this makes sense.</p> <p>Thanks,</p> <p>Mike</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload