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
Can this be done ?
Del.JPG
ASKER
Thanks Daniel Wilson I'll give it a go.
ASKER
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.
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
ASKER
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.
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.
ASKER
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
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
ASKER
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 ?
any ideas ?
Looks good to me.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
ASKER
Great many thanks Daniel for your help.
Open in new window