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
where taxdtlid='tx' and docdate>='01/01/2011'
group by month(docdate)
order by month(docdate)
where taxdtlid='tx' and year1=2011
order by docdate