QUERY - 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)

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-05 : 06:27:31

Hi

I have table like this..


ROW_ID	       SNO	        A		B	C	D
1		6		10		4.320	70.680	
2		7		10		6.200	68.800
3		8		10		10.580	64.420
4		9		10		6.770	68.230

I want to update the D column based on the C column values.

Conditions

In C columns values are like this...


Take the C column values.
FIRST ROW		SECOND ROW 
70.680	<	68.800,	
68.800	<  	64.420

The above two conditions are false so the D column should update 0 for both the rows

The below conditions are true so the D column should update 1
64.420<68.230

This checks will continue all rows in table…

The output is like this…


ROW_ID	       SNO	         A		B	C	D
1		6		10		4.320	70.680	0
2		7		10		6.200	68.800	0
3		8		10		10.580	64.420	1
4		9		10		6.770	68.230	0

Please help out this...

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-05 : 07:04:06

Hi

Pls Help on this...Guys..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-05 : 07:15:30

Should be something like this:

update t1
set D= case when t2.C is null then 0
            when t1.C < t2.C then 1 
            else 0 end
from your_table t1
left join your_table t2 on t2.ROW_ID = t1.ROW_ID+1

No, you're never too old to Yak'n'Roll if you're too young to die.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-05 : 08:16:46

Hi

I have test your query it was like this...

But i want once the query update 1 for first time rest will all rows are zero...
[/code]
73.390 0
34.350 0
20.573 0
15.995 0
10.820 0
9.612 0
7.530 0
4.863 1
5.222 0
3.546 0
1.980 0 -- even this row also i need zero..
2.976 0
...[/code]pls help this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-05 : 08:51:47

I am busy now, but you should try my statement using "UPDATE TOP 1 t1 ..."
Not sure but give it a try!

No, you're never too old to Yak'n'Roll if you're too young to die.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-06 : 03:41:30

Hi

Please help on this............

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 03:48:21

[code]DECLARE @Sample TABLE
(
RowID INT,
Sno INT,
A INT,
B MONEY,
C MONEY,
D TINYINT
)

INSERT @Sample
SELECT 1, 6, 10, 4.320, 70.680, 0 UNION ALL
SELECT 2, 7, 10, 6.200, 68.800, 0 UNION ALL
SELECT 3, 8, 10, 10.580, 64.420, 0 UNION ALL
SELECT 4, 9, 10, 6.770, 68.230, 0

SELECT *
FROM @Sample

UPDATE s
SET s.D = 1
FROM @Sample AS s
LEFT JOIN @Sample AS q ON q.RowID = s.RowID + 1
WHERE s.C < q.C

SELECT *
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-06 : 04:17:09

Hi

Thanks peso

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-06 : 04:28:18

Hi Peso

I have tested these data


INSERT	@Sample
SELECT	1, 6, 10,  4.320, 73.390, 0 UNION ALL
SELECT	2, 7, 10,  6.200, 34.350, 0 UNION ALL
SELECT	3, 8, 10, 10.580, 20.573, 0 UNION ALL
SELECT	4, 9, 10,  6.770, 15.995, 0 UNION ALL
SELECT	5, 9, 10,  6.770, 10.820, 0 UNION ALL
SELECT	6, 9, 10,  6.770, 9.612,  0 UNION ALL
SELECT	7, 9, 10,  6.770, 7.530,  0 UNION ALL
SELECT	8, 9, 10,  6.770, 4.863,  0 UNION ALL
SELECT	9, 9, 10,  6.770, 5.222,  0 UNION ALL
SELECT	10, 9, 10,  6.770, 3.546, 0 UNION ALL
SELECT	11, 9, 10,  6.770, 1.980, 0 UNION ALL
SELECT	12, 9, 10,  39.29, 2.976, 0

I got the result set like this..



1	6	10	4.32	73.39	0
2	7	10	6.20	34.35	0
3	8	10	10.58	20.573	0
4	9	10	6.77	15.995	0
5	9	10	6.77	10.82	0
6	9	10	6.77	9.612	0
7	9	10	6.77	7.53	0
8	9	10	6.77	4.863	1
9	9	10	6.77	5.222	0
10	9	10	6.77	3.546	0
11	9	10	6.77	1.98	1  --- Here again update one
12	9	10	39.29	2.976	0

I need when condition meet first time there only it's has to update one rest all should be zero even the condition meets also..

6 9 10 6.77 9.612 0
7 9 10 6.77 7.53 0
8 9 10 6.77 4.863 1
9 9 10 6.77 5.222 0
10 9 10 6.77 3.546 0
11 9 10 6.77 1.98 0--- like this...
12 9 10 39.29 2.976 0

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-06 : 04:47:54

Hi Peso

There...........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 04:51:38

[code]DECLARE @Sample TABLE
(
RowID INT,
Sno INT,
A INT,
B MONEY,
C MONEY,
D TINYINT
)

INSERT @Sample
SELECT 1, 6, 10, 4.320, 73.390, 0 UNION ALL
SELECT 2, 7, 10, 6.200, 34.350, 0 UNION ALL
SELECT 3, 8, 10, 10.580, 20.573, 0 UNION ALL
SELECT 4, 9, 10, 6.770, 15.995, 0 UNION ALL
SELECT 5, 9, 10, 6.770, 10.820, 0 UNION ALL
SELECT 6, 9, 10, 6.770, 9.612, 0 UNION ALL
SELECT 7, 9, 10, 6.770, 7.530, 0 UNION ALL
SELECT 8, 9, 10, 6.770, 4.863, 0 UNION ALL
SELECT 9, 9, 10, 6.770, 5.222, 0 UNION ALL
SELECT 10, 9, 10, 6.770, 3.546, 0 UNION ALL
SELECT 11, 9, 10, 6.770, 1.980, 0 UNION ALL
SELECT 12, 9, 10, 39.29, 2.976, 0

SELECT *
FROM @Sample

UPDATE @Sample
SET D = 1
WHERE RowID IN (
SELECT MIN(s.RowID)
FROM @Sample AS s
LEFT JOIN @Sample AS q ON q.RowID = s.RowID + 1
WHERE s.C < q.C
)

SELECT *
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-06 : 04:59:26

Thanks............

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources