SUM Totals by Year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SUM Totals by Year

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-09-03 : 13:05:27

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
2012 Total 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
2013 Total 110,979.23
0004544000 Total 197,401.81
Grand Total 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.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 13:21:54

Hard to tell what are the data types and column names are from the data you posted. But, is it as simple as adding a WHERE clause?

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

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-09-03 : 14:57:10

I tried the following.

SELECT [Fiscal Year], [AmountinLC]
FROM [UniversalDW].[Staging].[HRIS_Recruiting_GL]
WHERE [Fiscal Year] IS NOT NULL
AND [AmountinLC] NOT LIKE '%Total%'

and it did not work. the rows that are the Summed Totals are still there.

(Sorry about the formatting, I will see if I can fix it so that it's clearer.)

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-03 : 15:23:22

I'm still not clear on what is not working. Here are some link that can help you prepare your question with sample data and expected output so that we can ehlp you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-12 : 15:46:21

quote:
Originally posted by brubakerbr

I tried the following.

SELECT [Fiscal Year], [AmountinLC]
FROM [UniversalDW].[Staging].[HRIS_Recruiting_GL]
WHERE [Fiscal Year] IS NOT NULL
AND [AmountinLC] NOT LIKE '%Total%'

Try this and see if it works

WHERE ISDATE([Fiscal Year]) = 1

I think that'll work with 4-digit years. I believe your problem is that the import isn't bringing over a NULL in those fields, it's bringing in a blank space, which isn't a NULL.

You could also go fix the source and have it remove the totals from the data. Frankly, I think this is pretty reasonable thing to ask.

You've also kind of grouped the data in a way that I'm unsure what your source actually is, so my solution might be totally dumb.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources