I am really new to SQL and am trying to only pull 5 more recent service calls from a table.
Here is my query that works great, but I am getting more calls than I want.
SELECT dbo.SCCalls.CallNumber as 'EA Call Nbr',
dbo.SCCalls.EquipSerialNumber as 'Serial Nbr',
dbo.all_paths_serv_parts.PRODUCT_NUMBER as 'Part Prod Code',
dbo.all_paths_serv_call.CALL_ID as 'OMD Call Nbr',
dbo.ShAgents.PrefFullName as 'Tech Name',
dbo.scdispatches.date as 'EA Dispatch Date',
omd.dbo.all_paths_invt_descript.DESC1_IDE as 'OMD Part Description',
dbo.all_paths_serv_call.SYMPTOM_CODE as 'OMD Symptom Code',
dbo.all_paths_serv_call.CALL_TYPE as 'OMD Call Type',
dbo.all_paths_serv_call.CALL_DATE_SCA as 'OMD Call Date',
dbo.all_paths_serv_call.REPRESENTATIVE as 'OMD Service Tech'
FROM (((((dbo.SCCalls LEFT OUTER JOIN dbo.ShAgents ON dbo.SCCalls.TechnicianID = dbo.ShAgents.AgentID)
LEFT OUTER JOIN dbo.SCDispatches ON dbo.SCCalls.CallID = dbo.SCDispatches.CallID)
LEFT OUTER JOIN dbo.all_paths_serv_call oN dbo.SCCalls.EquipSerialNumber = dbo.all_paths_serv_call.SERIAL)
left OUTER JOIN dbo.all_paths_serv_parts ON dbo.all_paths_serv_call.CALL_ID = dbo.all_paths_serv_parts.CALL_ID)
left outer join omd.dbo.all_paths_invt_descript on dbo.all_paths_serv_parts.PRODUCT_NUMBER = omd.dbo.all_paths_invt_descript.PRODUCT_NUMBER)
WHERE dbo.SCCalls.Status = 'D ' order by dbo.sccalls.CallNumber
This query selects all the calls in dispatch table. For each Call, it looks in the all_paths_serv_call table and selects all the calls in this table that has the same serial number as the call from dispatch. It then links to the parts table and pulls all the parts used on the call. This is all working fine, except when it pulls the calls from serve_call, I only want it to get the most recent 5 calls in the table based on all_paths_serv_call.CALL_DATE_SCA.
Any help would be appreciated. I saw something about using "with rownumbersadded" but wasnt sure where or how to insert it.
btw, I am using SQL Server 2012