Re: Help witth CConverting a Query

From: Bill Lucas <Homebrew42_at_hotmail.ccom>
Date: Thu, 11 Sep 2003 18:53:04 GMT
Message-ID: <kw38b.190$Pd2.191827@news1.news.adelphia.net>

<SNIP>
> Rather than posting TSQL why don't you try describing what you are
> trying to do in plain English.
>
> Because what you seem to be doing is do convoluted I can't imagine what
> the actual purpose is supposed to be.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

Our application is a calibration management system and there is a basic hierarchy in the simplest case.

There is an asset (a piece of regulated equipment) this asset will have different scheduled events associated with it. Such as Calibration Events. As these events become due they need to have a history added for them to indicate that this event was done by when it was due and what the results were.

If a user decides to delete histories for events it may be necessary to roll the due date back. This only occurs when the user deletes the last history for an event.
for example if a user has a thermometer that needs to be calibrated every month on the 10th of the month and they added the event for September 10 our program will roll the due date forward 1 month to Oct. 10th. however if today I came in and deleted the history for Sept. 10th the application needs to roll the due date back to the 10th of September. There is a lot more code that goes on here and I agree it is convoluted but the user can delete any number of histories at any time (URS) and the rollback will have to happen against any that need it.

The update statement I needed help with was used to update old and new information for those items being rolled back for insertion into our audit trail table.

I have since solved this issue (but had to travel on business afterwards so didn't get your reply until now).

The code that works is...

UPDATE tmpRollbackInfo RB
SET (LastActiveDueDate, NewLAD)=
(SELECT H.DueDate, H.HistoryDate
  FROM tblHistories H
WHERE HistoryID = (SELECT HistoryID

                                   FROM (SELECT H2.HistoryID, RB2.RBEventID
                                                FROM tblHistories H2 INNER

JOIN tmpRollbackInfo RB2

ON H2.EventID = RB2.RBEventID

                                   WHERE BITAND(H2.Status,
eSTATUS_DELETED.BitValue) = 0
                                   ORDER BY CASE
                                                        WHEN

BMQR_UTIL.IsDate(H2.DueDate,
BMQR_COMMON.GetDateConvertFormat(recUserDetails.DatasetID)) = 1

                                                        THEN

to_Date(H2.DueDate,
BMQR_COMMON.GetDateConvertFormat(recUserDetails.DatasetID))

                                                        ELSE NULL
                                                        END DESC) DT
                                     WHERE RowNum = 1 AND RB.RBEventID =

DT.RBEventID));

Still convoluted but it is set based and faster than lists or anything else of that nature

If you are interested and wantt to know more contact me off list WCLUCAS AT COOLBLUE DOT COM Thanks for the reply though. Received on Thu Sep 11 2003 - 13:53:04 CDT