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.
| Author |
Topic |
|
sqlnoob35
Starting Member
10 Posts |
 Posted - 2009-02-27 : 09:33:51
|
| Hey guys,This should be an easy query to write, but I can't figure out how to code it in SQL.Here's what I need:Obtain a yearly total for each employee's monthly salary payments. I need the totals for the past 3 years.The table is called T_ANTY_PYMT, and ANTY_PYMT_NET_AMT contains their monthly payment. I would need to create user defined fields on my report so I can have a 2006 total, 2007 and so on. I wrote this query:SELECT A.TOTAL_2006 FROM (SELECT ANTY_PYMT_NET_AMT AS TOTAL_2006 FROM DSNP.PR01_T_ANTY_PYMT WHERE ANTY_PYMT_DT BETWEEN '2006-01-01' AND '2006-12-31') AS AThe problem with the query is it sums up the net amounts for every single employee. I need my query to show each employee's name and their own 2006 total. How can I tell SQL to only total the amounts for each unique social security number? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-27 : 09:36:35
|
[code]SELECT SSN, SUM(CASE WHEN dt >= '20060101' AND dt < '20070101' THEN amt ELSE 0 END) AS [2006], SUM(CASE WHEN dt >= '20070101' AND dt < '20080101' THEN amt ELSE 0 END) AS [2007]WHERE dt >= '20060101' AND dt < '20080101'GROUP BY SSNORDER BY SSN[/code]
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Problems with SUM () Function. Help!