Avatar of trims30
trims30
 asked on

Need SQL method to Summarize Data

Need some direction on how best to program the following:

I have two tables "MASTER" and "TRANSACTION"
TRANSACTION field MID is linked to MASTER ID Field MID

MASTER Table contains MID, Description and 12 columns "Jan" Feb".. etc, each representing expenditures for each month of the year.

TRANSACTION table contains MID, Transaction Date and Transaction Amount

I need to:
1. zero out each month column in each row of Master Table
2. read each Transaction Row and add it's Transaction Amount to the proper month column in the related MASTER Table Row.

Any suggestions??

trims30
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
trims30

8/22/2022 - Mon
Patrick Matthews

With respect, I think your design for the MASTER table is a bad idea.  Any how...

/* zero out the months first */

UPDATE MASTER
SET Jan = 0,
    Feb = 0,
    Mar = 0,
...
    Dec = 0

/* get monthly totals */

SELECT MID, 
    SUM(CASE WHEN DATEPART(month, TranDate) = 1 THEN TranAmt ELSE 0 END AS Jan,
    SUM(CASE WHEN DATEPART(month, TranDate) = 2 THEN TranAmt ELSE 0 END AS Feb,
    SUM(CASE WHEN DATEPART(month, TranDate) = 3 THEN TranAmt ELSE 0 END AS Mar,
...
    SUM(CASE WHEN DATEPART(month, TranDate) = 12 THEN TranAmt ELSE 0 END AS Dec
INTO #tmp
FROM TransTable
GROUP BY MID

/* update with monthly totals */

UPDATE MASTER
SET Jan = t.Jan,
    Feb = t.Feb,
    Mar = t.Mar,
...
    Dec = t.Dec
FROM MASTER m INNER JOIN
    #tmp t ON m.MID = t.MID

DROP TABLE #tmp

Open in new window

trims30

ASKER
matthewspatrick:

Master Table is something I inherited.  
I'll review your example and let you know how it fits for me.
At first glance looks good...

Thanks
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
trims30

ASKER
matthewspatrick:
It Appears to be too difficult solution to delegate to server.
Probably best to do on Client because...
1. Fiscal year can start in Jan , July or any month so Datepart(month,Transdate) may not point to correct column
2. I have been advised that configuration also allows 13 fiscal periods of 28 days to throw an additional monkey wrench into the mix.
3. Summary is performed once each fiscal period so it's not critical that it be done on server - we can suffer some degradation in performance once a month.

Thanks for your assistance on this , I will award you the points and close the question.

I learned something on this.

Regards,

Lee
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
trims30

ASKER
Thank You - See my comments.