January 6, 2005 at 6:11 pm
I have a stored procedure that updates a table with COUNT and SUM functions. I realize I can just add the Yes and No column to get the total, I'm just trying to understand how to join these into a single statement, when using an UPDATE statement.
The table has 7 fields that give this information.
Account Number
Count if Yes
Count if No
Total Count
Sum of Yes transactions
Sum of No transactions
Sum of All transactions
Here is my butchery:
UPDATE _ReportStats
SET EntryCntManual = B._EntryCntManual,
EntryDolManual = B._EntryDolManual
FROM _ReportStats A
INNER JOIN (
SELECT
AcctNo, ManualEntry, Count(ManualEntry) AS _EntryCntManual,
Sum(TransAmt) AS _EntryDolManual
FROM _FinalTrans B
WHERE B.ManualEntry = 'Y'
GROUP BY AcctNo, ManualEntry) B
ON A.AcctNo = B.AcctNo
UPDATE _ReportStats
SET EntryCntSwipe = B.EntryCntSwipe,
EntryDolSwipe = B._EntryDolSwipe
FROM _ReportStats A
INNER JOIN (
SELECT
AcctNo, ManualEntry, Count(ManualEntry) AS EntryCntSwipe,
Sum(TransAmt) AS _EntryDolSwipe
FROM _FinalTrans B
WHERE B.ManualEntry = 'N'
GROUP BY AcctNo, ManualEntry) B
ON A.AcctNo = B.AcctNo
UPDATE _ReportStats
SET EntryCntPerc = B._EntryCntPerc,
EntryDolPerc = _EntryDolPerc
FROM _ReportStats A
INNER JOIN (
SELECT
AcctNo, Count(AcctNo) AS _EntryCntPerc,
Sum(TransAmt) AS _EntryDolPerc
FROM _FinalTrans B
GROUP BY AcctNo) B
ON A.AcctNo = B.AcctNo
January 7, 2005 at 8:29 am
Do you mean like this
UPDATE A
SET A.EntryCntManual = B.EntryCntManual,
A.EntryDolManual = B.EntryDolManual,
A.EntryCntSwipe = B.EntryCntSwipe,
A.EntryDolSwipe = B.EntryDolSwipe,
A.EntryCntPerc = B.EntryCntPerc,
A.EntryDolPerc = B.EntryDolPerc
FROM ReportStats A
INNER JOIN (
SELECT AcctNo,
SUM(CASE WHEN ManualEntry = 'Y' THEN 1 ELSE 0 END) AS [EntryCntManual],
SUM(CASE WHEN ManualEntry = 'Y' THEN TransAmt ELSE 0 END) AS [EntryDolManual],
SUM(CASE WHEN ManualEntry = 'N' THEN 1 ELSE 0 END) AS [EntryCntSwipe],
SUM(CASE WHEN ManualEntry = 'N' THEN TransAmt ELSE 0 END) AS [EntryDolSwipe],
Count(AcctNo) AS [EntryCntPerc],
Sum(TransAmt) AS [EntryDolPerc]
FROM FinalTrans B
GROUP BY AcctNo
) B
ON B.AcctNo = A.AcctNo
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply