0

I've written the below code to get the max ACUTAL_SHIP_DATE value if the PLANT column values are duplicated and it is working as expected.

SELECT A.PLANT,
       A.SLOT_NUM,
       A.CONSUMPTIONENDITEM,
       A.SALESORDERNUMBER,
       A.SOLINEITEM,
       A.ACUTAL_SHIP_DATE
FROM (SELECT TOP 10
             P.SLOT_NUM,
             P.PLANT,
             P.CONSUMPTIONENDITEM,
             P.SALESORDERNUMBER,
             P.SOLINEITEM,
             T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
      FROM SRTPROJECT P WITH (NOLOCK)
           INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
                                                       AND P.PLANT = T.PLANT
                                                       AND P.CONSUMPTIONENDITEM = T.MAT_NUM) A
     INNER JOIN (SELECT DI.PLANT,
                        MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE
                 FROM (SELECT TOP 10
                              P.SLOT_NUM,
                              P.PLANT,
                              P.CONSUMPTIONENDITEM,
                              P.SALESORDERNUMBER,
                              P.SOLINEITEM,
                              T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
                       FROM SRTPROJECT P WITH (NOLOCK)
                            INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
                                                                        AND P.PLANT = T.PLANT
                                                                        AND P.CONSUMPTIONENDITEM = T.MAT_NUM) DI
                 GROUP BY DI.PLANT) B ON A.PLANT = B.PLANT
                                     AND A.ACUTAL_SHIP_DATE = B.ACUTAL_SHIP_DATE;

in the above query Step1: Fetching the data from two tables and making collection.

SELECT TOP 10
       P.SLOT_NUM,
       P.PLANT,
       P.CONSUMPTIONENDITEM,
       P.SALESORDERNUMBER,
       P.SOLINEITEM,
       T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
FROM SRTPROJECT P WITH (NOLOCK)
     INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
                                                 AND P.PLANT = T.PLANT
                                                 AND P.CONSUMPTIONENDITEM = T.MAT_NUM;

Step2: Applying Max if the PLANT column value is duplicated

(SELECT TOP 10 P.SLOT_NUM, P.PLANT, P.CONSUMPTIONENDITEM, P.SALESORDERNUMBER, P.SOLINEITEM,T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE FROM SRTPROJECT P WITH (NOLOCK) INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM AND P.PLANT = T.PLANT AND P.CONSUMPTIONENDITEM = T.MAT_NUM) AS A INNER JOIN (Select DI.PLANT, MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE From (SELECT TOP 10 P.SLOT_NUM, P.PLANT, P.CONSUMPTIONENDITEM, P.SALESORDERNUMBER, P.SOLINEITEM, T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE FROM SRTPROJECT P WITH (NOLOCK) INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM AND P.PLANT T.PLANT AND P.CONSUMPTIONENDITEM = T.MAT_NUM) AS DI GROUP BY DI.PLANT)

Step3: then selecting all the columns which is required with the final query which is written initially

but the problem is I'm using same subquery twice. is there any way to remove the duplicate code.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sivamohan Reddy
  • 436
  • 1
  • 8
  • 26
  • 1
    FYI, white space and line breaks are magical things; they make completely unreadable code (like yours) suddenly readable by all. Please get into the habit of using them; you're make your life and everyone else's much easier. I haven't formatted the last one, as the statement is invalid. You need to do that. – Thom A Nov 16 '21 at 11:14
  • 2
    I've removed the [[tag:mysql]] tag here, as this appears to be T-SQL due to the use of the `NOLOCK` hint. Speaking of which, *why* are you spamming said hint? It's going to be ruining how trustworthy the data is. Required reading: [Bad habits : Putting NOLOCK everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) – Thom A Nov 16 '21 at 11:16
  • 1
    Finally, the use of `TOP` with no `ORDER BY` has a "code smell" to it too. Without an explicit `ORDER BY` then the rows you get will be completely arbitrary and could easily be different *every* time you run the query. Add the `NOLOCK` hints into that recipe and you very likely have trash data, if i am honest. – Thom A Nov 16 '21 at 11:18
  • thats fine, but need to remove the duplicate code – Sivamohan Reddy Nov 16 '21 at 11:38
  • What's fine? None of what I highlight in my above comments in "fine". – Thom A Nov 16 '21 at 11:43
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Nov 16 '21 at 15:43

1 Answers1

0

I think this is what you want, but I have not tested it because you do not provide sample data:

with cte 
as
(
SELECT TOP 10
             P.SLOT_NUM,
             P.PLANT,
             P.CONSUMPTIONENDITEM,
             P.SALESORDERNUMBER,
             P.SOLINEITEM,
             T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
      FROM SRTPROJECT P WITH (NOLOCK)
           INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
                                                       AND P.PLANT = T.PLANT
                                                       AND P.CONSUMPTIONENDITEM = T.MAT_NUM
)
SELECT A.PLANT,
       A.SLOT_NUM,
       A.CONSUMPTIONENDITEM,
       A.SALESORDERNUMBER,
       A.SOLINEITEM,
       A.ACUTAL_SHIP_DATE
FROM cte A
     INNER JOIN (SELECT DI.PLANT,
                        MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE
                 FROM cte DI
                 GROUP BY DI.PLANT) B ON A.PLANT = B.PLANT
                                     AND A.ACUTAL_SHIP_DATE = B.ACUTAL_SHIP_DATE;

It just uses WITH to create "cte" so you do not have to execute the same query twice.

You should try to provide minimal reproductible examples, with sample data and expected results. This way it would be easier to help you

Carlos
  • 1,638
  • 5
  • 21
  • 39