asked on

SQL Syntax Update Based on Sums

I have two sales tax tables:
1) TX30000 contains all the transaction detail.
2) TX00203 is a period summary table where the index is taxdtlid and period. The period is the month

What is the proper syntax to update the TX00203 table based on taxdtlid and period given the summing of the transactions in the TX30000 table? The fields in the TX00203 table are:
TX00203.PERIODID - Link 1 field
TX00203.TAXDTLID - Link 2 field
TX00203.TAXAMNT - Update the total tax for this period
TX00203.SALAMNT - Update the total sales amount for this period

This query performs the sums
select month(docdate) as PeriodID,sum(taxamnt) as TaxAmount,sum(docamnt) as SalesAmount
from [tx30000]
where taxdtlid='tx' and docdate>='01/01/2011'
group by month(docdate)
order by month(docdate)

============================
select *
from [tx00203]
where taxdtlid='tx' and year1=2011
order by docdate

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

You sir are a genius. Thank you for for help. I had forgotten a field so here is the final version. I will award points by the end of today.

UPDATE tto
SET tto.tdtslpch = tfr.SalesAmount,
	tto.txdttxsp = tfr.TaxableAmount,
	tto.txdtsptx = tfr.TaxAmount
FROM (
SELECT taxdtlid,
	YEAR(docdate) AS FiscalYear,
    MONTH(docdate) AS PeriodID,
	SUM(docamnt) AS SalesAmount,
    SUM(taxable_amount) AS TaxableAmount,
	SUM(taxamnt) AS TaxAmount
FROM [tx30000]
GROUP BY taxdtlid, YEAR(docdate), MONTH(docdate)
) tfr
JOIN [tx00203] tto 
   ON tto.taxdtlid = tfr.taxdtlid
  AND tto.year1 = tfr.FiscalYear
  AND tto.periodid = tfr.PeriodID
WHERE tto.taxdtlid='tx' AND tto.year1=2011

Open in new window