Trying to find the difference between two sub-totals (sales - credits)
-
Hi Everyone,
I have the following code which essentially lists the total sales for an items on the first row, and the total credits for the same item on the second row.
SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales'
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode
UNION ALL
SELECT T1.ItemCode, SUM(T1.LineTotal) as 'Total Sales'
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode
The results of the query are shown below (with some alterations for confidentiality).
What I would like to do is write a code block that subtracts the total credits from the total sales, leaving me with only one row of data for the ItemCode.
If anybody can help with writing the code to achieve this it will be greatly appreciated.
Kind Regards,
Davo
-
Assiming that TotalCredits is the result of second part of query, I think this will work. (Not tested)
Select T0.ItemCode, T0.TotalSales - T1.TotalCreditsfrom
(select ItemCode, sum(LineTotal) as TotalSales
from Inv1
group by ItemCode
) T0
join
(select ItemCode, sum(LineTotal) as TotalCredits
from Inv1
group by ItemCode
) T1
on T1.ItemCode = T0.ItemCode
-
The biggest issue here is that tables by definition have no order. From you description you are counting on the rows being in a specific order. Do you have a column you can use to ensure the order?
This would be a lot easier if you could post ddl, sample data and desired output. Please take a few minutes and read the first link in my signature.
_______________________________________________________________
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/ -
Another option:
SELECT ItemCode, SUM([Total Sales]) as 'Total Sales'
FROM(
SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales'
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode
UNION ALL
SELECT T1.ItemCode, SUM(T1.LineTotal) * -1 as 'Total Sales'
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode)x
-
I interpret the question differently from earlier posters, maybe the following code is what you are looking for:
with TotalSales as (
SELECT T0.ItemCode, SUM(T0.LineTotal) as [Total Sales]
FROM INV1 T0
GROUP BY T0.ItemCode),
TotalCredit as (
SELECT T1.ItemCode, SUM(T1.LineTotal) as [Total Credit]
FROM RIN1 T1
GROUP BY T1.ItemCode)
SELECT A.ItemCode, TotalSales.[Total Sales], TotalCredit.[Total Credit],
TotalSales.[Total Sales] - TotalCredit.[Total Credit] as [Sales less Credit]
FROM TotalSales INNER JOIN TotalCredit
ON TotalSales.ItemCode = TotalCredit.ItemCode
but it may need the inner join replacing by a left, right, or full outer join depending on whether there can be sales without credit for the same item, or credit without sales for teh same item, or both - together with use of IsNull(TotalCredit.[Total Credit],0) or IsNull(TotalSales.[Total Sales],0) or both according to the join type.
Tom
-
Thanks everyone for your time, and wonderful responses, I have learned a great deal, and am now getting the results I desired.
-
david.dartnell (5/25/2014)
Thanks everyone for your time, and wonderful responses, I have learned a great deal, and am now getting the results I desired.Which one of the solutions was the one that is working for you?
_______________________________________________________________
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/ -
SELECT
ISNULL(sales.ItemCode, returns.ItemCode) AS ItemCode,
ISNULL(sales.total_sales, 0) - ISNULL(returns.total_returns, 0) AS NetSales
FROM (
SELECT T0.ItemCode, SUM(T0.LineTotal) as total_sales
FROM INV1 T0
WHERE T0.ItemCode = 'ACR2401010'
GROUP BY T0.ItemCode
) AS sales
FULL OUTER JOIN (
SELECT T1.ItemCode, SUM(T1.LineTotal) as total_returns
FROM RIN1 T1
WHERE T1.ItemCode = 'ACR2401010'
GROUP BY T1.ItemCode
) AS returns ON
returns.ItemCode = sales.ItemCode
ORDER BY
ItemCode
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I am using the solution provided by 'Roland C'. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply