SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SUM Totals by Year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/03/2013 :  13:05:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  13:21:54  Show Profile  Reply with Quote
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%'

Edited by - Lamprey on 09/03/2013 13:23:21
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 09/03/2013 :  14:57:10  Show Profile  Reply with Quote
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

Edited by - brubakerbr on 09/03/2013 14:58:30
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/03/2013 :  15:23:22  Show Profile  Reply with Quote
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
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 09/12/2013 :  15:46:21  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000