CODEWITHSUNDEEP
sql-server
user3431239
user3431239

Reputation: 267

please help to revamp this simple but ugly query

I've done the query but it looks ugly. And the main concern, i worry the performance would be slow.

This is my table:

ID      ProductCode         Qty        PYear      PMonth
1            A1              4          2015        2
2            A2              5          2015        2  
3            A3              2          2014       12
4            A3             16          2015        4

I want to get the latest sell date categories by each product, below is the result that i want:

ProductCode         Qty         Year      Month
     A1              4          2015        2
     A2              5          2015        2  
     A3             16          2015        4

looks simple, but my query is complicated, i bet there must be an easy solution for this, below is my query i'm working with, i joined the year and month into 1 column:

SELECT A.ProductCode, B.Qty, PDate 
FROM (
    SELECT MAX(ID) AS purchaseID, 
    ProductCode, 
    MAX(CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') AS PDate
    FROM Table1
    GROUP BY ProductCode
) AS B
LEFT JOIN Table1 AS B ON A.ID= B.ID

Upvotes: 0

Views: 78

Answers (2)

John Bell
John Bell

Reputation: 2350

You could use ROW_NUMBER with a CTE? You don't really need to use a JOIN in this case:

;WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY PRODUCTCODE ORDER BY PYEAR DESC, PMONTH DESC) AS RN
  FROM TABLE1)
 SELECT PRODUCTCODE, QTY, PYEAR, PMONTH
 FROM CTE
 WHERE RN = 1

Here's an SQL Fiddle: http://sqlfiddle.com/#!6/90fe1/1

Upvotes: 2

Dibstar
Dibstar

Reputation: 2364

Using windowed functions should do the trick - this gives each record a row based on descending date, and groups by productcode

SELECT 
purchaseID
,ProductCode
,Qty
,Pdate
FROM
( 
SELECT 
purchaseID 
,Qty
,ProductCode 
,CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') AS PDate
,ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') DESC) AS ROWNO
FROM Table1 
)
WHERE ROWNO = 1

Upvotes: 2

Related Questions