lundi 30 mars 2015

SQL View generate current YTD total for GL periods

My SQL skills are fairly basic and I'm trying to create a SQL View that will give me the current YTD figure for each of my GL Accounts - showing all 12 periods and then listing them as rows. I have a GL Summary table that gives me the opening DR and CR, then a DR and CR for each of the 12 periods. I wrote the code below but it only gives me point in time, it needs to be a running total (ie take the opening CD/DR then add the P1 CR/DR, then take that total and add the P@ CR/DR, etc). I can't work out how to do this without using a temp table. The image below shows there was a 500.00 DR transaction in P9 which made the balance 500.00. Period 10, 11 and 12 should also show as 500.00 not 1,000.00. Can anyone help please?



GLAccount Description FiscalYear Period Total
10105-30-30-10-05-00 Petty Cash 2015 P1 1,000.00
10105-30-30-10-08-00 Petty Cash 2015 P2 1,000.00
10105-30-30-10-12-00 Petty Cash 2015 P3 1,000.00
10105-30-30-10-50-00 Petty Cash 2015 P4 1,000.00
10105-30-30-10-92-00 Petty Cash 2015 P5 1,000.00
10105-30-30-10-95-00 Petty Cash 2015 P6 1,000.00
10105-30-30-10-97-00 Petty Cash 2015 P7 1,000.00
10105-30-30-20-05-00 Petty Cash 2015 P8 1,000.00
10105-30-30-20-08-00 Petty Cash 2015 P9 500.00
10105-30-30-20-50-00 Petty Cash 2015 P10 1,000.00
10105-30-30-20-51-00 Petty Cash 2015 P11 1,000.00
10105-30-30-20-97-00 Petty Cash 2015 P12 1,000.00





SELECT gl.MaskedAlias AS GLAcct,
gl.Description AS GLDesc,
gl.FiscalYear,
unpvt.Period,
unpvt.Total
FROM(SELECT DISTINCT gltsum.GLAcct,gltsum.FiscalYear,gla.MaskedAlias, gla.Description
FROM GLTranSumm AS gltsum
JOIN GLAcct AS gla ON (( gltsum.GLAcct ) = ( gla.AcctIndex ))
WHERE gltsum.FiscalYear >= 2015 ) gl
LEFT OUTER JOIN
( SELECT glts.GLAcct,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer01CR,0)+ ISNULL(glts.TranPer01DR,0)) P1,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer02CR,0)+ ISNULL(glts.TranPer02DR,0)) P2,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer03CR,0)+ ISNULL(glts.TranPer03DR,0)) P3,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer04CR,0)+ ISNULL(glts.TranPer04DR,0)) P4,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer05CR,0)+ ISNULL(glts.TranPer05DR,0)) P5,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer06CR,0)+ ISNULL(glts.TranPer06DR,0)) P6,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer07CR,0)+ ISNULL(glts.TranPer07DR,0)) P7,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer08CR,0)+ ISNULL(glts.TranPer08DR,0)) P8,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer09CR,0)+ ISNULL(glts.TranPer09DR,0)) P9,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer10CR,0)+ ISNULL(glts.TranPer10DR,0)) P10,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer11CR,0)+ ISNULL(glts.TranPer11DR,0)) P11,
SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer12CR,0)+ ISNULL(glts.TranPer12DR,0)) P12
FROM GLTranSumm AS glts
WHERE glts.GLType = 'Accrual' AND glts.FiscalYear >= 2015
GROUP BY glts.GLAcct) p
UNPIVOT
(
Total FOR Period IN (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
) AS unpvt
ON unpvt.GLAcct = gl.GLAcct

Aucun commentaire:

Enregistrer un commentaire