May 9, 2006 at 9:14 am
I Need to write a query to capture if a call was sent to the field and then cancelled, what defines if a call was sent to the filed is Service Event = 'T', what defines if a call was cancelled is Cancelled = 'T' and if it has the same serial number as the call that was sent to the field and the Caseid is greather than the send to the field calls case id.
I've one table that holds all this information the unique id is the case id and its in order so the field case id will always be before the cancel case id, my bug at the moment is that am cancelling all Service Event Calls and not just the one that the cancel is related to, also i need to make sure if i've more than one cencel call then the 2 field call will be cancel for it,
here is my query its only a example and not correct
Update dbo.tbl_Voy_RCATransfer
Set Cancel_Field_Calls = '1'
where CASEID IN (Select dbo.tbl_Voy_RCATransfer.CASEID
From dbo.tbl_Voy_RCATransfer inner join dbo.tbl_Voy_RCATransfer tbl_Voy_RCATransfer_1
on dbo.tbl_Voy_RCATransfer.SerialNum = tbl_Voy_RCATransfer_1.SerialNum
and dbo.tbl_Voy_RCATransfer.CountryCD3 = tbl_Voy_RCATransfer_1.CountryCD3
and (dbo.tbl_Voy_RCATransfer.ServiceEvent = 't') and (tbl_Voy_RCATransfer_1.Cancelled = 't')
and (dbo.tbl_Voy_RCATransfer.CASEID) < (tbl_Voy_RCATransfer_1.CASEID ))
May 9, 2006 at 11:08 am
Can you provide a sample of the data and how you would like the results to look based on the sample data?
-SQLBill
May 10, 2006 at 12:30 am
Try:
UPDATE dbo.tbl_Voy_RCATransfer SET dbo.tbl_Voy_RCATransfer.Cancel_Field_Calls = '1'
FROM dbo.tbl_Voy_RCATransfer
INNER JOIN (SELECT dbo.tbl_Voy_RCATransfer.CASEID
FROM dbo.tbl_Voy_RCATransfer
INNER JOIN dbo.tbl_Voy_RCATransfer AS tbl_Voy_RCATransfer_1
ON dbo.tbl_Voy_RCATransfer.SerialNum = tbl_Voy_RCATransfer_1.SerialNum
AND dbo.tbl_Voy_RCATransfer.CountryCD3 = tbl_Voy_RCATransfer_1.CountryCD3
AND dbo.tbl_Voy_RCATransfer.ServiceEvent = tbl_Voy_RCATransfer_1.Cancelled
AND tbl_Voy_RCATransfer_1.Cancelled = 't'
AND dbo.tbl_Voy_RCATransfer.CASEID < tbl_Voy_RCATransfer_1.CASEID) AS A
ON dbo.tbl_Voy_RCATransfer.CASEID = A.CASEID
I suggest testing the "() AS A" portion as a SELECT query to insure that it returns the expected CASEID for the Rows you want to Update, then replace the above with the tested query.
Andy
May 19, 2006 at 4:42 am
I've got this far but i still have one issues with it... read on..
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_RCATransfer
Set Cancel_Field_Calls = '1'
where CASEID IN (SELECT MAX(dbo.vw_voyageur_field_serv_live.CaseID) AS Field_Case_id
FROM 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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply