2

Hi I have the following query to get all of the pricing out of my system. I need to have all of these selections grouped and then I need to select the 2nd newest date to get the prior day pricing. The problem is that the prior day pricing isn't Today - 1 day. It may be Today - 5 days for example for a certain stock_id. I am doing this quest through microsoft query in excel so row_number() cannot be used.

The query would output the following:

MS_CODE LOCATION_CODE   MFG_CODE    GROUP_CODE  STOCK_ID  EFFECTIVE_DATE PRICE
COKE    12              23          HTG         23        01/12/2018     1.00
COKE    12              23          HTG         23        01/11/2018     0.99
COKE    12              23          HTG         23        01/10/2018     1.05
PEPSI   12              23          HTG         23        01/12/2018     1.10
PEPSI   12              23          HTG         23        01/11/2018     1.00
PEPSI   12              23          HTG         23        01/10/2018     0.60

What I need to output would be:

MS_CODE LOCATION_CODE   MFG_CODE    GROUP_CODE  STOCK_ID  EFFECTIVE_DATE PRICE
COKE    12              23          HTG         23        01/11/2018     0.99
PEPSI   12              23          HTG         23        01/11/2018     1.00

My Current Query is:

SELECT
        dbo.PRICE_BOOK.ms_code, 
        dbo.PRICE_BOOK.location_code, 
        dbo.PRICE_BOOK.mfg_code, 
        dbo.PRICE_BOOK.group_code,
        dbo.PRICE_BOOK.stock_id,
        dbo.PRICE_BOOK.effective_date,
        dbo.PRICE_BOOK.price
FROM dbo.PRICE_BOOK

Thanks in advance.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
jeffpro
  • 21
  • 2

3 Answers3

0

If I'm understanding correctly, something like this should work:

SELECT pb.ms_code, 
       pb.location_code, 
       pb.mfg_code, 
       pb.group_code,
       pb.stock_id,
       MAX(effective_date) effective_date,
       price
FROM dbo.PRICE_BOOK pb
JOIN (SELECT MAX(effective_date) as dte,
             ms_code,
             location_code, 
             mfg_code, 
             group_code,
             stock_id
      FROM dbo.PRICE_BOOK
      GROUP BY ms_code,
               location_code, 
               mfg_code, 
               group_code,
               stock_id) pb2 on pb.ms_code = pb2.ms_code
                            and pb.location_code = pb2.location_code
                            and pb.mfg_code = pb2.mfg_code
                            and pb.group_code = pb2.group_code
                            and pb.stock_id = pb2.stock_id
                            and pb.effective_date < pb2.dte
GROUP BY pb.ms_code, 
         pb.location_code, 
         pb.mfg_code, 
         pb.group_code,
         pb.stock_id,
         price

Syntax seems valid based on a few googles, but we'll see. May need to add more to the inner select / join if stock_id isn't enough for deciding the correct date.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • each combination of the following ms_code, location_code, mfg_code, group_code, stock_id can have a different date with a different price. I've been trying to edit your query for 2 hours with no luck. Any ideas? – jeffpro Jan 13 '18 at 01:06
  • Aaron im also not trying to grab the max im trying to grab the 2nd newest effective_date. Notice in my example my output was the 11th and not the 12th. – jeffpro Jan 13 '18 at 01:07
  • @jeffpro This will grab the second to max date. The inner query gets the true max date and joins to the outer query where the date *is less than* that (`pb.effective_date < pb2.dte`). Then the outer query maxes the date, which is now the second highest, because we know it's less than the true max. I edited the answer and added the join conditions where I think you need them. – Aaron Dietz Jan 16 '18 at 14:45
0

Consider a correlated count subquery to produce a row_num, later used to select 2nd row in group.

SELECT main.ms_code, 
       main.location_code, 
       main.mfg_code, 
       main.group_code,
       main.stock_id,
       main.effective_date,
       main.price
FROM
 (SELECT t.ms_code, 
         t.location_code, 
         t.mfg_code, 
         t.group_code,
         t.stock_id,
         t.effective_date,
         t.price,
         (SELECT COUNT(*) FROM dbo.PRICE_BOOK sub
          WHERE sub.ms_code = t.ms_code 
            AND sub.location_code = t.location_code
            AND sub.mfg_code = t.mfg_code
            AND sub.group_code = t.group_code
            AND sub.stock_id = t.stock_id
            AND sub.effective_date >= t.effective_date) AS row_num
   FROM dbo.PRICE_BOOK t) AS main
WHERE main.row_num = 2;
Parfait
  • 104,375
  • 17
  • 94
  • 125
-1

Using MS SQL. This selects the 2nd most recent date. I assume dbo.PRICE_BOOK.effective_date is the date field.

            SELECT * FROM
            (SELECT
                    dbo.PRICE_BOOK.ms_code, 
                    dbo.PRICE_BOOK.location_code, 
                    dbo.PRICE_BOOK.mfg_code, 
                    dbo.PRICE_BOOK.group_code,
                    dbo.PRICE_BOOK.stock_id,
                    dbo.PRICE_BOOK.effective_date,
                    ROW_NUMBER() OVER (ORDER BY dbo.PRICE_BOOK.effective_date) AS ROWNUM,
                    dbo.PRICE_BOOK.price
            FROM dbo.PRICE_BOOK
            ORDER BY dbo.PRICE_BOOK.effective_date DESC
            ) AS TEMP
        WHERE ROWNUM  = MAX(ROWNUM)-1 
        GROUP BY  ms_code, 
                    location_code, 
                    mfg_code, 
                    group_code,
                    stock_id,
                    effective_date,
                    ROWNUM,
                    price
W. Richtmyer
  • 34
  • 1
  • 3