# Miscellaneous > Structured Query Language (SQL) >  SQL Coding- adding a new variable to an existing query

## sqlbeginner25

I have an existing query that works really well- it takes a set of claims and creates a minimum start and max end date for a given member with a given provider, along with a sum of paid- it strings along for hospital stays where there are interim bills. I need to add in adjudication date- I need the min adjudication date for each stay. I tried adding it in and it isn't working. Can anyone help me? Both queries are below. thank you!

*1st query- works- no adjudication date*
 Create Table IPClaimsGrouped2 as
 (SELECT Member_ID , MedID
      , Start_Date   ,End_Date, 
      (SELECT sum(r2.paid)  
          FROM  IPClaims2 AS r2
          WHERE r2.Member_ID = r1.Member_ID
            AND r2.MedID        = r1.MedID
            AND r2.Start_Date  >= r1.Start_Date
            AND r2.End_Date    <= r1.End_Date
        ) AS sum_paid
  FROM  (SELECT Member_ID , MedID
              , Start_Date
              , (SELECT MIN(t3.End_Date) 
                  FROM  IPClaims2 AS t3
                  WHERE NOT EXISTS
                        (SELECT *
                          FROM  IPClaims2 AS t4
                          WHERE t4.Member_ID = t3.Member_ID
                            AND t4.MedID        = t3.MedID
                            AND t4.Start_Date   = t3.End_Date + interval 1 day 
                        )
                    AND t3.Member_ID = t1.Member_ID
                    AND t3.MedID        = t1.MedID
                    AND t3.End_Date    >= t1.Start_Date
                ) as end_date
          FROM  IPClaims2 AS t1
          WHERE NOT EXISTS
                (SELECT *
                  FROM  IPClaims2 AS t2
                  WHERE t2.Member_ID = t1.Member_ID
                    AND t2.MedID        = t1.MedId
                    AND t2.End_Date     = t1.Start_Date - interval 1 day
                )
        ) AS r1
  ORDER BY
        Member_ID , MedID
      , Start_Date);

*2nd Query- adding in adjudication date- broken*
Create Table IPClaimsGrouped2 as
 (SELECT Member_ID , MedID
      , Start_Date   ,End_Date, min(Adj_Date) as min_adj_date,
      (SELECT sum(r2.paid)  
          FROM  IPClaims2 AS r2
          WHERE r2.Member_ID = r1.Member_ID
            AND r2.MedID        = r1.MedID
            AND r2.Start_Date  >= r1.Start_Date
            AND r2.End_Date    <= r1.End_Date
        ) AS sum_paid
  FROM  (SELECT Member_ID , MedID
              , Start_Date, min(Adj_Date) as Adj_Date
              , (SELECT MIN(t3.End_Date) 
                  FROM  IPClaims2 AS t3
                  WHERE NOT EXISTS
                        (SELECT *
                          FROM  IPClaims2 AS t4
                          WHERE t4.Member_ID = t3.Member_ID
                            AND t4.MedID        = t3.MedID
                            AND t4.Start_Date   = t3.End_Date + interval 1 day 
                        )
                    AND t3.Member_ID = t1.Member_ID
                    AND t3.MedID        = t1.MedID
                    AND t3.End_Date    >= t1.Start_Date
                ) as end_date
          FROM  IPClaims2 AS t1
          WHERE NOT EXISTS
                (SELECT *
                  FROM  IPClaims2 AS t2
                  WHERE t2.Member_ID = t1.Member_ID
                    AND t2.MedID        = t1.MedId
                    AND t2.End_Date     = t1.Start_Date - interval 1 day
                )
        ) AS r1
  ORDER BY

----------

