Subquery or a MAX or Select Top 1

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)

Subquery or a MAX or Select Top 1

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-05-18 : 20:19:33

Not sure whats needed here a Subquery or a MAX or Select Top 1 ....

What I need is the last row based off the edicf.FileTransmitted, column. For example, I have three rows for the same ticket number --- each with its own edicf.FileTransmitted date. I need it to pull only the last/most recent one.

My Query

/* EDI Claim Filed Rejected */
SET NOCOUNT ON
SELECT pp.Last + ', ' + pp.First as PatientName,
pp.PatientId,
pv.TicketNumber,
pv.Visit,
edic.Charges,
edicf.FileTransmitted,
edicf.SubmissionNumber,
ic.ListName AS CarrierName,
ic.InsuranceCarriersId,
ic.ECSPayorId,
cl.ClearingHouseName AS Clearinghouse,
cl.ClearinghouseId,
edir.Description,
edirf.Name AS ReportFile,
ISNULL(ig.Name,'None') AS InsuranceGroup,
ISNULL(ic.InsuranceGroupId, 0) AS InsuranceGroupId,
Flag=convert(varchar(50),'Clearinghouse')

FROM PatientVisit pv
JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
JOIN EDIClaimFile edicf ON edic.EDIClaimFileId = edicf.EDIClaimFileId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
JOIN InsuranceCarriers ic ON edic.InsuranceCarriersId = ic.InsuranceCarriersId
JOIN Clearinghouse cl ON edicf.ClearinghouseId = cl.ClearinghouseId
LEFT JOIN EDIReport edir ON pv.PatientVisitId = edir.PatientVisitId AND edir.Created > pv.LastFiledDate
LEFT JOIN EDIReportFile edirf ON edir.EDIReportFileId = edirf.EDIReportFileId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId

WHERE edicf.TransmissionStatus = 'Transmitted' AND
pv.BillStatus = 8 and
edicf.filetransmitted >= ISNULL(NULL,'1/1/1900') and
edicf.filetransmitted < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance group
(
(NULL IS NOT NULL AND ig.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-21 : 02:39:25

Check the following one by changing the join on EDIClaimFile

SELECT <columnlist>
FROM PatientVisit pv
JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
JOIN
(
SELECT SubmissionNumber, EDIClaimFileId, MAX(FileTransmitted) AS filetransmitted
FROM EDIClaimFile
GROUP BY SubmissionNumber, EDIClaimFileId
)
edicf ON edic.EDIClaimFileId = edicf.EDIClaimFileId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
JOIN InsuranceCarriers ic ON edic.InsuranceCarriersId = ic.InsuranceCarriersId
JOIN Clearinghouse cl ON edicf.ClearinghouseId = cl.ClearinghouseId
LEFT JOIN EDIReport edir ON pv.PatientVisitId = edir.PatientVisitId AND edir.Created > pv.LastFiledDate
LEFT JOIN EDIReportFile edirf ON edir.EDIReportFileId = edirf.EDIReportFileId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
WHERE <condition>

Peter

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-05-21 : 13:05:30

I ran your suggestion and got this:

Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'edicf'.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 13:07:56

s.b.
(
SELECT SubmissionNumber, EDIClaimFileId, MAX(FileTransmitted) AS filetransmitted
FROM EDIClaimFile
GROUP BY SubmissionNumber, EDIClaimFileId
) edicf

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-05-21 : 13:17:06

Maybe Im missing something --- now its giving me:

Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'EDIClaimFile'.

FROM PatientVisit pv
JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
JOIN
(
SELECT SubmissionNumber, EDIClaimFileId, MAX(FileTransmitted) AS filetransmitted
FROM EDIClaimFile
GROUP BY SubmissionNumber, EDIClaimFileId
) edicf
edicf ON edic.EDIClaimFileId = edicf.EDIClaimFileId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
JOIN InsuranceCarriers ic ON edic.InsuranceCarriersId = ic.InsuranceCarriersId
JOIN Clearinghouse cl ON edicf.ClearinghouseId = cl.ClearinghouseId
LEFT JOIN EDIReport edir ON pv.PatientVisitId = edir.PatientVisitId AND edir.Created > pv.LastFiledDate
LEFT JOIN EDIReportFile edirf ON edir.EDIReportFileId = edirf.EDIReportFileId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 13:27:11

oops
edicf is on the following line.
This sort of thing is usually a missing comma or bracket.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 13:30:16

This is sql server isn't it?

try
select 1
FROM PatientVisit pv
JOIN EDIClaim edic ON pv.PatientVisitId = edic.PatientVisitId
JOIN
(
SELECT SubmissionNumber, EDIClaimFileId, MAX(FileTransmitted) AS filetransmitted
FROM EDIClaimFile
GROUP BY SubmissionNumber, EDIClaimFileId
) edicf
ON edic.EDIClaimFileId = edicf.EDIClaimFileId
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
JOIN InsuranceCarriers ic ON edic.InsuranceCarriersId = ic.InsuranceCarriersId
JOIN Clearinghouse cl ON edicf.ClearinghouseId = cl.ClearinghouseId
LEFT JOIN EDIReport edir ON pv.PatientVisitId = edir.PatientVisitId AND edir.Created > pv.LastFiledDate
LEFT JOIN EDIReportFile edirf ON edir.EDIReportFileId = edirf.EDIReportFileId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
where 1=0

not too sure this will work - you have submissionnumber in the group by - is that what you want?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-05-21 : 13:48:33

What I need is the last row based off the edicf.FileTransmitted, column. For example, I have three rows for the same distinct ticket number --- each with its own edicf.FileTransmitted date. I need it to pull only the last/most recent one.

I dont want the two earlier ones, just the last/most recent one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources