"Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
news:4A314E2E-258E-4649-99BD-28CF5E30906C@microsoft.com...
> This can be acheived in one update query, if I am not wrong.
> But can you tell us what you have to update. which record?
>
> Moreover, this query might be wrong. There is no guarantee that
> max(Trans_date) and max(audit_number) will point to the same row, though
it
> is in your scenario.
> And you don't need a temp table, you can use an inline view.
>
> SELECT
> MAX(Trans_Date) AS 'Max_Trans_Date',
> MAX(Audit_Number) AS 'Max_Audit_Number',
>
>
> And you don't need a temp table, you can use an inline view. Something
like
> this..
>
> SELECT
> Trans_Date,
> Audit_Number,
> Policy_Number,
> Master_Flag
>
> FROM AccountingLog AS AL
> INNER JOIN (SELECT
> MAX(Trans_Date) AS 'Max_Trans_Date',
> MAX(Audit_Number) AS 'Max_Audit_Number',
> Policy_Number
> FROM  AccountingLog
> GROUP BY
> Policy_Number) AS MaxDate
> ON AL.Policy_Number = MaxDate.Policy_Number
> AND AL.Audit_Number = MaxDate.Audit_Number
>
> Though the above can be written even in a better way, its just an example.
> We will be able to help you better, if you give the functionality and the
> end results.
> what needs to be updated..
>
>

"ricky" <ri***@ricky.com> wrote in message
news:eqbj9GyeGHA.4892@TK2MSFTNGP02.phx.gbl...
> Good Morning
>
> I have the following table format, whereby I need to stamp all records for
> that policy with the most current transaction's Master_Flag.
>
> Audit_Number     Policy_Number     Master_Flag     Trans_Date
> 1                          CC123                  4
> 27/12/2005
> 1                          CC123                  4
> 27/12/2005
> 2                          CC123                  4
> 28/12/2005
> 2                          CC123                  4
> 28/12/2005
> 3                          CC123                  A
> 01/01/2006
>
>
> I have written a query to obtain the latest transaction date:
>
> --************************************************
>
> SELECT
> MAX(Trans_Date) AS 'Max_Trans_Date',
> MAX(Audit_Number) AS 'Max_Audit_Number',
> Policy_Number
>
> INTO #Max_Trans_Date
>
> FROM  AccountingLog
>
> GROUP BY
> Policy_Number
>
> --************************************************
>
> I am not exactly sure how to update all the transactions with the current
> flag, which in this case is (A).
>
> I have tried to write an UPDATE, but obviously I need the latest
Master_Flag
> first, which I have obtain through the following query:
>
>
> --************************************************
>
> SELECT
> Trans_Date,
> Audit_Number,
> Policy_Number,
> Master_Flag
>
> FROM AccountingLog AS AL
> INNER JOIN #Max_Trans_Date AS MaxDate
> ON AL.Policy_Number = MaxDate.Policy_Number
> AND AL.Audit_Number = MaxDate.Audit_Number
>
>
> This returns the following record:
>
> Trans_Date     Audit_Number         Policy_Number     Master_Flag
> 01/01/2006     3                              CC123                  A
>
> How do I now go update all the transactions for the records in that
policy?
>
> This UPDATE statement, (when working correctly will have to be executed
over
> 600,000 records.
>
> Thank you kindly in advance for all those who have taken the time in
reading
> this long and probably quite trivial posting.
>
> Kind Regards
>
> Ricky
>
> (WIN2K/SQL2K-SP4)
>
>
>

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:u2BSBF2eGHA.3468@TK2MSFTNGP03.phx.gbl...
> Try the following (untested) code...
>
> update AccountingLog
> set Master_Flag =
> (
>    select a.Master_Flag
>    from AccountingLog a
>    where Trans_Date =
>    (
>       select max(A1.Trans_Date)
>       from
>       and A.Policy_Number = A1.Policy_Number
> --      AND A.Audit_Number = A1.Audit_Number
>    )
>    and A.Policy_Number = AccountingLog.Policy_Number
> --   AND A.Audit_Number = AccountingLog.Audit_Number
> )
>
> Note that the Audit_Number is commented out of the criteria.  I assumed
you
> want to update all rows for a given POLICY_NUMBER, regardless of
> Audit_Number.  If you want rows with different Audit_Number to have
seperate
> values for Master_Flag, then uncomment those lines.
>
> At the same time, I think this is all that was wrong with your original
> code, although only a SQL statement is needed for this update.
>
> "ricky" <ri***@ricky.com> wrote in message
> news:eqbj9GyeGHA.4892@TK2MSFTNGP02.phx.gbl...
> > Good Morning
> >
> > I have the following table format, whereby I need to stamp all records
for
> > that policy with the most current transaction's Master_Flag.
> >
> > Audit_Number     Policy_Number     Master_Flag     Trans_Date
> > 1                          CC123                  4
> > 27/12/2005
> > 1                          CC123                  4
> > 27/12/2005
> > 2                          CC123                  4
> > 28/12/2005
> > 2                          CC123                  4
> > 28/12/2005
> > 3                          CC123                  A
> > 01/01/2006
> >
> >
> > I have written a query to obtain the latest transaction date:
> >
> > --************************************************
> >
> > SELECT
> > MAX(Trans_Date) AS 'Max_Trans_Date',
> > MAX(Audit_Number) AS 'Max_Audit_Number',
> > Policy_Number
> >
> > INTO #Max_Trans_Date
> >
> > FROM  AccountingLog
> >
> > GROUP BY
> > Policy_Number
> >
> > --************************************************
> >
> > I am not exactly sure how to update all the transactions with the
current
> > flag, which in this case is (A).
> >
> > I have tried to write an UPDATE, but obviously I need the latest
> Master_Flag
> > first, which I have obtain through the following query:
> >
> >
> > --************************************************
> >
> > SELECT
> > Trans_Date,
> > Audit_Number,
> > Policy_Number,
> > Master_Flag
> >
> > FROM AccountingLog AS AL
> > INNER JOIN #Max_Trans_Date AS MaxDate
> > ON AL.Policy_Number = MaxDate.Policy_Number
> > AND AL.Audit_Number = MaxDate.Audit_Number
> >
> >
> > This returns the following record:
> >
> > Trans_Date     Audit_Number         Policy_Number     Master_Flag
> > 01/01/2006     3                              CC123                  A
> >
> > How do I now go update all the transactions for the records in that
> policy?
> >
> > This UPDATE statement, (when working correctly will have to be executed
> over
> > 600,000 records.
> >
> > Thank you kindly in advance for all those who have taken the time in
> reading
> > this long and probably quite trivial posting.
> >
> > Kind Regards
> >
> > Ricky
> >
> > (WIN2K/SQL2K-SP4)
> >
> >
> >
>
>