Help with subquery - 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 2008 Forums

Transact-SQL (2008)

Help with subquery

Author

Topic

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-22 : 10:51:11

A few people were kind enough to help me build a query a number of weeks ago, but it has become a bit more complex than before.

Here is the solution query to my previous issue:

.............
SELECT
ContractYear,
SalesAmt,
ExpenseAmt,
SalesAmt - ExpenseAmt as YearSurplus,
(SELECT SUM(SalesAmt - ExpenseAmt)
FROM
dbo.Sales
WHERE
ContractYear <= s.ContractYear) as RunningSurplus
FROM
dbo.Sales s
...............

This calculated a running surplus that was always based on the previous year's surplus and so on. Now, its possible that any ContractYear could also have a forcedSurplus amount. When this is the case, I need to ignore all the prior surplus values earlier than that contract year, and just use the forcedsurplus year as the starting year, and then using the same calculation as before to calculate the running surplus each year.

I hope that makes sense. I found it somewhat hard to describe.

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-22 : 14:30:16

To describe what I need a bit more..

For each row, I need to determine what is the most recent prior contract year that has a forcedsurplus amount. Then I need to use that contract year as the base year for the subquery above, and add in the forcedsurplus amount.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 14:46:50

Sample data? Expected output? If you supply that in a consumable format you'll probably get better help faster.

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

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-22 : 15:45:11

Thanks for the advice. I knew I didn't draw up my question/details very well.

I think I may have solved my own problem though.. just started throwing together small pieces that worked and then re-using them in case statements..

SELECT TOP (100) PERCENT ContractYear, Sales, Expenses, ForcedSurplus, Sales - Expenses AS YearSurplus, CASE WHEN
(SELECT MAX(ContractYear) AS Expr1
FROM dbo.TestSales
WHERE (ContractYear <= s.ContractYear) AND (ContractID = s.ContractID) AND (ForcedSurplus IS NOT NULL)) IS NOT NULL THEN
(SELECT SUM(Sales - Expenses + ISNULL(ForcedSurplus, 0)) AS Expr1
FROM dbo.TestSales AS TestSales_1
WHERE (ContractYear <= s.ContractYear) AND (ContractID = s.ContractID) AND (ContractYear >=
(SELECT MAX(ContractYear) AS Expr1
FROM dbo.TestSales AS TestSales_2
WHERE (ContractYear <= s.ContractYear) AND (ContractID = s.ContractID) AND (ForcedSurplus IS NOT NULL)))) ELSE
(SELECT SUM(Sales - Expenses) AS Expr1
FROM dbo.TestSales
WHERE (ContractYear <= s.ContractYear) AND (ContractID = s.ContractID)) END AS RunningSurplus,
(SELECT MAX(ContractYear) AS Expr1
FROM dbo.TestSales
WHERE (ContractYear <= s.ContractYear) AND (ContractID = s.ContractID) AND (ForcedSurplus IS NOT NULL)) AS ForcedYear, ContractID
FROM dbo.TestSales AS s
ORDER BY ContractID, ContractYear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:56:40

why do you need all these subqueries?
i think better to do like this


SELECT s.*,s1.RunningSurplus,s1.ContractYear AS ForcedYear
FROM dbo.TestSales AS s
CROSS APPLY (SELECT ContractYear ,SUM(Sales - Expenses + ISNULL(ForcedSurplus, 0)) AS RunningSurplus,
             ROW_NUMBER() OVER (ORDER BY ContractYear DESC) AS Seq
             FROM dbo.TestSales 
             WHERE ContractYear <= s.ContractYear
             AND (ContractID = s.ContractID) AND (ForcedSurplus IS NOT NULL)
             GROUP BY ContractYear 
             )s1
WHERE s1.Seq=1
ORDER BY ContractID, ContractYear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-23 : 10:08:54

ha.. I did all those subqueries, because I don't know what I'm doing, and I'm just thrilled I was able to write something like that and get the desired result.

I'll try yours and see if it yields the same dataset...

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-23 : 10:13:32

That doesn't spit out what mine does. Only 3 rows were returned. The result dataset should have the same number of rows as the the initial table has. (currently, 8)

TestSales table:
Columns: SalesID, Sales, Expenses, ContractYear, ContractID, ForcedSurplus
1 3.0000 1.0000 2001 1 NULL
2 5.0000 4.0000 2002 1 7
3 2.0000 1.0000 2003 1 NULL
4 6.0000 3.0000 2001 2 NULL
5 8.0000 3.0000 2002 2 NULL
6 9.0000 6.0000 2003 2 NULL
7 4.0000 1.0000 2004 1 6
8 5.0000 4.0000 2004 2 NULL

my query's result dataset:
Columns: ContractYear, Sales, Expenses, ForcedSurplus, YearSurplus, RunningSurplus, ForcedYear, ContractID
2001 3.0000 1.0000 NULL 2.0000 2.0000 NULL 1
2002 5.0000 4.0000 7 1.0000 8.0000 2002 1
2003 2.0000 1.0000 NULL 1.0000 9.0000 2002 1
2004 4.0000 1.0000 6 3.0000 9.0000 2004 1
2001 6.0000 3.0000 NULL 3.0000 3.0000 NULL 2
2002 8.0000 3.0000 NULL 5.0000 8.0000 NULL 2
2003 9.0000 6.0000 NULL 3.0000 11.0000 NULL 2
2004 5.0000 4.0000 NULL 1.0000 12.0000 NULL 2

darn, how do I get the column names to show up when I copy and paste a dataset like that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources