Solved

# SQL Syntax Update Based on Sums

Posted on 2011-10-18
235 Views
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.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
``````
0
Question by:rwheeler23

LVL 59

Accepted Solution

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
;
``````

Hope that helps!
0

Author Comment

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
``````
0

## Featured Post

### Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…