I am having a hard time battling duplicate entries when creating a view. I need a combination of fields from both tables to show in this field, however each incident has multiple radiolog entries. Therefore the join creates a row for every time the incident is referenced in the radiolog table. Here is the breakdown.
Table 1(Incidents)
callid
respoff
number
date
nature
agency(used for filtering)
Table 2(radiolog)
callid
xpos
ypos
logdate
I need the results to show where the callid matched and am using it for a specific incident. Here is what I have:
SELECT dbo.lwmain.respoff,
dbo.lwmain.number,
dbo.lwmain.dtrepor,
dbo.rlmain.xpos,
dbo.rlmain.ypos,
dbo.lwmain.nature,
dbo.rlmain.logdate
FROM dbo.lwmain LEFT OUTER JOIN
dbo.rlmain ON dbo.lwmain.callid = dbo.rlmain.callid
WHERE (dbo.lwmain.agency = 'P16')
AND (dbo.lwmain.dtrepor > '2022-01-01 00:00:00.0000000')
Edit with photo of example data: Example data
Given the additional requirement that you only want the latest rlmain record for each call, the following should work:
SELECT LW.respoff,
LW.number,
LW.dtrepor,
RL_LAST.xpos,
RL_LAST.ypos,
LW.nature,
RL_LAST.logdate
FROM dbo.lwmain LW
OUTER APPLY (
SELECT TOP 1 RL.*
FROM dbo.rlmain RL
WHERE RL.callid = LW.callid
ORDER BY RL.logdate DESC
) RL_LAST
WHERE (LW.agency = 'P16')
AND (LW.dtrepor > '2022-01-01 00:00:00.0000000')
Side note: Be careful of edge conditions. Although the likelihood of an exact '2022-01-01 00:00:00.0000000' dtrepor date may be small, it will be excluded by the WHERE condition above.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments