March 17, 2009 at 4:22 am
Hi,
I have a table with the following data.
DID-------ID--------Value
1---------1-----------2
1---------2-----------12
1---------3-----------45
1---------4-----------8
1---------5-----------63
1---------6-----------24
2---------1-----------89
2---------2-----------20
2---------3-----------46
2---------4-----------96
I have to write a procedure with two parameters @startposition and @endposition. If @StartPosition contains 1 and @endposition contains 3 then i have to sum first three rows i.e. 2+12+45 then next three rows i.e. 12+45+8 then 45+8+63 then 8+63+24 then 63+24 and then only 24. Same goes for next DID i.e 89+20+46 then 20+46+96 then 46+96 and then only 96. I would have same number of rows in output.
How can i do it in one query.
I am using SQL Server 2005. If you have any question please let me know.
Regards,
Sulaman
March 17, 2009 at 4:40 am
Hmm... And what if @startposition is 2 and @endposition 3?
Does that mean you skip rows with 1 altogether and only need sums of rows with ID 2+3, 3+4, 4+5, 5+6, 6?
Also, it would be nice to supply us with table definition (CREATE TABLE) and test data in form of INSERT INTO ...
March 17, 2009 at 4:43 am
yes you got it right.
March 17, 2009 at 4:43 am
What do you want the output to look like if @StartPosition contains 1 and @endposition contains 3?
How do the @StartPosition and @endposition correspond to values in the table? It's not clear.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 4:47 am
Try something like this...this works i think
while @startposition<=6
(
select SUM(Value) from
where id >= @startposition and id<=@endposition and DID=@DID
select @startposition=@startposition+1
select @endposition=@endposition+1
)
March 17, 2009 at 4:51 am
Here is a solution that doesn't use a loop.
It uses a tally table.
The only thing missing from the query is that I'm not 100% sure how the StartPostion comes into play
DECLARE @Table TABLE
([DID] INT,
[ID] INT ,
[Value] INT)
DECLARE @startposition INT
DECLARE @endposition INT
SELECT
@startposition = 1,
@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 * FROM @Table
SELECT DID,n.n,SUM([VALUE])
FROM Tally n
INNER JOIN @Table i
ON n.n <= i.Id AND i.id < n.n + @endposition
GROUP BY n.n,DID
ORDER BY DID,n
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 17, 2009 at 4:55 am
If @startposition = 1 and @endposition = 3 then Output column would be something like following
DID-------ID--------Value---------Output
1---------1-----------2------------2+12+45 = 59
1---------2-----------12-----------12+45+8 = 65
1---------3-----------45-----------45+8+63
1---------4-----------8------------8+63+24
1---------5-----------63-----------63+24
1---------6-----------24-----------24
2---------1-----------89-----------89+20+46
2---------2-----------20-----------20+46+96
2---------3-----------46-----------46+96
2---------4-----------96-----------96
I have to do it in one query without using loop. My be it is possible with recursive CTE but i am not able to do it yet.
March 17, 2009 at 4:56 am
if the table has one millinon rows:-)
March 17, 2009 at 4:58 am
does my solution not work???
I'll test it with a million rows and see what happens...
OH and you haven't given us an example of a different start position?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 17, 2009 at 5:01 am
Christopher Stobbs (3/17/2009)
does my solution not work???I'll test it with a million rows and see what happens...
OH and you haven't given us an example of a different start position?
It matches OP's desired result set, Chris:cool:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 5:01 am
As a quick solution, I would use this:
/*Create environment*/
create table #test(did int, id int, myval int)
/*create test data*/
insert into #test(did, id, myval)
SELECT 1, 1, 2
UNION SELECT 1,2,12
UNION SELECT 1,3,45
UNION SELECT 1,4,8
UNION SELECT 1,5,63
UNION SELECT 1,6,24
UNION SELECT 2,1,89
UNION SELECT 2,2,20
UNION SELECT 2,3,46
UNION SELECT 2,4,96
/*set parameters*/
DECLARE @startposition INT, @endposition INT, @rows INT
SELECT @startposition = 2, @endposition = 3
SET @rows = @endposition - @startposition
/*This does the work*/
SELECT t1.did, t1.id, SUM(t2.myval)
FROM #test t1
JOIN #test t2 ON t2.did = t1.did
AND t1.id >= @startposition
AND t2.id >= t1.id
AND t2.id <= t1.id + @rows
GROUP BY t1.did, t1.id
/*cleanup*/
DROP TABLE #test
But if you're going to work with large amounts of data, solution using "Tally" table might be more effective. My solution uses triangular self-join, which can sometimes be pretty nasty.
March 17, 2009 at 5:06 am
I see that Christopher already posted the solution with Tally table, so just try to tweak the parameters there to suit your needs...
BTW, there seems to be some problem with code display, all "INT" in declaration of parameters are doubled.
March 17, 2009 at 5:17 am
@Christopher
Yes your solution works but as you said, @startposition is not going to play any role in it.
And as long as different @startpositions are concerned, it is very tricky scenerio [Smile]
if @startposition is 2 and @endposition is 3 then
DID-------ID--------Value----------Output
1---------1-----------2------------12+45
1---------2-----------12-----------45+8
1---------3-----------45-----------8+63
1---------4-----------8------------63+24
1---------5-----------63-----------24
1---------6-----------24-----------Null
2---------1-----------89-----------20+46
2---------2-----------20-----------46+96
2---------3-----------46-----------96
2---------4-----------96-----------Null
Please ignore my previous reply where i said that if @startposition is 2 then first row will be ignored all together.
If you still have any question please let me know.
March 17, 2009 at 5:20 am
@Christopher
Yes your solution works but as you said, @startposition is not going to play any role in it.
And as long as different @startpositions are concerned, it is very tricky scenerio 🙂
if @startposition is 2 and @endposition is 3 then
DID-------ID--------Value----------Output
1---------1-----------2------------12+45
1---------2-----------12-----------45+8
1---------3-----------45-----------8+63
1---------4-----------8------------63+24
1---------5-----------63-----------24
1---------6-----------24-----------Null
2---------1-----------89-----------20+46
2---------2-----------20-----------46+96
2---------3-----------46-----------96
2---------4-----------96-----------Null
Please ignore my previous reply where i said that if @startposition is 2 then first row will be ignored all together.
If you still have any question please let me know.
March 17, 2009 at 5:51 am
You can do it without a number table though I'd love to know what the @startPosition variable does...
DECLARE @Table TABLE
([DID] INT,
[ID] INT ,
[Value] INT)
DECLARE @startposition INT
DECLARE @endposition INT
SELECT
@startposition = 1,
@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
inner join @Table t2 on t1.DID=T2.did and t2.id between t1.id and T1.id+@endposition-1
group by t1.DID, t1.id
DID ID value output
----------- ----------- ----------- -----------
1 1 2 59
1 2 12 65
1 3 45 116
1 4 8 95
1 5 63 87
1 6 24 24
2 1 89 155
2 2 20 162
2 3 46 142
2 4 96 96
(10 row(s) affected)
(Later: Oops- Vladen beat me to it!)
Best wishes,
Phil Factor
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply