SQL query to subtract two columns

• Julia George

  SSC-Addicted

  Points: 400

  More actions

  November 12, 2008 at 8:05 am

  #202792

  I am trying to create a SQL Script to pull data from three tables

  Accounts (list of Customers),Sales (Invoice Tables),Sales_Payments (table with holds payments made against invoices) to display Customer AccountNumber,CustomerName, InvoiceBalance (for 0 - 14 days),InvoiceBalance (for 14-30 days).

  I dont think I am getting the subtractio part correctly for each accountID .... any thoughts to correct this query..?

  SELECT Accounts.AccountNumber,Accounts.CustomerName,

  (SELECT SUM(ISNULL(Sales.subtotal,0) + ISNULL(Sales.vat,0))

  - (SELECT SUM(ISNULL(Sales_Payments.Amount,0)) from Sales_Payments)

  FROM Sales INNER JOIN Sales_Payments ON Sales.SaleID = Sales_Payments.SaleID

  WHERE datediff(dd,Sales.dateandtime,getdate()) <= 14 AND Sales.Type = 'Invoice' AND

  Sales.AccountID = Accounts.AccountID) AS [InvoiceBalance(0-14 Days)]

  ,

  (SELECT SUM(ISNULL(Sales.subtotal,0) + ISNULL(Sales.vat,0))

  - (SELECT SUM(ISNULL(Sales_Payments.Amount,0)) from Sales_Payments)

  FROM Sales INNER JOIN Sales_Payments ON Sales.SaleID = Sales_Payments.SaleID

  WHERE datediff(dd,Sales.dateandtime,getdate()) >= 15 AND

  datediff(dd,Sales.dateandtime,getdate()) <= 31 AND

  Sales.Type = 'Invoice' AND Sales.AccountID = Accounts.AccountID) AS [InvoiceBalance(15-31 Days)]

  from Accounts INNER JOIN Sales ON Accounts.AccountID = Sales.AccountId

  GROUP BY Accounts.AccountNumber,Accounts.CustomerName, Accounts.AccountID

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  November 12, 2008 at 9:01 am

  #897591

  Maybe something like:

  SELECT A.AccountNumber

  &nbsp&nbsp&nbsp&nbsp,A.CustomerName

  &nbsp&nbsp&nbsp&nbsp,D.subtotal14 + D.vat14 - D.Payment14 AS [InvoiceBalance(0-14 Days)]

  &nbsp&nbsp&nbsp&nbsp,D.subtotal31 + D.vat31 - D.Payment31 AS [InvoiceBalance(15-31 Days)]

  FROM Accounts A

  &nbsp&nbsp&nbsp&nbspJOIN

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT S.AccountID

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime >= DT.DT14 THEN S.subtotal END), 0) AS subtotal14

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime < DT.DT14 THEN S.subtotal END), 0) AS subtotal31

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime >= DT.DT14 THEN S.vat END), 0) AS vat14

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime < DT.DT14 THEN S.vat END), 0) AS vat31

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime >= DT.DT14 THEN P.Amount END), 0) AS Payment14

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,ISNULL(SUM(CASE WHEN S.dateandtime < DT.DT14 THEN P.Amount END), 0) AS Payment31

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM Sales S

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspCROSS JOIN

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT DATEADD(d, -14, DATEDIFF(d, 0, GETDATE())) AS DT14

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp) DT

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspLEFT JOIN Sales_Payments P

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON S.SaleID = P.SaleID

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHERE S.[Type] = 'Invoice'

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND S.dateandtime >= DATEADD(d, -31, DATEDIFF(d, 0, GETDATE()))

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspGROUP BY S.AccountID

  &nbsp&nbsp&nbsp&nbsp) D

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON A.AccountID = D.AccountID