Sum of values between nulls - 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 2005 Forums

Transact-SQL (2005)

Sum of values between nulls

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-22 : 06:18:37

Hi Guys,

I have a resulset in this manner.

id----- amt
----------- -----------
1----- NULL
2----- 20
3----- 20
4----- 20
8----- NULL
9----- 30
10----- 120
11----- 110
21----- NULL
22----- 110

Furthermore I would like to sum the minimum amt & maximum amt between two Null values.So ideally the output should be

minid maxid amt
2----- 4----- 40
9----- 11----- 140
21---- NULL----- 110

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 06:42:32

[code];
With Base_CTE (RowNo,ID) AS
(SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNo,
id
FROM YourTable
WHERE amt IS NULL)

SELECT min.id as minid,max.id as maxid,min.amt+max.amt as amt
FROM Base_CTE b
LEFT JOIN Base_CTE c
ON c.RowNo=b.RowNo + 1
OUTER APPLY (SELECT TOP 1 id,amt
FROM YourTable
WHERE id > b.id
AND (id < c.id OR c.id IS NULL)
AND amt IS NOT NULL
ORDER BY id
)min
OUTER APPLY (SELECT TOP 1 id,amt
FROM YourTable
WHERE id > b.id
AND (id < c.id OR c.id IS NULL)
AND amt IS NOT NULL
ORDER BY id DESC
)max[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources