Nested Select Statements in Stored Proc

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 2005 Forums

Transact-SQL (2005)

Nested Select Statements in Stored Proc

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-09-25 : 17:21:07

I am trying to calculate some totals based on two tables. One particular account no. in the table is subject to a special tax that the others are not. I want to get the totals, and have the special tax filled in only for the account that has it. Here is my select statement:

SELECT l.ProjID, l.Yr, w.Acct, Sum(w.[Daily]*(l.[Days])) AS SalAmount, Sum((w.[Allowances])*l.[Days]) AS AllowAmount,
(SELECT (Sum(s.[Daily]*(t.[Days]))*0.135) AS TaxAmount
FROM _allLabor t LEFT JOIN z_Full_Wages s ON t.Pen = s.PEN
WHERE s.Acct = '4009' AND s.LocAbbrev = 'Ferney'
GROUP BY t.ProjID, t.Yr
HAVING t.ProjID=@projID AND t.Yr=@year
)AS FranceTaxAmount
FROM _allLabor l LEFT JOIN z_Full_Wages w ON l.Pen = w.PEN
GROUP BY l.ProjID, l.Yr, w.Acct
HAVING l.ProjID=@projID AND l.Yr=@year

This is almost what I want, but my output is:

ProjId Yr Acct SalAmount AllowAmount FranceTaxAmount
1224 2009 4006 1600.06 NULL 115.90
1224 2009 4008 0 NULL 115.90
1224 2009 4010 326616.50 71944.27 115.90
1224 2009 4009 173250.92 1026.27 115.90

How can I get the FranceTaxAmount to only attach to the record with the Acct of 4009?

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 00:24:43

Sounds like you need to use a case statement

You can do anything at www.zombo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 00:54:52

try like this

SELECT l.ProjID, l.Yr, w.Acct, Sum(w.[Daily]*(l.[Days])) AS SalAmount, Sum((w.[Allowances])*l.[Days]) AS AllowAmount,
Sum(case when w.Acct = '4009' AND w.LocAbbrev = 'Ferney' then w.[Daily]*(l.[Days] else 0 end)*0.135 AS FranceTaxAmount 
FROM _allLabor l LEFT JOIN z_Full_Wages w ON l.Pen = w.PEN
WHERE l.ProjID=@projID AND l.Yr=@year
GROUP BY l.ProjID, l.Yr, w.Acct

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-09-26 : 11:31:50

That works perfectly! You know, I had started with a case statement, but tried to nest them too - something like
Case when w.Acct = '4009'
Case when w.LocAbbrev = 'Ferney' Then w.[Daily]*(l.[Days] * 0.135 etc...
End
End
As FranceTaxAmount

That caused me all kinds of trouble so I tried the nested select instead.

Thanks for all your help - its much appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources