March 17, 2009 at 6:04 am
Phil Factor (3/17/2009)
You can do it without a number table though I'd love to know what the @startPosition variable does...[/code]
As I understood it, @startposition tells you where to start - any rows with ID lower than @startposition are omitted, and the number of rows added in each sum depends on it as well.
Sulaman confirmed it when I described it, but so far he didn't reply to my post with solution based on this, so I'm not yet 100% sure whether it really does what was required.
March 17, 2009 at 6:06 am
OK then..... How's this
DECLARE @Table TABLE
([DID] INT,
[ID] INT ,
[Value] INT)
DECLARE @startposition INT
DECLARE @endposition INT
SELECT
@startposition = 2,
@endposition = 3
INSERT INTO @Table
SELECT 1,1,2 UNION ALL
SELECT 1,2,12 UNION ALL
SELECT 1,3,45 UNION ALL
SELECT 1,4,8 UNION ALL
SELECT 1,5,63 UNION ALL
SELECT 1,6,24 UNION ALL
SELECT 2,1,89 UNION ALL
SELECT 2,2,20 UNION ALL
SELECT 2,3,46 UNION ALL
SELECT 2,4,96
SELECT t1.DID, t1.ID, min(t1.value) as value, sum(t2.Value) as output FROM @Table t1
left outer join @Table t2 on t1.DID=T2.did
and t2.id between t1.id+@StartPosition-1 and T1.id+@endposition-1
group by t1.DID, t1.id
order by t1.did, t1.ID
/*
DID ID value output
----------- ----------- ----------- -----------
1 1 2 57
1 2 12 53
1 3 45 71
1 4 8 87
1 5 63 24
1 6 24 NULL
2 1 89 66
2 2 20 142
2 3 46 96
2 4 96 NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
(10 row(s) affected)
*/
Best wishes,
Phil Factor
March 17, 2009 at 6:11 am
That should be
[font="Courier New"]SELECT t1.DID, t1.ID, min(t1.value) as value, sum(t2.Value) as output FROM @Table t1
left outer join @Table t2 on t1.DID=T2.did
and t2.id between t1.id+@StartPosition-1 and T1.id+@endposition-1
group by t1.DID, t1.id
order by t1.did, t1.ID
[/font]
But the new code formatting is broken
Best wishes,
Phil Factor
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply