September 6, 2016 at 12:22 am
Given the following setup:
Base Data
if object_ID('tempdb..#baseTable','U') is not null drop table #baseTable;
create table #baseTable
(
RIN int,
Year int,
Parcel varchar(13),
TransDateTime DateTime,
ReceiptNumber int,
TransType char,
Payment decimal(13,2)
);
GO
INSERT #baseTable (RIN, Year, Parcel, TransDateTime, ReceiptNumber, TransType, Payment)
SELECT 18872,2004,'00-0003-7015','2005-03-09 00:00:00.000',198,'I',12.66 UNION ALL
SELECT 18873,2004,'00-0003-7015','2005-03-09 00:00:00.000',198,'P',11.01 UNION ALL
SELECT 18874,2004,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',173.21 UNION ALL
SELECT 18875,2004,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',191.79 UNION ALL
SELECT 18876,2004,'00-0003-7015','2005-03-09 00:00:00.000',198,'R',185.38 UNION ALL
SELECT 13798,2003,'00-0003-7015','2005-03-09 00:00:00.000',198,'I',57.34 UNION ALL
SELECT 13799,2003,'00-0003-7015','2005-03-09 00:00:00.000',198,'P',13.51 UNION ALL
SELECT 13800,2003,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',425.52 UNION ALL
SELECT 13801,2003,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',76.28 UNION ALL
SELECT 13802,2003,'00-0003-7015','2005-03-09 00:00:00.000',198,'R',173.70 UNION ALL
SELECT 9220,2002, '00-0003-7015','2005-03-09 00:00:00.000',198,'I',80.23 UNION ALL
SELECT 9221,2002,'00-0003-7015','2005-03-09 00:00:00.000',198,'P',10.65 UNION ALL
SELECT 9222,2002,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',425.52 UNION ALL
SELECT 9223,2002,'00-0003-7015','2005-03-09 00:00:00.000',198,'R',106.86 UNION ALL
SELECT 5067,2001,'00-0003-7015','2005-03-09 00:00:00.000',198,'I',66.14 UNION ALL
SELECT 5068,2001,'00-0003-7015','2005-03-09 00:00:00.000',198,'P',10.00 UNION ALL
SELECT 5069,2001,'00-0003-7015','2005-03-09 00:00:00.000',198,'S',173.21 UNION ALL
SELECT 5070,2001,'00-0003-7015','2005-03-09 00:00:00.000',198,'R',102.87;
GO
Destination Table
if object_ID('tempdb..#updateTable','U') is not null drop table #updateTable;
create table #updateTable
(
Year int,
Parcel varchar(13),
Principal decimal(13,2),
Penalty decimal(13,2),
Interest decimal(13,2),
UNIQUE(Year, Parcel)
);
GO
INSERT #updateTable (Year, Parcel, Principal, Penalty, Interest)
SELECT 2001, '00-0003-7015', 10,1,0 UNION ALL
SELECT 2002, '00-0003-7015', 20,2,0 UNION ALL
SELECT 2003, '00-0003-7015', 30,3,0 UNION ALL
SELECT 2004, '00-0003-7015', 40,4,0;
I am trying to reverse transactions that are in the Base Table by summing them up and grouping them by Year, Parcel and TransType. Then taking that result I should be able to update the #updateTable. This is what I have tried, but it is not updating all the columns!
select * from #updateTable;
begin tran
GO
with cte as
(
SELECT C2.Year, C2.Parcel, C2.TransType
, SUM(CASE WHEN C2.TransType = 'R' THEN C2.Payment else 0 END) As SumPrincipal
, SUM(CASE WHEN C2.TransType = 'P' THEN C2.Payment else 0 END) As SumPenalty
, SUM(CASE WHEN C2.TransType = 'I' THEN C2.Payment else 0 END) As SumInterest
FROM #baseTable C1
INNER JOIN #baseTable C2
on C1.Parcel = C2.Parcel
and C1.ReceiptNumber = C2.ReceiptNumber
and cast(C1.TransDateTime as DATE) = cast(C2.TransDateTime as DATE)
and C2.TransTYpe <> 'S'
WHERE C1.RIN IN (18875)
GROUP BY C2.Year, C2.Parcel, C2.TransType
)
Update ut SET
ut.principal += case when c.Transtype = 'R' then c.SumPrincipal else 0 end,
ut.Penalty += case when c.TransType = 'P' then c.SumPenalty else 0 end,
ut.Interest += case when c.TransType = 'I' then c.SumInterest else 0 end
from #updateTable ut
inner join cte c
on c.year = ut.year
and c.parcel = ut.parcel;
select * from #updateTable;
rollback tran
GO
The cte correctly sums and orders the data, but the update fails miserably. The other thought I had was to take the results of the cte and pivot them on the year and parcel. All the examples I looked at and tried did not come close to what I need. Will a pivot table be needed or can my original query be fixed?
Thanks.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2016 at 4:45 am
This seems to work, if you group the updates by Year & Parcel.
select * from #updateTable;
begin tran
GO
with cte as
(
SELECT C2.Year, C2.Parcel
, SUM(CASE WHEN C2.TransType = 'R' THEN C2.Payment else 0 END) As SumPrincipal
, SUM(CASE WHEN C2.TransType = 'P' THEN C2.Payment else 0 END) As SumPenalty
, SUM(CASE WHEN C2.TransType = 'I' THEN C2.Payment else 0 END) As SumInterest
FROM #baseTable C1
INNER JOIN #baseTable C2
on C1.Parcel = C2.Parcel
and C1.ReceiptNumber = C2.ReceiptNumber
and cast(C1.TransDateTime as DATE) = cast(C2.TransDateTime as DATE)
and C2.TransTYpe <> 'S'
WHERE C1.RIN IN (18875)
GROUP BY C2.Year, C2.Parcel
)
Update ut SET
ut.principal += c.SumPrincipal
, ut.Penalty += c.SumPenalty
, ut.Interest += c.SumInterest
from #updateTable ut
inner join cte c
on c.year = ut.year
and c.parcel = ut.parcel;
select * from #updateTable;
rollback tran
GO
September 6, 2016 at 11:48 am
Sometimes the simplest things trip you up.
Thanks for the extra set of eyes. With a bit of tweaking, it worked perfectly.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply