October 31, 2015 at 2:35 pm
I'm having a hard time figuring out the right way to ask the question, so I'm sorry if the subject isn't very clear.
The scenario:
* Transactions are stored in 3 separate tables: Charges, Payments, Adjusts.
* Each table contains an entry date field "EntryDte" and an "Amount" field.
* Individual queries are run against each table to get total transactions by entry date from that table
* The individual queries all have the "EntryDte" field, but the same dates may not be present in all tables
Desired result is a single table with fields Entry Date, Charges, Payments, and Adjusts like so:
EntryDte Charges Payments Adjusts
10/01/2015 4,617.00 0.00 0.00
10/02/2015 71,820.00 0.00 -636.00
10/03/2015 60,142.00 0.00 -2,560.00
10/05/2015 5,152.00 0.00 0.00
10/08/2015 20,907.00 0.00 0.00
10/09/2015 8,766.00 0.00 0.00
10/14/2015 33,039.00 0.00 0.00
10/15/2015 19,704.00 0.00 -1,288.00
10/16/2015 36,598.00 0.00 0.00
10/20/2015 57,577.00 0.00 -1,288.00
10/21/2015 60,455.00 0.00 -2,511.00
10/22/2015 25,349.00 0.00 0.00
10/23/2015 19,835.00 0.00 0.00
10/26/2015 73,346.00 0.00 0.00
10/29/2015 72,143.00 -620.19 -4,448.38
10/30/2015 44,720.00 -6,902.16 -12,371.13
10/31/2015 8,532.00 0.00 0.00
I've gotten what I need 2 different ways but both solutions seem clumsy. It seems like there should to be either a better or more efficient method to do this.
I appreciate any ideas you might offer.
Thank you
DML for the data (sorry for the UNION's, I'm stuck with 2005):
if object_id('tempdb..#tmpChgs') is not null drop table #tmpChgs;
if object_id('tempdb..#tmpPmts') is not null drop table #tmpPmts;
if object_id('tempdb..#tmpAdj') is not null drop table #tmpAdj;
go
create table #tmpChgs (EntryDte datetime not null, Charges decimal(9,2))
insert into #tmpChgs
SELECT '20151001',4617.00 UNION ALL
SELECT '20151002',71820.00 UNION ALL
SELECT '20151003',60142.00 UNION ALL
SELECT '20151005',5152.00 UNION ALL
SELECT '20151008',20907.00 UNION ALL
SELECT '20151009',8766.00 UNION ALL
SELECT '20151014',33039.00 UNION ALL
SELECT '20151015',19704.00 UNION ALL
SELECT '20151016',36598.00 UNION ALL
SELECT '20151020',57577.00 UNION ALL
SELECT '20151021',60455.00 UNION ALL
SELECT '20151022',25349.00 UNION ALL
SELECT '20151023',19835.00 UNION ALL
SELECT '20151026',73346.00 UNION ALL
SELECT '20151029',72143.00 UNION ALL
SELECT '20151030',44720.00 UNION ALL
SELECT '20151031',8532.00
create table #tmpPmts (EntryDte datetime not null, Payments decimal(9,2))
insert into #tmpPmts
SELECT '20151029',-620.19 UNION ALL
SELECT '20151030',-6902.16
create table #tmpAdj (EntryDte datetime not null, Adjusts decimal(9,2))
insert into #tmpAdj
SELECT '20151002',-636.00 UNION ALL
SELECT '20151003',-2560.00 UNION ALL
SELECT '20151015',-1288.00 UNION ALL
SELECT '20151020',-1288.00 UNION ALL
SELECT '20151021',-2511.00 UNION ALL
SELECT '20151029',-4448.38 UNION ALL
SELECT '20151030',-12371.13
Version 1, CTE's:
WITH
c as (SELECT EntryDte, Charges, 0. as Payments, 0. as Adjusts FROM #tmpChgs)
,p as (SELECT EntryDte, 0. as Charges, Payments, 0. as Adjusts FROM #tmpPmts)
,a as (SELECT EntryDte, 0. as Charges, 0. as Payments, Adjusts FROM #tmpAdj)
SELECT
EntryDte
,sum(Charges) as Charges
,sum(Payments) as Payments
,sum(Adjusts) as Adjusts
FROM
(
SELECT c.* FROM c
UNION ALL
SELECT p.* FROM p
UNION ALL
SELECT a.* FROM a
) q
GROUP BY
EntryDte
Version 2, same thing without the CTE:
SELECT
EntryDte
,sum(Charges) as Charges
,sum(Payments) as Payments
,sum(Adjusts) as Adjusts
FROM
(
SELECT EntryDte, Charges, 0. as Payments, 0. as Adjusts FROM #tmpChgs
UNION ALL
SELECT EntryDte, 0. as Charges, Payments, 0. as Adjusts FROM #tmpPmts
UNION ALL
SELECT EntryDte, 0. as Charges, 0. as Payments, Adjusts FROM #tmpAdj
) q
GROUP BY
EntryDte
November 2, 2015 at 4:13 am
Hi
A bit different version:
WITH dates AS (SELECT EntryDte FROM #tmpChgs UNION SELECT
EntryDte FROM #tmpPmts UNION SELECT
EntryDte FROM #tmpAdj)
SELECTd.EntryDte
,ISNULL(c.Charges,0)AS Charges
,ISNULL(b.Payments,0)AS Payments
,ISNULL(a.Adjusts,0)AS Adjusts
FROM dates AS d LEFT OUTER JOIN #tmpChgs AS c ON d.EntryDte = c.EntryDte
LEFT OUTER JOIN #tmpPmts AS b ON d.EntryDte = b.EntryDte
LEFT OUTER JOIN #tmpAdj AS a ON d.EntryDte = a.EntryDte;
Best regards,
Mike
November 3, 2015 at 9:22 pm
Thanks Mike. That doesn't include the sums (to combine days that have values from more than one of the 3 tables), but I like the way the EntryDte field is combined.
November 3, 2015 at 9:41 pm
Why not just create a Calendar table with all the dates you want (no gaps!) and then just outer join the other tables to it? Then you can do totals etc, and it's stupid easy.
November 3, 2015 at 10:02 pm
pietlinden (11/3/2015)
Why not just create a Calendar table with all the dates you want (no gaps!) and then just outer join the other tables to it? Then you can do totals etc, and it's stupid easy.
Good point; the calendar table we use works fine. I was writing it for someone else & didn't want to assume they had such a table. Thanks for the answer!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply