Compare row data and input data into a new field

Dear All
I have a simple query which I cannot seem to work out and would like some
help.
I have a small table with data like such
IDWeek_as_IntWeekEnding Check
ASome 5235/06/2005
ASome 53131/07/2005
ASome 54827/11/2005
ASome 5494/12/2005
ASome 60422/01/2006 1
what I need to do is to create a new column called check and put a "1" check
filed in the last one of the selection. In the example above it would be the
row with "604" in it but it is not always going to be "604" it could be
anything. So what I need to do is to look at each row and when it gets to the
last row that has the same id then to place a "1" in a check field.
The answer is easy using excel but I would like to take that intervension
out an incorporate it in a SQL script as part of the automation
Thanking you in advance
Regards
Newman
>>when it gets to the
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
(IDvarchar(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
"Newman Emanouel" wrote:

> Dear All
> I have a simple query which I cannot seem to work out and would like some
> help.
> I have a small table with data like such
> IDWeek_as_IntWeekEnding Check
> ASome 5235/06/2005
> ASome 53131/07/2005
> ASome 54827/11/2005
> ASome 5494/12/2005
> ASome 60422/01/2006 1
> what I need to do is to create a new column called check and put a "1" check
> filed in the last one of the selection. In the example above it would be the
> row with "604" in it but it is not always going to be "604" it could be
> anything. So what I need to do is to look at each row and when it gets to the
> last row that has the same id then to place a "1" in a check field.
> The answer is easy using excel but I would like to take that intervension
> out an incorporate it in a SQL script as part of the automation
> Thanking you in advance
> Regards
> Newman
>
|||On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:

>Dear All
>I have a simple query which I cannot seem to work out and would like some
>help.
>I have a small table with data like such
>IDWeek_as_IntWeekEnding Check
>ASome 5235/06/2005
>ASome 53131/07/2005
>ASome 54827/11/2005
>ASome 5494/12/2005
>ASome 60422/01/2006 1
>what I need to do is to create a new column called check and put a "1" check
>filed in the last one of the selection. In the example above it would be the
>row with "604" in it but it is not always going to be "604" it could be
>anything. So what I need to do is to look at each row and when it gets to the
>last row that has the same id then to place a "1" in a check field.
>The answer is easy using excel but I would like to take that intervension
>out an incorporate it in a SQL script as part of the automation
>Thanking you in advance
>Regards
>Newman
>
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)
Hugo Kornelis, SQL Server MVP
|||Hugo/Vishal
Thank you
your suggestions worked well
"Hugo Kornelis" wrote:

> On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:
>
> 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)
> --
> Hugo Kornelis, SQL Server MVP
>