October 3, 2003 at 11:02 am
I am trying to write a query to get the following results
A,B,C
6,0,0
5,1,0
4,2,0
3,3,0
2,4,0
5,0,1
4,0,2
4,1,1
3,1,2
2,2,2
2,3,1
3,2,1
I am using the following query to get those results. I need help to get the above mentione results.
DECLARE @a INT,@B INT,@C INT,@CNT INT
SELECT @a = 2, @b-2 = 2, @C = 2
WHILE @CNT > 0
BEGIN
SELECT (@A + @b-2 + @C)- @CNT, @CNT, 0
SELECT @CNT = @CNT - 1
END
SELECT @CNT = @C
WHILE @CNT > 0
BEGIN
SELECT (@A + @C + @b-2)- @CNT,0, @CNT
SELECT @CNT = @CNT - 1
END
WHILE @CNT > 0
BEGIN
SELECT (@A + @C + @b-2)- (@CNT), @C - @b-2, @CNT -@B
SELECT @CNT = @CNT - 1
END
Thanks in advance,
Charlie
October 3, 2003 at 11:11 am
Why not to use a temporary table or table variable, insert your data then select then at once?
Bye
Gabor
Bye
Gabor
October 3, 2003 at 11:16 am
I don't have the data with me,I have to generate it when the data is passed into the script. I am using @a, @b-2 and @C to pass the data, the variable values change when used in the application. for example, it can be like @a = 3, @b-2 = 4 and @C = 5 and the result set we are looking for will change accordingly.
October 3, 2003 at 11:17 am
I don't have the data with me,I have to generate it when the data is passed into the script. I am using @a, @b-2 and @C to pass the data, the variable values change when used in the application. for example, it can be like @a = 3, @b-2 = 4 and @C = 5 and the result set we are looking for will change accordingly as
12,0,0
11,1,0
10,2,0
9,3,0
ETC.
October 6, 2003 at 10:52 am
Often, in a situation like this, it's a good idea to go back to the English statement of the requirements. If the statements you've translated from English (or whatever your native tongue may be) don't do what you want, it can mean either that the logic behind the translation itself is at fault, or that in the "foreign" language (T-SQL), the words don't come together as you thought they did.
Can you tell us, in English, what you're trying to accomplish?
R David Francis
R David Francis
October 6, 2003 at 12:09 pm
Charlie,
If I understand you, you want all the 3 digit combinations that add up to the sum of the three parameters that are passed into the SP. To accomplish this, you will need to use a temp table to store your valid combinations and three nested loops. Something like the following:
CREATE PROCEDURE p_Get_ValidCombos AS
SET NOCOUNT ON
DECLARE @Sumint,
@Xint,
@Yint,
@Zint
CREATE TABLE #TmpCombos (
A int,
B int,
C int)
SET @X = 0
SET @Y = 0
SET @Z = 0
WHILE @X <= @Sum
BEGIN
WHILE @Y <= @Sum
BEGIN
WHILE @Z <= @Sum
BEGIN
IF @X + @Y + @Z = @Sum
INSERT INTO #TmpCombos
VALUES (@X, @Y, @Z)
SET @Z = @Z + 1
END
SET @Y = @Y + 1
SET @Z = 0
END
SET @X = @X + 1
SET @Y = 0
SET @Z = 0
END
SELECT *
FROM #TmpCombos
ORDER BY A DESC, B DESC, C DESC
DROP TABLE #TmpCombos
October 6, 2003 at 1:50 pm
Hi Dave,
Thankyou very much for your script, I am almost looking for the similar kind of script, with little modification it works perfectly for me.
I appriciate your help.
Charlie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply