Link to home
Start Free TrialLog in
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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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

Avatar of trims30
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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Avatar of trims30

ASKER

Thank You - See my comments.