lundi 16 mars 2015

Pull ONLY Most Recent CallSwitch Info

2 Tables one with the generic info, the other showing our phone switch information. I want to pull the most recent information based off our call switch. This is sample data for my table structure, how can I ONLY pull the most recent info, even though the callswitch table could possibly contain multiple records for the same day?


Table Structure: Create Table #PhoneSwitch ( PSID int, UserID int, #Dialed varchar(100), LengthofCall int, CallDateTime datetime, CallOutcome varchar(100) ) Create Table #ResourceTable ( RID int, UserID int, AddressMain varchar(100), PhoneNameFirst varchar(100), PhoneNameLast varchar(100), PhoneNameCity varchar(100), PhoneNameState varchar(100), PhoneNameZip varchar(100) )



Insert Into #PhoneSwitch Values ('1', '311', '5555555555', '0', '03/16/2015', 'No Connect'), ('2', '311', '5555555555', '0', '03/16/2015', 'No Connect'),
('3', '311', '5555555555', '0', '03/16/2015', 'No Connect'),('4', '511', '5555555555', '0', '03/15/2015', 'No Connect'), ('5', '511', '5555555555', '0', '03/15/2015', 'No Connect')

Insert Into #ResourceTable Values ('1','311','123 Nowhere Street', 'Z', 'F', 'Montreal', 'CA', '123'),
('2','311','123 Nowhere Street', 'Z', 'F', 'Montreal','CA', '123'), ('3','311','123 Nowhere Street', 'Z', 'F', 'Montreal', 'CA', '123'),
('4','511','623 Nowhere Street', 'A', 'X', 'Montreal', 'CA', '192'), ('5','511','623 Nowhere Street', 'A', 'X', 'Montreal', 'CA', '192')


And this is the query I tried, but it is not returning ONLY the most recent information from the switch



Select case when [UserID] LIKE '311' Then 'Sam Smith' when [UserID] Like '511' Then 'Ricky Zefry' end As [User Name], MAX(PS.CallDateTime) As [Call Date], RT.AddressMain, RT.PhoneNameFirst, RT.PhoneNameLast, RT.PhoneNameCity, RT.PhoneNameState, RT.PhoneNameZip
FROM #ResourceTable RS
Inner Join #PhoneSwitch ps
On RS.UserID = PS.UserID

Aucun commentaire:

Enregistrer un commentaire