Compare row data and input data into a new field -- sql server mseq

Vishal Parkar
1/27/2006 7:17:02 AM

[quoted text, click to view]
last row that has the same id then to place a "1" in a check field. <<

With ref. to your above statement, how will you be defining last row, will
it be on the basis of the maximum number of week_as_int column for respective
id
OR will it be on the basis of maximum date from "weekending" column for
respective id?

If its on the basis of max of week_As_int column your query will look like
as shown in the following example, you can change the same query by selecting
max of weekending column.

--table and sample data

create table t
(ID varchar(50),Week_as_Int int, WeekEnding datetime, chk int)

insert into t (id, week_As_int,weekending)
select 'ASome',523,'2005/06/5' union all
select 'ASome',548,'2005/11/27' union all
select 'ASome',549,'2005/12/4' union all
select 'ASome',604,'2005/01/22' union all
select 'bSome',700,'2005/06/5' union all
select 'bSome',549,'2005/12/4' union all
select 'bSome',604,'2005/01/22' union all
select 'cSome',700,'2005/06/5' union all
select 'cSome',701,'2005/12/4' union all
select 'cSome',704,'2005/01/22'

--update statement to update the column chk .

update t
set chk = 1
from t a join
(select "id", max(week_as_int) week_as_int
from t
group by "id") b on a.id = b.id and a.week_as_int = b.week_as_int

[quoted text, click to view]

Hugo Kornelis
1/28/2006 12:04:05 AM

[quoted text, click to view]

Hi Newman,

Why store it in the table and recalculate it each time you need it?

CREATE VIEW YourView
AS
SELECT a.ID, a.Week_as_Int, a.WeekEnding,
CASE WHEN Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = a.ID)
THEN 1
END AS Check
FROM YourTable AS a

Of course, if you insist on storing it in the table:

UPDATE YourTable
SET Check = 1
WHERE Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = YourTable.ID)

--

Newman Emanouel
1/29/2006 7:31:27 PM

Hugo/Vishal

Thank you

your suggestions worked well

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : Compare row data and input data into a new field