Link to home
Start Free TrialLog in
Avatar of JasonAsh
JasonAsh

asked on

How do I pull max start date from a sql table and check another date at the same time

I have a delivery prices table that has about 50,000 records in and I need to pull the most recent prices using the start date and end dates. eg get MAX(start Date) but only if the End Date has not pasted. Also in my table if a record has a blank end date on the form it is entered in the database as the date 1753-01-01, I have tried to explain it a bit more in my picture below,

Can this be done ?
Del.JPG
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

OK, how about this:

Select DP.*
From delivery_prices DP Inner Join
  (Select My_Product, max(start_date) as MaxStartDate from delivery_prices 
     Where End_Date > getdate() OR End_Date = '1753-01-01') R 
  ON DP.My_Product = R.My_Product AND DP.Start_Date = R.MaxStartDate

Open in new window

Avatar of JasonAsh
JasonAsh

ASKER

Thanks Daniel Wilson I'll give it a go.
Hi Daniel,

I am getiting an error about: 'not contained in either an aggregate function or the GROUP BY clause'
Do I need to add in the columns my_product and colour into a GROUP BY at the end of the script ?

Thanks.
Yeah, sorry.  Try this:

Select DP.*
From delivery_prices DP Inner Join
  (Select My_Product, max(start_date) as MaxStartDate from delivery_prices 
     Where End_Date > getdate() OR End_Date = '1753-01-01'
     GROUP By My_Product) R 
  ON DP.My_Product = R.My_Product AND DP.Start_Date = R.MaxStartDate

Open in new window

Thanks Daniel looks good just testing and checking it now and will then send over the points.

Just to double check is it OK to only have the join on DP.My_Product = R.My_Product ?
Do I need a join on Colour too ? DP.Colour = R.Colour
I suppose not as it's looking like it is working OK without the join.
That depends on what makes for a unique item.  If colour is part of the (de facto) unique identifier, include it in your join.
Hmmm I suppose yes it is as in the example above I could have one products with many colours and also null in the colour field. eg

my_product  colour
TRS005        BLACK
TRS005        BLACK
TRS005        NULL
TRS005        NAVY
TRS005        RED
etc

so I suppose I just need it as a join and in the select MAX bit eg:

Select DP.*
From delivery_prices DP Inner Join
  (Select My_Product, colour, max(start_date) as MaxStartDate from delivery_prices
     Where End_Date > getdate() OR End_Date = '1753-01-01'
     GROUP By My_Product, Colour) R
 ON DP.My_Product = R.My_Product
AND DP.colour = R.colour
AND DP.Start_Date = R.MaxStartDate
I just tried the above code puttting in the colour join but it then removes the rows that have Null in the Colour field,
any ideas ?
Looks good to me.
Hi Daniel,

I've found a problem with the original code above in if a style has the same start date it pulls back 2 rows even if one rows end date is out of range eg

my_product   colour      start date       end date
TRS005         BLACK     01-JAN-2009    01-JAN-1753
TRS005         BLACK     01-JAN-2009    19-JAN-2009

Even though the end date for 19-JAN-2009 should be excluded because of:
Where End_Date > getdate() OR End_Date = '1753-01-01'

I think it is being pulled out because the sub query My_Product, max(start_date) pulls out:
TRS005  01-JAN-2009
then because there is a join only on Item and Start Date we get 2 lines.

Does this makes any sense ? Do I need to add in End Date some how to the query ?

Thanks Jason.
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Daniel,

Thanks looks like the code above works, I'l look into why there can be 2 open prices but just so I know what would I need to put to do your example of:

The one with the earliest end date (treating 1753-01-01 as the latest) comes to mind ... but let me know.

Thanks,
Jason.
I think this will do it:

Select DP.*
From delivery_prices DP Inner Join
 (Select My_Product, colour, max(case when end_date = '1753-01-01' then '2999-12-31' else end_date end) as MaxEndDate from delivery_prices DP2
     Where End_Date > getdate() OR End_Date = '1753-01-01'
     GROUP By My_Product, Colour) R2 INNER JOIN
  (Select My_Product, colour, max(start_date) as MaxStartDate from delivery_prices
     Where End_Date > getdate() OR End_Date = '1753-01-01'
     GROUP By My_Product, Colour) R
 
 ON DP2.My_Product = R.My_Product
AND DP2.colour = R.colour
AND DP.Start_Date = R.MaxStartDate) R2
 ON DP.My_Product = R2.My_Product
AND DP.colour = R2.colour
AND DP.Start_Date = R2.MaxStartDate
 
AND DP.EndDate = Case When R2.MaxEndDate = '2999-12-31' then '1753-01-01' else R2.maxEndDate END

Open in new window

Great many thanks Daniel for your help.