Subquery or a MAX or Select Top 1 ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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-06-28 : 21:41:10

I need the last (most recent) result set from the following field:

edir.created

Not sure if I will need a subquery or not?!

Not sure if this will be an issue either, but on one ticket, it listed 4 entries for 2007-02-22. I would like to see those 4. If its possible to just look at the Month Day and Year and ignore the time, that would be PERFECT!

2007-02-22 10:18:56.000
2007-02-22 10:18:56.000
2007-02-22 09:34:17.000
2007-02-22 09:34:17.000
2007-02-16 13:41:19.000
2007-02-16 13:41:19.000

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.created,
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)
)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-29 : 05:33:09

Try replacing

LEFT JOIN EDIReport edir ON pv.PatientVisitId = edir.PatientVisitId AND edir.Created > pv.LastFiledDate

with something like

LEFT JOIN
    (
        (    SELECT edir1.PatientVisitId
                ,DATEADD(day, 0, DATEDIFF(day, 0, MAX(edir1.created))) AS Created
            FROM EDIReport edir1
            GROUP BY edir1.PatientVisitId
        ) D
        JOIN EDIReport edir
            ON D.PatientVisitId = edir.PatientVisitId
                AND edir.Created >= D.Created
    ) ON pv.PatientVisitId = edir.PatientVisitId
        AND edir.Created > pv.LastFiledDate

Also, some of the filters at the end of the query look a bit convoluted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-29 : 05:47:23

SELECT TOP 4 <col list here>
from <your query here>
order by <your datetime column here> desc

Peter Larsson
Helsingborg, Sweden

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-06-30 : 22:36:10

Ifor,

Thank you for your help! I am now seeing another issue with this report now... when I ran this in query analyzer, it returned 5 result sets for my one specific ticket I ran this against (I had in my where clause where pv.ticketnumber = '000946') ... removed in the below as it shouldnt matter in the grand picture.

The reason my report returned 5 rows was due to the claim being transmitted 5 times (edicf.FileTransmitted,). So now I need only the last edicf.FileTransmitted in addition.

Main Query as Follows:

/* EDI Claim Filed Rejected */

SET NOCOUNT ON

SELECT pp.Last + ', ' + pp.First as PatientName,
pp.PatientId,
pv.TicketNumber,
edir.created,
edir.Description,
pv.Visit,
edic.Charges,
edicf.FileTransmitted,
edicf.SubmissionNumber,
ic.ListName AS CarrierName,
ic.InsuranceCarriersId,
ic.ECSPayorId,
cl.ClearingHouseName AS Clearinghouse,
cl.ClearinghouseId,
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
/*Subquery to pull last created JAS 06/28/07*/
LEFT JOIN((SELECT edir1.PatientVisitId,DATEADD(day, 0, DATEDIFF(day, 0, MAX(edir1.created))) AS Created
FROM EDIReport edir1 GROUP BY edir1.PatientVisitId) D
JOIN EDIReport edir ON D.PatientVisitId = edir.PatientVisitId AND edir.Created >= D.Created)
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)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources