CODEWITHSUNDEEP
sqlsql-server
RBC Kyle Bullard
RBC Kyle Bullard

Reputation: 87

Most Recent Date in many-to-many Query

I am trying to query Parts that have not moved in my system for more than 90 days. I am starting to run into a many-to-many issue, and I am very new to SQL.

It needs to work like:

Meets all criteria, post result in query.

DECLARE @now DATETIME
DECLARE @90daysago DATETIME


SET @now = GETDATE()
SET @90daysago = DATEADD(day, -90, @now)



SELECT
    o.PartNo,
    o.JobNo,
    o.Status,

    cast(po.DateEnt as DATE) AS DateEnt,


    tt.TicketDate,
    (o.qtyToMake - o.QtyShipped2Stock) AS NumOpen

FROM
    OrderDet AS o

INNER JOIN PODet AS pod
    ON o.PartNo = pod.PartNo

INNER JOIN PO as po
    ON pod.PONum = po.PONum

INNER JOIN TimeTicketDet as tt
    ON o.JobNo = tt.JobNo

WHERE
    o.Status = 'Open' AND
    o.PartNo = '72347' AND
    o.JobNo = '16516-02'


ORDER BY
    po.DateEnt DESC

https://photos.google.com/share/AF1QipMPAogamiVv_19JAQHO3GBYsBr9s6PPt-WXGbdrPTqvW-EAU0QjkCI0s-cidT-IuQ?key=ZElDNzFsdmU2XzZPVmh1d01tWWZLcU1WbllDUzN3

The above link shows a piece of the executed query, I am running it on a mock PartNo.

The first columns are PartNo, JobNo respectively.

The two dates are DateEnt, TicketDate respectively.

As you can see, every single change in our system duplicates the data and shows all of the dates. When I add the criteria >@90daysago then it will show a DateEnt from 12-19-2016 which is a closed Order. I know this is incredibly vague, I just do not know how to show only the most recent values for each date for the query. I will be glad to provide any more useful information.

Sample Data
+--------+-------+--------+------------+------------+
| PartNo | JobNo | Status |  DateEnt   | TicketDate |
+--------+-------+--------+------------+------------+
|  72347 |     1 | OPEN   | 12/19/2016 | 10/6/2016  |
|  72347 |     2 | OPEN   | 12/19/2016 | 10/5/2016  |
|  72347 |     3 | OPEN   | 12/19/2016 | 12/23/2016 |
|  72347 |     4 | OPEN   | 12/19/2016 | 12/23/2016 |
|  72347 |     1 | OPEN   | 12/19/2016 | 10/6/2016  |
|  72347 |     2 | OPEN   | 3/30/2017  | 10/5/2016  |
|  72347 |     3 | OPEN   | 3/30/2017  | 12/23/2016 |
|  72347 |     4 | OPEN   | 3/30/2017  | 12/23/2016 |
|  72347 |     1 | OPEN   | 3/30/2017  | 10/6/2016  |
|  72347 |   200 | OPEN   | 1/9/2017   | 10/5/2016  |
|  72347 |     3 | OPEN   | 3/30/2017  | 12/23/2016 |
|  72347 |     4 | OPEN   | 3/30/2017  | 12/26/2016 |
|  72347 |     1 | OPEN   | 3/30/2017  | 10/6/2016  |
|  72347 |     2 | OPEN   | 3/30/2017  | 10/5/2016  |
|  72347 |     3 | OPEN   | 3/30/2017  | 12/23/2016 |
|  72347 |     4 | OPEN   | 3/30/2017  | 12/23/2016 |
+--------+-------+--------+------------+------------+

Sample Result
+--------+-------+--------+----------+------------+--+
| PartNo | JobNo | Status | DateEnt  | TicketDate |  |
+--------+-------+--------+----------+------------+--+
|  72347 |   200 | OPEN   | 1/9/2017 | 10/5/2017  |  |
+--------+-------+--------+----------+------------+--+

Upvotes: 0

Views: 82

Answers (1)

Wendy
Wendy

Reputation: 660

Please try this. You can adjust PARTITION BY and ORDER BY to meet your requirement.

;WITH cte AS(SELECT o.PartNo
   , o.JobNo
   , o.Status
   , CAST(po.DateEnt as DATE) AS DateEnt
   , tt.TicketDate
   , (o.qtyToMake - o.QtyShipped2Stock) AS NumOpen
   , ROW_NUMBER() OVER(PARTITION BY o.PartNo ORDER BY po.DateEnt DESC, tt.TicketDate DESC) AS Rownum
FROM OrderDet AS o
INNER JOIN PODet AS pod
    ON o.PartNo = pod.PartNo
INNER JOIN PO AS po
    ON pod.PONum = po.PONum
INNER JOIN TimeTicketDet AS tt
    ON o.JobNo = tt.JobNo
WHERE o.Status = 'Open' 
AND po.DateEnt < CONVERT(VARCHAR(10), DATEADD(DAY, -90, GETDATE()), 120)
)

SELECT *
FROM cte
WHERE Rownum = 1
AND TicketDate < CONVERT(VARCHAR(10), DATEADD(DAY, -90, GETDATE()), 120);

Upvotes: 1

Related Questions