All geek questions in one place

How can you use SQL to return values ​​for a specified date or the nearest date <a specified date?

I wrote an SQL statement to return a price list based on a date parameter, but I find that on some dates there is no price. I am looking for a way to change this operator to return the price on a specified date, but if this is not possible, return the price for the latest price available before the specified date.

 Select date, grp, id, price From price_table Where date In ('12/31/2009', '11/30/2009') And grp In ('Group1')

For example, I would like to be able to overwrite the above statement to return all records below, indicating the corresponding parameter dates for all records. Suppose this is a subset of the daily pricing table, and the values ​​below are the last prices for the marked months.

 12/31/2009 Group1 1111 100 12/31/2009 Group1 2222 99 12/29/2009 Group1 3333 98 11/30/2009 Group1 1111 100 11/28/2009 Group1 2222 99 11/30/2009 Group1 3333 98

UPDATE: Thanks to some help from srgerg below, I was able to create a statement that works for one date at a time, but I still would like to find a way to pass multiple dates to the request.

 Select p1.date, p1.grp, p1.id, p1.price From price_table As p1 Inner Join (Select Max(p2.date) As maxdt, id From price_table As p2 Where p2.date <= '12/31/2009' Group By p2.id) As p On p.maxdt = p1.date And p1.id = p.id Where grp in ('Group1')
0
source share
2 answers

You can try something like this:

 SELECT date, grp, id , (SELECT TOP 1 price FROM price_table P2 WHERE P1.id = P2.id AND P1.grp = P2.grp AND P2.date <= P1.date ORDER BY P2.date DESC) FROM price_table P1 WHERE P1.date IN ('12/31/2009', '11/30/2009') AND P1.grp IN ('Group1')

Change To get the latest record for each month, group and ID, you can try the following:

 SELECT date, grp, id, price FROM price_table P1 WHERE P1.date = (SELECT MAX(date) FROM price_table P2 WHERE P1.grp = P2.grp AND P1.id = P2.id AND YEAR(P1.date) = YEAR(P2.date) AND MONTH(P1.date) = MONTH(P2.date)) AND P1.grp In ('Group1')
+2
source

Here is my approach to solving this problem:

  • Associate each search date with a date in the table.
  • Use search dates as (optional) group terms.
  • Rank dates in descending order, breaking them down into groups.
  • Select those that require group term values ​​and rank = 1 .

script:

 WITH price_grouped AS ( SELECT date, grp, id, price, dategrp = CASE WHEN date <= '11/30/2009' THEN '11/30/2009' WHEN date <= '12/31/2009' THEN '12/31/2009' /* extend the list of dates here */ END FROM price_table ), price_ranked AS ( SELECT date, grp, id, price, dategrp, rank = RANK() OVER (PARTITION BY grp, dategrp ORDER BY date DESC) FROM price_grouped ) SELECT date, grp, id, price FROM price_ranked WHERE grp IN ('Group1') AND rank = 1

The above solution may not seem very convenient due to the need to repeat each search date twice. An alternative to this could be to define the list of search dates as a separate CTE and, accordingly, assign dates differently:

 WITH search_dates (Date) AS ( SELECT '11/30/2009' UNION ALL SELECT '12/31/2009' /* extend the list of dates here */ ), price_grouped AS ( SELECT p.date, p.grp, p.id, p.price, dategrp = MIN(d.Date) FROM price_table p INNER JOIN search_dates d ON p.date <= d.Date GROUP BY p.date, p.grp, p.id, p.price ), price_ranked AS ( SELECT date, grp, id, price, dategrp, rank = RANK() OVER (PARTITION BY grp, dategrp ORDER BY date DESC) FROM price_grouped ) SELECT date, grp, id, price FROM price_ranked WHERE grp IN ('Group1') AND rank = 1

But keep in mind that the first solution is likely to be more effective.

+1
source

Source: https://habr.com/ru/post/893630/

More articles:


All Articles