SQL Question

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
How do I need to modifiy this query:
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'
To change this result:

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)?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This is the criteria row...
Code:
WHERE t3.EntryDate = 'Q1_2011'

Change that to
Code:
WHERE t3.Quarter = 'Q1_2009'

Denis
 
Upvote 0
I guess I wasn't clear. Those aren't the only 3 rows in the recordset, they were just meant to be an example. I need to pull only the rows that represent the earliest (by calendar quarter) appearance of the grouped columns. Right now I'm getting all the rows instead of just the ones with the earliest quarter. That quarter won't always be Q1_2009, it might be Q2_2008, Q4_2010, etc.
 
Upvote 0
Why are you joining the same table with itself twice?

Try a using a sub query that returns the earliest quarter for whatever it is you are grouping.
 
Upvote 0
Possibly you can query your query - first group on all the quarters, then take the quarter of choice. If you need the lowest quarter you'll have to separate out the years and quarters.

FWIW, the basic lesson is this: use quarter abbreviations with year first. It should be 2009-Q1 rather than Q1-2009. The reason is now obvious.
 
Upvote 0
This is what I meant about using a subquery.

SELECT DISTINCT Program, State, MCO, NDC11, Quarter, OrigUnits
FROM UpdateHistory
WHERE Quarter =
(SELECT MIN(Quarter)
FROM UpdateHistory T
WHERE T.NDC11 = NDC11)
 
Upvote 0
xenou - the format of the Quarter abbreviation isn't up to me, if it were I would definitely have put the year first.

Norie - the problem is that the MIN(Quarter) isn't always the same for all Program/State/NDC11 groups. For example, it might be Q1_2008 for MEDI/AK/00012345678 and Q2_2009 for AZACAP/AZ/98761112345. For that reason I can't just get the MIN(Quarter) on a single field.

Thanks for your suggestions, I'll keep at it!
 
Upvote 0
Jeff

The same min quarter won't get returned for each group

Look at the WHERE clause in the subquery.

I've only used the NDC11 field but if you need the other fields too add them in.

WHERE NCD11=T.NDC11 AND PROGRAM=T.PROGRAM AND STATE=T.STATE


PS If you reverse the year and quarter you will get the earliest quarter using a single field.
 
Upvote 0
Might need to run a query separately to get your min, so you can plug it in. Don't know. I'd probably normalize this into real dates or sortable values (you can transform your data even if you can't change the boneheaded way it is given to you - but since quarters come from dates which come from numbers somewhere you do have real sortable data - if you want good reports you really have to start with good data and it's reasonable to ask for that).

This pulls the quarters apart and puts them back together as the minimum quarter. Not very good but shows it can be done (My data table in this case was "Table2").

SELECT "Q" & Right(MIN(FiscalQuarter),1) & "-" & LEFT(MIN(FiscalQuarter),4) As MinOfQuarter
FROM
(SELECT Right(t.[Qrtr],4) & "Q" & Mid(t.[Qrtr],2,1) AS FiscalQuarter
FROM Table2 As t)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,858
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top