Re: Compare row data and input data into a new field

Hugo/Vishal

Thank you

your suggestions worked well



"Hugo Kornelis" wrote:

> 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
> >
> >ID Week_as_Int WeekEnding Check
> >ASome 523 5/06/2005
> >ASome 531 31/07/2005
> >ASome 548 27/11/2005
> >ASome 549 4/12/2005
> >ASome 604 22/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
>
.

Relevant Pages