Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2006-05-17 : 10:24:31
|
| I have a query for the cancel field calls.(See below), i've got it working but i've one bug left to fix in it, and am wondering if anyone could help me solve it. Definition. I've 2 views, one displays all the cancelled calls, and another displays all the Field calls, they look at the following.Cancelled = 'T'ServiceEvent = 'T' The 2 views are sorted by caseid as they can be put in order... 123, etc... The Serial number must be equal, cancelled calls serialnum = Field calls serialnum and the Cancel Caseid must be > than Field caseid as a cancel must be after as field call, also the StartTime for cancel call must be > than startime for field My query below, gets the MAX field caseid from the field view as their can be many field caseid, where the Serialnum are equal and Cancel Caseid > than Field caseid also StartTime cancel > than startime for field. The bug am getting is that if I've a valid Field call and then another Field call a few days later which is cancelled , my query cancels out both field calls. where i only want it to cancel the last field call, which is the max field caseid. Here is my query.Update dbo.tbl_Voy_RCATransferSet Cancel_Field_Calls = '1' where CASEID IN (SELECT MAX(dbo.vw_voyageur_field_serv_live.CaseID) AS Field_Case_idFROM dbo.vw_voyageur_cancel_serv_live INNER JOIN dbo.vw_voyageur_field_serv_live ON dbo.vw_voyageur_cancel_serv_live.SerialNum = dbo.vw_voyageur_field_serv_live.SerialNum AND dbo.vw_voyageur_cancel_serv_live.StartTime > dbo.vw_voyageur_field_serv_live.StartTime AND dbo.vw_voyageur_cancel_serv_live.CaseID > dbo.vw_voyageur_field_serv_live.CaseID and tbl_Voy_RCATransfer.StartTime >= dbo.udf_date_only(getdate() -1) GROUP BY dbo.vw_voyageur_field_serv_live.StartTime, dbo.vw_voyageur_cancel_serv_live.SerialNum, dbo.vw_voyageur_cancel_serv_live.CaseID, dbo.vw_voyageur_cancel_serv_live.StartTime) |
|
|
|
|
|
|
|
Help with bug in query