• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

SQL Query Assistance

I am not sure how to get the value I want. What I need is just the most recent date for my distinct PatientProfileId and not a record for each Date of Service. For example, Patient John Doe has surgery on 05/02/2011 and re-admitted and has a second surgery on 05/09/2011, I would only want back that patients most recent service date and not all as this one is giving me now.
SELECT  'PatientName' = dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        'PatientId' = pp.PatientId,
        'DateOfServiceFrom' = MAX(pvp.DateOfServiceFrom),
        'GlobalStartDate' = pvp.DateOfServiceFrom,
        'GlobalEndDate' = DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom),
        'GlobalLength' = pvp.GPDays
FROM    PatientVisitProcs pvp
        JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE   ISNULL(GPDays, 0) > 0
        AND DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom) > GETDATE()
        AND ISNULL(pvp.Voided, 0) = 0
GROUP BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        pp.PatientId,
        pvp.DateOfServiceFrom,
        pvp.GPDays
ORDER BY GlobalEndDate DESC

Open in new window

0
Jeff S
Asked:
Jeff S
3 Solutions
 
GSGDBACommented:
Add a column lastrecentupdatedate.
Select the records based on lastrecentupdatedate.
0
 
knightEknightCommented:

SELECT  'PatientName' = dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        'PatientId' = pp.PatientId,
        'DateOfServiceFrom' = MAX(pvp.DateOfServiceFrom),
        'GlobalStartDate' = pvp.DateOfServiceFrom,
        'GlobalEndDate' = DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom),
        'GlobalLength' = pvp.GPDays
FROM    PatientVisitProcs pvp
        JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        JOIN (select PatientProfileId, MAX(DateOfServiceFrom) as DateOfServiceFrom 
              from PatientVisitProcs 
              group by PatientProfileId ) mx
          on mx.PatientProfileId = pp.PatientProfileId
         and mx.DateOfServiceFrom = pvp.DateOfServiceFrom
WHERE   ISNULL(GPDays, 0) > 0
        AND DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom) > GETDATE()
        AND ISNULL(pvp.Voided, 0) = 0
GROUP BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        pp.PatientId,
        pvp.DateOfServiceFrom,
        pvp.GPDays
ORDER BY GlobalEndDate DESC

Open in new window

0
 
jcott28Commented:
The above suggestion is a good one.  If you don't have the ability to mod the database, then you could do your exact query, which returns all the dates.  Then wrap it into a subquery where you'd just grab the record with the max date from the initial query.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GSGDBACommented:
Add new database role as NestRole
0
 
wdosanjosCommented:
Please try the folllowing:

SELECT  TOP 1
        'PatientName' = dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix),
        'PatientId' = pp.PatientId,
        'DateOfServiceFrom' = pvp.DateOfServiceFrom,
        'GlobalStartDate' = pvp.DateOfServiceFrom,
        'GlobalEndDate' = DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom),
        'GlobalLength' = pvp.GPDays
FROM    PatientVisitProcs pvp
        JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE   ISNULL(GPDays, 0) > 0
        AND DATEADD(day, pvp.GPDays, pvp.DateOfServiceFrom) > GETDATE()
        AND ISNULL(pvp.Voided, 0) = 0
ORDER BY GlobalEndDate DESC

Open in new window

0
 
GSGDBACommented:
Sorry, the last comment is not for your question.
0
 
8080_DiverCommented:
Try the following:
SELECT 'PatientName' = dbo.FormatName(pp.Prefix,pp.First,pp.Middle,pp.Last,pp.Suffix)
  ,'PatientId' = pp.PatientId
  ,MAXDateOfServiceFrom
  ,'GlobalStartDate' = pvp.DateOfServiceFrom
  ,'GlobalEndDate' = DATEADD(DAY,pvp.GPDays,pvp.DateOfServiceFrom)
  ,'GlobalLength' = pvp.GPDays
FROM PatientVisitProcs pvp
JOIN PatientVisit pv
ON  pvp.PatientVisitId = pv.PatientVisitId
JOIN PatientProfile pp
ON  pv.PatientProfileId = pp.PatientProfileId
JOIN
(
SELECT  'PatientId' = pp.PatientId
       ,'MAXDateOfServiceFrom' = MAX(pvp.DateOfServiceFrom)
FROM PatientVisitProcs pvp
JOIN PatientVisit pv
ON  pvp.PatientVisitId = pv.PatientVisitId
JOIN PatientProfile pp
ON  pv.PatientProfileId = pp.PatientProfileId
GROUP BY pp.PatientId
) Z
ON Z.PatientID = pp.PatientId  AND
   Z.MAXDateOfServiceFrom = pvp.DateOfServiceFrom  
WHERE ISNULL(GPDays,0) > 0
    AND DATEADD(DAY,pvp.GPDays,pvp.DateOfServiceFrom) > GETDATE()
    AND ISNULL(pvp.Voided,0) = 0
GROUP BY dbo.FormatName(pp.Prefix,pp.First,pp.Middle,pp.Last,pp.Suffix)
  ,pp.PatientId
  ,pvp.DateOfServiceFrom
  ,pvp.GPDays
ORDER BY GlobalEndDate DESC;

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Select MAx(AdmitDate) from Patient Group By PatientID
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now