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

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

  • Re: Need help with a SQL Query
    ... I get store id 6380 as a result when I run these against pubs. ... BG, SQL Server MVP ... Peter, this is a relational division problem. ... > I need help with a query below. ...
    (microsoft.public.sqlserver.programming)
  • Re: default value equals a different field value
    ... But why would you want to store the full name if you already have first ... Just concatenate the two when you query the table. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: FTS Performance in SQL 2005
    ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Date_Time Convert(24) to DateTime Format
    ... You posted a query in the original post, ... When it's in smalldatetime format then use the ... >>> select counterDateTime from CounterData ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)