How do I need to modifiy this query:
To change this result:
To just get the row with Q1_2009 as the Quarter (the earliest Quarter)?
Code:
SELECT DISTINCT t3.Program, t3.State, t3.MCO, t3.NDC11, t3.Quarter, t3.Units as OrigUnits
FROM (UpdateHistory t3
LEFT JOIN
(SELECT DISTINCT MIN(t1.Quarter) AS Quarter, t1.Program, t1.State, t1.MCO, t1.NDC11
FROM (UpdateHistory t1
LEFT JOIN (SELECT DISTINCT MIN(RIGHT(Quarter,4)), Program, State, MCO, NDC11
FROM UpdateHistory
GROUP BY Program, State, MCO, NDC11) t2
ON t2.Program=t1.Program AND t2.State=t1.State AND t2.MCO=t1.MCO AND t2.NDC11=t1.NDC11)
WHERE t1.Source='iMANY'
GROUP BY t1.Program, t1.State, t1.MCO, t1.NDC11) t4
ON t4.Program = t3.Program AND t4.State = t3.State AND t4.MCO = t3.MCO AND t4.NDC11 = t3.NDC11)
WHERE t3.EntryDate = 'Q1_2011'
Code:
Program State MCO NDC11 Quarter OrigUnits
MEDI KY False 59762005701 Q1_2009 539.00
MEDI KY False 59762005701 Q2_2009 716.00
MEDI KY False 59762005701 Q3_2009 892.00
To just get the row with Q1_2009 as the Quarter (the earliest Quarter)?