SUM total by Year

Question

I am working on an HR project that receives an export from our SAP system weekly. It delivers the weekly totals that were input into the General Ledger for a specific account.

Company Code    G/L Fiscal Year Local Currency  Amount in LC
0020            4544000 2012            USD         575.00
0020            4544000 2012            USD         252.70
0020            4544000 2012            USD         89.75
0020            4544000 2012            USD         44.00
NULL            NULL    2012 Total      NULL        86,422.58
0020            4544000 2013            USD         2,000.00
0020            4544000 2013            USD        -2,000.00
0020            4544000 2013            USD         35.00
0020            4544000 2013            USD         35.00
NULL            NULL    2013 Total      NULL        110,979.23
NULL            0004544000 Total        NULL        197,401.81
Grand Total     NULL    NULL            NULL        197,401.81

(I removed several rows to save space)

What I need is for this to be simply the Sum of the Year.

Fiscal Year       Amount in LC  
2012              86,422.58
2013              110,979.23

And it needs to continue on well into 2016 or beyond.

The file I am importing is an .xlsx that is exported from an SAP system. I have had to get a consultant to refine the export as the first couple were unusable.

I currently have an SSIS package that imports the data, runs it through an aggregation before exporting it to a staging table. However, the Already Sumed totals is causing a duplication in the staging table. If there is a way to run a derived column to remove the data then that would also help.

The aggregation data looks like this.

Fiscal Year     Amount in LC
NULL            394803.62         <- Needs to be removed
2012            86422.58          <- Correct
2013            110979.23         <- Correct
2012 Total      86422.58          <- Duplication, Needs to be removed
2013 Total      110979.23         <- Duplication, Needs to be removed

Thank you for any help you can provide.

Answer 1

Remove the aggregation in your SSIS and export to a table. I assume the table will look like the "export from our SAP system weekly" table you described.

Then, write a stored procedure to get your results from the table:

Select LEFT([Fiscal Year],4) AS [Fiscal year] , [Amount in LC]
/* NOT NULL will exclude NULLs in [Fiscal Year] */
From <your table>
Where ([Fiscal Year] IS NOT NULL) AND [Fiscal Year] LIKE '%Total'
/* Group by eliminates duplicates */
Group By [Fiscal Year], [Amount in LC]

The aggregation is already in the table, you're just merely picking it out.

Answer 2

I assume since you're running aggregation in SSIS that there's a sql task being executed.

You should edit the WHERE criteria of that sql task to exclude records:

WHERE [Fiscal Year] IS NOT NULL 
  AND [Fiscal Year] NOT LIKE '%Total%'

If not using a sql task, you could add a conditional split between the aggregation and the ole db destination.