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

SQL Multiple Tables with Latest Date and Time Also Needs Nulls

I have 3 Tables with the following charateristics:

Table 1 - Procedures: SourceID (PK), ProcedureID (PK), Field1, Field2....

Table 2 - StdEffDate: SourceID (PK), ProcedureID(PK), EffDateTime(PK), Price

Table 3 - AltEffDate: SourceID (PK), ProcedureID(PK), TypeID(PK), EffDateTime(PK), Code

What I need is a query that pulls some flelds from T1 where Field1 = 'Y', the latest price from T2 based on EffDate, The Latest Code Base on EffDate From T3 where the value is TypeID is ABC and the latest code from T3 where the value of TypeID is XYZ. Populating the fields from T2 or T3
should not limit the records from T1 in other words if I queried T1 by itself with the criteria, adding fields from T2 and T3 should not change the number of records returned. By convention Price is usually not blank but nothing in the design prevents it. On the other hand Code could have values for one, both or neither.

Please let me know if this needs further clarification. I do have a query working that does everything but the last piece:
SELECT A.ProcedureID, A.Description, A.ChargeDeptID, A.ChargeCategoryID, B.Charge, A.RelativeValueUnits, C.Code As CPT, D.Code AS HCPCS
FROM (( DBarProcedures AS A
            INNER JOIN DBarProcStandardEffectDates AS B ON A.SourceID = B.SourceID AND A.ProcedureID = B.ProcedureID)
            LEFT OUTER JOIN DBarProcAltCodeEffectDates AS C ON A.SourceID = C.SourceID AND A.ProcedureID = C.ProcedureID)
            LEFT OUTER JOIN DBarProcAltCodeEffectDates AS D ON A.SourceID = D.SourceID AND A.ProcedureID = D.ProcedureID
WHERE B.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcStandardEffectDates AS C WHERE C.ProcedureID = B.ProcedureID ) AND
        C.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcAltCodeEffectDates AS E WHERE E.ProcedureID = A.ProcedureID ) AND
        D.EffectiveDateTime = ( SELECT MAX(EffectiveDateTime) FROM DBarProcAltCodeEffectDates AS F WHERE F.ProcedureID = A.ProcedureID ) AND
        A.ChargeDeptID = '01.762000' AND C.TypeID = 'CPT-4' AND D.TypeID = 'HCPCS'
ORDER BY A.ProcedureID



 
0
mohrk
Asked:
mohrk
2 Solutions
 
ThomasianCommented:
Try
SELECT A.ProcedureID, A.Description, A.ChargeDeptID, A.ChargeCategoryID, B.Charge, A.RelativeValueUnits, C.Code As CPT, D.Code AS HCPCS
FROM
   DBarProcedures AS A CROSS APPLY
  (SELECT TOP 1 Charge
     FROM DBarProcStandardEffectDates
     WHERE A.SourceID = SourceID AND A.ProcedureID = ProcedureID
     ORDER BY EffectiveDateTime DESC
  ) AS B OUTER APPLY
  (SELECT TOP 1 Code
     FROM DBarProcAltCodeEffectDates 
     WHERE A.SourceID = SourceID AND A.ProcedureID = ProcedureID AND TypeID = 'CPT-4'
     ORDER BY EffectiveDateTime DESC
  ) AS C OUTER APPLY
  (SELECT TOP 1 Code
     FROM DBarProcAltCodeEffectDates 
     WHERE A.SourceID = SourceID AND A.ProcedureID = ProcedureID AND TypeID = 'HCPCS'
     ORDER BY EffectiveDateTime DESC
  ) AS D
WHERE A.ChargeDeptID = '01.762000'
ORDER BY A.ProcedureID

Open in new window

0
 
LowfatspreadCommented:
ok this is how you should write the join
now what is the actual problem and what do you want to happen to the nulls?

(basically you hadn't properly qualified the pk joins as you'd specified them
and by including conditional tests on the left joined table in the where clause
without regard to nulls made it an inner join...
you had also ignored your effectdate condition limiting it to current maximum)


SELECT A.ProcedureID, A.Description, A.ChargeDeptID, A.ChargeCategoryID
      ,B.Charge, A.RelativeValueUnits
      ,C.CPT, C.HCPCS
  FROM DBarProcedures AS A
 INNER JOIN (
              Select *
                from DBarProcStandardEffectDates as x
               where EffectiveDateTime = ( SELECT MAX(EffectiveDateTime)
                                             FROM DBarProcStandardEffectDates as y
                                            WHERE x.ProcedureID = y.ProcedureID
                                              and x.sourceid=y.sourceid
                                              and y.effectivedatetime<=getdate()
                                          )
          ) AS B
    ON A.SourceID = B.SourceID
   AND A.ProcedureID = B.ProcedureID
  LEFT OUTER JOIN
       (select sourceid,procedureid
              ,max(case when typeid='CPT-4' then code else null end) as CPT
              ,max(case when typeid='HCPCS' then code else null end) as HCPCS
          from
        (select sourceid,procedureid,typeid,code
          from DBarProcAltCodeEffectDates as y
         where typeid in ('CPT-4','HCPCS')
           and effectivedatetime
                ( SELECT MAX(EffectiveDateTime)
                    FROM DBarProcAltCodeEffectDates AS x
                   WHERE y.ProcedureID = x.ProcedureID
                     and x.effectivedatetime <= getdate()
                     and y.sourceid=x.sourceid
                     and y.typeif=x.typeid)
         )
        group by sourceid,procedureid    
       ) as C
    ON A.SourceID = C.SourceID
   AND A.ProcedureID = C.ProcedureID

WHERE A.ChargeDeptID = '01.762000'
ORDER BY A.ProcedureID
0
 
mohrkAuthor Commented:
LowFatSpread,
There is an syntax error in this  and I cannot find it, based on your response
 

SELECT A.ProcedureID, A.Description, A.ChargeDeptID, A.ChargeCategoryID, B.Charge, A.RelativeValueUnits, C.CPT, C.HCPCS
FROM DBarProcedures AS A
INNER JOIN(
                  SELECT * 
                  FROM DBarProcStandardEffectDates AS X
                  WHERE EffectiveDateTime = ( SELECT MAX(Y.EffectiveDateTime)
                                                            FROM DBarProcStandardEffectDates AS Y
                                                            WHERE X.ProcedureID = Y.ProcedureID
                                                            AND X.XOurceID = Y.SourceID
                                                            AND Y.EffectiveDateTime <= GetDate()
                                                         )
               ) AS B
ON A.SourceID = B.SourceID AND
   A.ProcedureID = B.ProcedureID
LEFT OUTER JOIN (
                          SELECT SourceID, ProcedureID,
                          MAX(CASE WHEN TypeID = 'CPT-4' THEN Code ELSE Null END) AS CPT,
                          MAX(CASE WHEN TypeID = 'HCPCS' THEN Code ELSE Null END) AS HCPCS
                          FROM (
                                     SELECT SourceID, ProcedureID, TypeID, Code
                                     FROM DBarProcAltCodeEffectDates AS Y
                                     WHERE TypeID IN ('CPT-4','HCPCS')
                                     AND EffectiveDateTime = (
                                                                          SELECT MAX(EffectiveDateTime)
                                                                          FROM DBarProcAltCodeEffectDates AS X
                                                                          WHERE Y.ProcedureID = X.ProcedureID AND
                                                                                 X.EffectiveDateTime <= GetDate() AND
                                                                                 Y.SourceID = X.SourceID AND
                                                                                 Y.TypeID = X.TypeID
                                                                          )
                                    )
                        ) AS C
ON A.SourceID = C.SourceID AND
   A.ProcedureID = C.ProcedureID
WHERE A.ChargeDeptID = '01.762000'
ORDER BY A.ProcedureID

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
What is the error message? I did not see any error in the code.
0
 
mohrkAuthor Commented:
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ')'.

Originally there was an ORDER BY clause right before that line which caused a similar syntax error...
0
 
LowfatspreadCommented:
missing alias on inline table

     )
        group by sourceid,procedureid    
       ) as C


should be

     ) as XX
        group by sourceid,procedureid    
       ) as C
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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