• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

0
rwheeler23
Asked:
rwheeler23
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You would do this as any other UPDATE that uses a JOIN; however, the JOIN in this case is to a derived table.

First, place your GROUP BY query into a derived / sub-query:
FROM (
SELECT taxdtlid
     , YEAR(docdate) AS FiscalYear
     , MONTH(docdate) AS PeriodID
     , SUM(taxamnt) AS TaxAmount
     , SUM(docamnt) AS SalesAmount
FROM [tx30000]
GROUP BY taxdtlid, YEAR(docdate), MONTH(docdate)
) tfr

Then JOIN it to your destination table:
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

Note: I moved filtering to the outer query, so you can change to different years and taxdtlid values more easily without having to repeat per table.

Total query looks like this:
UPDATE tto
SET tto.taxamnt = tfr.TaxAmount
  , tto.salamnt = tfr.SalesAmount
FROM (
SELECT taxdtlid
     , YEAR(docdate) AS FiscalYear
     , MONTH(docdate) AS PeriodID
     , SUM(taxamnt) AS TaxAmount
     , SUM(docamnt) AS SalesAmount
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


Hope that helps!
0
 
rwheeler23Author Commented:
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

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now