Aggregate CTE and Update Query

Question

I have studied a number of aggregate and CTE post but have not found a solution for updating a minumum value from a CTE to an additional (non-CTE) outside table. This is using SQL 2008 (Microsoft managment studio). I am combining dates from an appointment table, dates for services from a billing table, and then cross checking those with a field in a patient table. If that field called New Patient Date is blank, I want to update it with the earliest date from the other two tables. They all joined on a common ID called chart or chart number. Here is the code...Any insights?

WITH New_Patients (chart_nbr, First_Visit)
  AS
  ( SELECT A.[Chart], MIN(A.[Appt Date])
      FROM Appointment AS A, Patient AS P
      WHERE A.[Chart] = P.[Chart Number]
        AND A.[Appt Date] IS NOT NULL
      GROUP BY A.[Chart]

   UNION

   SELECT BD.[Chart], MIN(BD.[Service Date 1])
    FROM [Billing Detail] AS BD, Patient AS P 
    WHERE BD.[Chart] = P.[Chart Number]
      AND [Transaction Code] IN 
         ('99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', 
         '99214', '99215', '99241', '99242', '99243', '99244', '97001', '97002')
      AND BD.[Service Date 1] IS NOT NULL
    GROUP BY BD.[Chart]

   UNION

   SELECT P.[Chart Number], P.[New Patient Date]
     FROM Patient AS P
     GROUP BY P.[New Patient Date], P.[Chart Number]  )

UPDATE [Patient]    
SET Patient.[New Patient Date]=MIN(First_Visit)
FROM Patient AS P, New_Patients AS NP
WHERE P.[Chart Number] = NP.chart_nbr
    AND P.[New Patient Date]IS NULL

score 0 · Answer 1 · answered Oct 24 '12 at 14:45

Cheers, if my understanding is correct, I would solve the issue as follows:

   WITH New_Patients (chart_nbr, First_Visit)
  AS
  ( SELECT A.[Chart] as chart_nbr, MIN(A.[Appt Date]) as First_Visit
      FROM Appointment AS A, Patient AS P
      WHERE A.[Chart] = P.[Chart Number]
        AND A.[Appt Date] IS NOT NULL
      GROUP BY A.[Chart]
   UNION
   SELECT BD.[Chart], MIN(BD.[Service Date 1])
    FROM [Billing Detail] AS BD, Patient AS P 
    WHERE BD.[Chart] = P.[Chart Number]
      AND [Transaction Code] IN 
         ('99201', '99202', '99203', '99204', '99205', '99211', '99212', '99213', 
         '99214', '99215', '99241', '99242', '99243', '99244', '97001', '97002')
      AND BD.[Service Date 1] IS NOT NULL
    GROUP BY BD.[Chart]
   UNION
   SELECT P.[Chart Number], P.[New Patient Date]
     FROM Patient AS P
     GROUP BY P.[New Patient Date], P.[Chart Number]  
 )
UPDATE P SET P.[New Patient Date] = ISNULL(P.[New Patient Date], MIN(NP_group.First_Visit))
from [Patient] P 
left join 
(   
    select min[First_date) as First_Visit, chart_nbr
        from New_Patients 
            group by chart_nbr

) NP_group ON P.[Chart Number] = NP_group.chart_nbr

WITH part remained the same, UPDATE part is slightly modified. In subquery you get minimum date across CTE table, then update where is applicable (ISNULL conditioning).

Aggregate CTE and Update Query

1 Answers1