We help IT Professionals succeed at work.

SQL - Select MAX ( Date ) / Group By

SQL 2005 / 2008

From the below query, I am trying to get MAX ( Rx.FillDate ) for each PLN.PlanCodeKey.

Unfortunately my query returns all Rx.FillDate available for each Pln.PlanCodeKey.

How to fix this issue ? I used MAX Keyword and Group by Clause.
SELECT		PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,
			MAX(Case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT(Rx.FillDate,4) as DATE))else null end) as [Fill Date],
			Rx.PaidByPlan1,Rx.Plan1StoreId,Rx.Plan1Key
FROM		PLANS PLN
INNER JOIN	Rx Rx
ON			PLN.PlanCodeKey = Rx.Plan1Key
Group By    PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,Rx.PaidByPlan1,Rx.Plan1StoreId,Rx.Plan1Key
order by PLN.PlanCodeKey ASC

Open in new window

Comment
Watch Question

If you do not want to group by Pln.PlanCodeKey, you should either remove it from the query or aggregate that value too.

Greg

 
chokkaStudent

Author

Commented:
MAX Keyword requires Group By.

I want to explain For which Group, i am looking for Max Fill Date.

So, in my requirement, For each Pln.PlanCodeKey - I am looking MAX(Rx.FillDate)
I'm not sure if I understand completely, but you could just use MAX(Rx.FillDate) like this.

Greg


SELECT PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,
       MAX(Rx.FillDate) as [Fill Date],
       Rx.PaidByPlan1,Rx.Plan1StoreId,Rx.Plan1Key
FROM            PLANS PLN
INNER JOIN      Rx Rx ON PLN.PlanCodeKey = Rx.Plan1Key
Group By PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,Rx.PaidByPlan1,Rx.Plan1StoreId,Rx.Plan1Key
order by PLN.PlanCodeKey ASC

Open in new window

chokkaStudent

Author

Commented:
@Greg, Your query results the same as my query.

Let me break it down by an example.

Pln.PlanCodeKey        Rx.FillDate

1                                 01/01/2010
1                                  01/01/2011
2                                02/01/2010
2                                 02/01/2011

Output would be .. only the Maximum Fill Date associated with each PlanCodeKey.
You can see the output reflecting as only two rows by picking MAX Fill Date.

Pln.PlanCodeKey        Rx.FillDate

1                                  01/01/2011
2                                  02/01/2011
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you are grouping my more fields than just PlanCodeKey, hence you get more rows per PlanCodeKey.
remove the other fields from the GROUP BY, this should solve the issue.

if you want the max(date) really just by that field, but keep the group by, you must then solve that by putting a subquery.

for the rest, you might want to read this article:
http://www.experts-exchange.com/A_3203.html
chokkaStudent

Author

Commented:

I haven't started writing Sub Query, I will do that !!

Meanwhile, my Group by clause is expecting all the columns which i am calling on Select Query !!
SELECT		PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,
			MAX(Case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT(Rx.FillDate,4) as DATE))else null end) as [Fill Date],
			Rx.PaidByPlan1,Rx.Plan1StoreId,Rx.Plan1Key
FROM		PLANS PLN
INNER JOIN	Rx Rx
ON			PLN.PlanCodeKey = Rx.Plan1Key
Group By    PLN.PlanCodeKey

Open in new window

chokkaStudent

Author

Commented:
This is the issue.

I have two tables.

Rx                                                Plans

Rx.Plan1Key                                Plans.PlanCodeKey
Rx.FillDate                                   Plans.Bin
                                                    Plans.PlanName
                                                    Plans.PlainId


Rx.Plan1Key = Plans.PlanCodeKey

So, for each Plans.PlanCodeKey i am trying to get the Max(Rx.FillDate)

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
well, please read my article, you will get explanations, and also solutions for your query, I presume.
chokkaStudent

Author

Commented:

Thank you @angelll

Terrific, Thank you !!
select PLN.PLANID,PLN.PLANNAME,PLN.BIN,PLN.PlanCodeKey,Rx.FillDate
  from PLANS PLN
  join ( select Rx.Plan1Key, max(Case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT(Rx.FillDate,4) as DATE))else null end) FillDate
           from Rx
          group by Rx.Plan1Key
       ) Rx
    on Rx.Plan1Key = PLN.PlanCodeKey
Order by Rx.FillDate ASC

Open in new window

chokkaStudent

Author

Commented:
Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.