February 24, 2014 at 9:15 am
Disclaimer: I don't claim that the SQL below is the "best" way to achieve what I needed but it did work until... 🙂
Objective:
Compare sales item values from set period this year with same period last year.
Example below January 2013 and January 2014
Everything (seemed) to work fine UNTIL it was decided the report must include Credits.
(On our invoiceitems table credits do NOT show as a minus - hence the case statement)
The point I have reached - below - works to a point except the (report) user wants a single row combining
sales and credit where they both exist.
Example:
Customer ABC
Part number XYX
10 sold @ £1.00
5 returned for credit
My report displays two separate rows.
Question: Is it possible to report combined sales and credits on a single line?
SQL is:-
;with
LastYear as (
select
customers.id,
customers.name,
customeraddresses.countryid,
invoiceitems.partid,
invoiceitems.invoicetype,
sum(CASE WHEN invoiceitems.invoicetype = 'SCRN' THEN ABS(invoiceitems.homenettvalue) *-1
ELSE invoiceitems.homenettvalue END) as SumPrice,
sum(invoiceitems.quantity) as SumQty,
sum(invoiceitems.homenettvalue) / sum(invoiceitems.quantity) as SumValue,
invoices.currencyid
from customers
LEFT OUTER JOIN invoices ON
customers.id = invoices.traderid
LEFT OUTER JOIN invoiceitems ON
invoices.id = invoiceitems.invoiceid
LEFT OUTER JOIN customeraddresses ON
customers.id = customeraddresses.customer
WHERE invoices.tradertype = 'C'
AND invoiceitems.partid NOT LIKE 'C%'
AND invoices.invoicetype IN ('SINV', 'SCRN')
AND invoiceitems.nominalaccountid LIKE '5%'
AND customeraddresses.isinvaddress = '1'
AND invoices.taxdate >=?
AND invoices.taxdate <=?
Group By
customers.id, customers.name, invoiceitems.partid, invoices.currencyid, invoiceitems.invoiceid, customeraddresses.countryid, invoiceitems.invoicetype
)
, ThisYear as (
select
customers.id,
customers.name,
customeraddresses.countryid,
invoiceitems.partid,
invoiceitems.invoiceid,
invoiceitems.invoicetype,
sum(CASE WHEN invoiceitems.invoicetype = 'SCRN' THEN ABS(invoiceitems.homenettvalue) *-1
ELSE invoiceitems.homenettvalue END) as SumPrice,
sum(invoiceitems.quantity) as SumQty,
sum(invoiceitems.homenettvalue) / sum(invoiceitems.quantity) as SumValue,
invoices.currencyid
from customers
LEFT OUTER JOIN invoices ON
customers.id = invoices.traderid
LEFT OUTER JOIN invoiceitems ON
invoices.id = invoiceitems.invoiceid
LEFT OUTER JOIN customeraddresses ON
customers.id = customeraddresses.customer
WHERE invoices.tradertype = 'C'
AND invoiceitems.partid NOT LIKE 'C%'
AND invoices.invoicetype IN ('SINV', 'SCRN')
AND invoiceitems.nominalaccountid LIKE '5%'
AND customeraddresses.isinvaddress = '1'
AND invoices.taxdate >=?
AND invoices.taxdate <=?
Group By
customers.id,customers.name, invoiceitems.partid, invoices.currencyid, invoiceitems.invoiceid, customeraddresses.countryid, invoiceitems.invoicetype
)
select
coalesce(ly.id, ty.id) as CustomerID,
coalesce(ly.name, ty.name) as CustomerName,
coalesce(ly.countryid, ty.countryid) as Country,
coalesce(ly.invoicetype, ty.invoicetype) as Type,
coalesce(ly.partid, ty.partid) as PartNumber,
coalesce(ly.currencyid, ty.currencyid) as Currency,
coalesce (ly.SumPrice, 0) as PYTD_Ext_Price,
coalesce (ty.SumPrice, 0) as CYTD_Ext_Price,
coalesce (ly.SumQty, 0) as PYTD_Qty,
coalesce (ty.SumQty, 0) as CYTD_Qty,
coalesce (ly.SumValue, 0) as ASP_PYTD,
coalesce (ty.SumValue, 0) as ASP_CYTD
from lastYear ly
full join thisYear ty
on ly.id = ty.id and ly.partid = ty.partid
order by 1,2,3
February 24, 2014 at 9:21 am
I would say to use a SUM with a case expression inside. It seems you have done that. We can't offer a lot more insight here because we have no idea what your table looks like.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 9:39 am
Instead of that:
invoiceitems.invoicetype,
sum(CASE WHEN invoiceitems.invoicetype = 'SCRN' THEN ABS(invoiceitems.homenettvalue) *-1
ELSE invoiceitems.homenettvalue END) as SumPrice,
sum(CASE WHEN invoiceitems.invoicetype = 'SCRN' THEN ABS(invoiceitems.homenettvalue) *-1
ELSE 0.00 END) as SumCredit,
sum(CASE WHEN invoiceitems.invoicetype = 'SCRN' THEN 0.00
ELSE invoiceitems.homenettvalue END) as SumPrice,
February 24, 2014 at 9:41 am
Example of output that's "wrong." (Sorry about the formatting - I can never seem to get it to display correctly.)
CustomerIDCustomerNameCountryPartNumberCurrencyPYTD_Ext_PriceCYTD_Ext_PricePYTD_QtyCYTD_QtyASP_PYTDASP_CYTD
N1141ARC Machine Tools LtdGBS2G00009D070GBP0.00-235.44010235.44
N1141ARC Machine Tools LtdGBS2G00009D070GBP0.00261.60010261.6
Issue 1. -235.44 and 261.60 are on separate rows
and I'm after a single row - customer - part number = 26.16
Issue 2. -235.44 (Ext_Price) displays as 235.44 (ASP_CYTD) (If I can resolve issue 1 I'd like this to display as a positive or negative as appropriate.)
February 24, 2014 at 10:23 am
malcolm.garbett (2/24/2014)
Example of output that's "wrong." (Sorry about the formatting - I can never seem to get it to display correctly.)CustomerIDCustomerNameCountryPartNumberCurrencyPYTD_Ext_PriceCYTD_Ext_PricePYTD_QtyCYTD_QtyASP_PYTDASP_CYTD
N1141ARC Machine Tools LtdGBS2G00009D070GBP0.00-235.44010235.44
N1141ARC Machine Tools LtdGBS2G00009D070GBP0.00261.60010261.6
Issue 1. -235.44 and 261.60 are on separate rows
and I'm after a single row - customer - part number = 26.16
Issue 2. -235.44 (Ext_Price) displays as 235.44 (ASP_CYTD) (If I can resolve issue 1 I'd like this to display as a positive or negative as appropriate.)
Without some ddl and tables this is shooting in the dark. You need to aggregate your rows using a sum. I can't get any more specific than that because I have nothing to work with. From the two rows you listed above a SUM on PYTD_Ext_Price I think is what you are looking for.
Formatting in here is a real PITA to say the least. It is generally considered easier to just create a query or table to hold your data. It seems a little overkill I know but it ensures that you don't have formatting issues. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply