Return latest price per product - SQL

Question

I am an SQL Noob and have been thrown in at the deep end it feels, I have been asked to pull some data from a DB using an existing script. I need to modify the script such that I pull the product info using the latest price for each product. Having done some reading I believe I need to use a nested select statement to pull the max month per product to get the latest price but I am unsure as to how to incorporate it into my script.

Currently the DB is returning the following format:

Fill Month  ProdName1   ProdName2   PackType    Quantity    Prod Cost
  2018/01   ProdA        ProdA_ALT  Pack1         30          7.35
  2018/02   ProdB        ProdB_ALT  Pack5         60          6.68
  2018/01   ProdE        ProdE_ALT  Pack2         54538      81010.86
  2018/03   ProdA        ProdA_ALT  Pack1         600         97.22
  2018/01   ProdC        ProdC_ALT  Pack2         1230       30192.25
  2018/05   ProdD        ProdD_ALT  Pack4         60262      51396.6
  2018/01   ProdF        ProdF_ALT  Pack3         480         109.3
  2018/07   ProdA        ProdA_ALT  Pack1         210         149.94
  2018/09   ProdF        ProdF_ALT  Pack3         360         26.68
  2018/10   ProdD        ProdD_ALT  Pack4         9585        14350.47

But what I wish to return are rows # 2,3,5,8,9,10 since these are the latest prices based on date for each of the products.

My SQL query looks as follows:

SELECT               
        table1.month AS 'Fill Month',
        table2.field2 AS PackType,
        TRIM(table2.brand) ProdName1,
        TRIM(table2.field3) ProdName2,
        SUM(table3.field4) 'Fill Qty',
        SUM(table3.field5) 'Calc Cost'

FROM
        DB_Name.overseas.CLAIMINFO table3
        INNER JOIN DB_Name.overseas.DIM1 table1
        ON table3.key1 = table1.key1
        INNER JOIN DB_Name.overseas.DIM2 table2
        ON table3.key2 = table2.key2
        INNER JOIN DB_Name.overseas.DIM3 table4 
        ON table3.key3 = table4.key3
        INNER JOIN DB_Name.overseas.DIM4 table5
        ON table3.key4 = table5.key4
        INNER JOIN DB_Name.overseas.DIM5 table6
        ON table3.key5 = table6.key5 
        INNER JOIN DB_Name.overseas.LOOKUP table7
        ON table3.key6 = table7.key6
        INNER JOIN DB_Name.overseas.DIM6 table8
    ON table3.key7 = table8.key7
    INNER JOIN DB_Name.overseas.LOOKUP_key8 table9
        ON table3.key8 = table9.key8

WHERE
        table1.day BETWEEN '2018-01-01' AND '2018-08-31'  --TIME PERIOD
        AND (table2.brand LIKE '%PRODA%' OR table2.brand LIKE '%PRODUCTB%'
                                            OR table2.brand LIKE '%PRODC%'
                                            OR table2.brand LIKE '%PRODD%'
                                            OR table2.brand LIKE '%PRODE%'
                                            OR table2.brand LIKE '%PRODF%'
        AND LEFT(table3.account_id, 1) <> 'E' 
        AND grp1.grp_nbr NOT IN ('12345', '67890')

GROUP BY 
        table1.month,
        table2.field2,
        table2.field3,
        table2.drug_str_descr,
        dr1

I have tried using a nested SELECT just inside the first from statement but the RDBMS kicks back an error each time, if anyone has any pointers I would be very grateful.

while I understand you're (probably) trying to generalize the query by obfuscating table and column names, you're also confusing the issue; for example, why are you pulling `table[4-9]` into the query when they don't appear to have any purpose (or are you trying to verify there are matching rows in said tables?); what is `grp1` (red'd in last WHERE clause)?; why don't your non-aggregate `select` columns match the `group by` columns (ie, do you understand the consequences)? why are looking for `sum()` when you don't mention anything about needing sums? — markp-fuso, Sep 18 '18 at 23:24

score 1 · Accepted Answer · answered Sep 18 '18 at 22:49

1

To get the last date each product changed, try this:

(SELECT ProdName1, Max(FillMonth) 
FROM tablename
GROUP BY ProdName1) AS ProductDates

You can join to this in your FROM clause to limit the results to just the months you want.

answered Sep 18 '18 at 22:49

Without Haste

507
2
6

Return latest price per product - SQL

1 Answers1