Help needed with Query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed with Query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-09 : 10:29:33

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 filed 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 ))

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-05-10 : 08:57:45

Wouldn't the CASEID's be the same if it is all stored in 1 record, or do you insert every new change into a new record?

If it's in one record, this will do:


Update dbo.tbl_Voy_RCATransfer
Set Cancel_Field_Calls = '1' 
where CASEID IN (Select CASEID 
From dbo.tbl_Voy_RCATransfer 
where ServiceEvent = 't' and Cancelled = 't')

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-10 : 10:38:46

The Case id is unique and its a new record each time so for a Send to the field you have one row and then for a cancel you'll have another row. The serial number is the only field that is the same.

skativarapu
Starting Member

13 Posts

Posted - 2006-05-10 : 16:57:14

I think you have to break the requitment into parts
first cerate a temp table which holds the details of the changes and then run the update using a join this should solve the prob and also reduse the time drastically

vasu

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-11 : 08:05:11

I broke it into parts but i did it in views, first i got all my cancel calls then all my field calls and then i applied my query below, and it works..

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
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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources