Link to home
Create AccountLog in
Avatar of csicard
csicardFlag for United States of America

asked on

Need Query total customer sales (AR invoices - Credit Memos) into one row per customer within a date range

Query syntax is attached.
Result is similar to:
Customer A     100
Customer A      -50
Customer B      150

 The query is accurate but I would like the syntax to get the following result:
Customer A   50
Customer B  150

Thanks.
Query-totaling--AR-invoices-and-.doc
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Let's treat your query as a derived table:
SELECT Cardcode, Cardname, [Vendor dba Name], [sla license address], [sla city], [sla state], [sla zip], [vendor tel], [vendor mailing address], [vendor mailing city],
  [vendor mailing state], [vendor mailing zip], [sla number], [federal tax id], [NYS sals tax id],
SUM (Total) as Total

From (
SELECT  DISTINCT T0.cardcode,T0.[CardName],  T0.[CardFName] AS 'Vendor dba Name', T0.[Address] AS 'sla license address', T0.[City] AS 'sla city', T0.[State1] AS 'sla state', T0.[ZipCode] AS 'sla zip', T0.[Phone1] AS 'vendor tel', T0.[Address] AS 'vendor mailing address', T0.[City] AS 'vendor mailing city', T0.[State1] AS 'vendor mailing state', T0.[ZipCode] AS 'vendor mailing zip', T0.[U_BeerLic] AS 'sla number', T0.[Profession] AS 'federal tax id', T0.[LicTradNum] AS 'NYS sals tax id', sum(t2.LineTotal) AS 'Total'  FROM OCRD T0  INNER JOIN OINV T1 ON T0.cardcode = T1.cardcode INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry  

WHERE T0.[CardType] ='C' and T0.state1=  'NY' and t1.docdate >= N'[%1]'  and t1.docdate <= N'[%2]' 

GROUP BY T0.Cardcode,T0.CardName, T0.[CardFName] , T0.[Address] , T0.[City] , T0.[State1] , T0.[ZipCode], T0.[Phone1] , T0.[U_BeerLic] , T0.[Profession] , T0.[LicTradNum]

UNION ALL

SELECT DISTINCT T0.Cardcode,T0.[CardName],   T0.[CardFName] AS 'Vendor dba Name', T0.[Address] AS 'sla license address', T0.[City] AS 'sla city', T0.[State1] AS 'sla state', T0.[ZipCode] AS 'sla zip', T0.[Phone1] AS 'vendor tel', T0.[Address] AS 'vendor mailing address', T0.[City] AS 'vendor mailing city', T0.[State1] AS 'vendor mailing state', T0.[ZipCode] AS 'vendor mailing zip', T0.[U_BeerLic] AS 'sla number', T0.[Profession] AS 'federal tax id', T0.[LicTradNum] AS 'NYS sals tax id', sum(-t2.LineTotal) AS 'Total'  FROM OCRD T0  INNER JOIN ORIN T1 ON T0.cardcode = T1.cardcode INNER JOIN RIN1 T2 ON T1.DocEntry = T2.DocEntry  

WHERE T0.[CardType] ='C' and T0.state1=  'NY' and t1.docdate >= N'[%1]'  and t1.docdate <= N'[%2]' 


GROUP BY T0.Cardcode,T0.[CardName],   T0.[CardFName] , T0.[Address] , T0.[City] , T0.[State1] , T0.[ZipCode], T0.[Phone1] , T0.[U_BeerLic] , T0.[Profession] , T0.[LicTradNum]


ORDER BY  T0.Cardcode,T0.[CardName],  T0.[CardFName] , T0.[Address] , T0.[City] , T0.[State1] , T0.[ZipCode], T0.[Phone1] , T0.[U_BeerLic] , T0.[Profession] , T0.[LicTradNum]
) Q
Group By Cardcode, Cardname, [Vendor dba Name], [sla license address], [sla city], [sla state], [sla zip], [vendor tel], [vendor mailing address], [vendor mailing city],
  [vendor mailing state], [vendor mailing zip], [sla number], [federal tax id], [NYS sals tax id]

Open in new window

Avatar of awking00
Select other_columns, sum(sales) as total_sales
from (yourquery)
group by other_columns
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of csicard

ASKER

Thank you