I’m trying to parse some data for a dashboard we’re creating. The data I’m looking at has columns: job_num, kit_timestamp, and skid_serial. The skid_serial has the “side” category incorporated (A or B). I'm getting the side using this:

RIGHT(LEFT(Skid_Serial, 2), 1)

I’m needing just the newest job_num for each side based on the kit_timestamp. Sometimes, production may only run one side, sometimes 2 sides. I need just the single job_num query because it will end up being incorporated into an IN query.

This is what I've tried, but it will return the latest job_num and timestamp for each job that has run on the current day, instead of just the latest.

SELECT job_num, max(kit_timestamp) as Kit_Timestamp, RIGHT(LEFT(Skid_Serial, 2), 1) as Side
FROM  Line_Cummins_MDC_Kit_Cell1.dbo.Line_Cummins_MDC_Kit
WHERE CONVERT(date, kit_timestamp) > dateadd(day, - 1, CONVERT(date, getdate()))
GROUP BY RIGHT(LEFT(Skid_Serial, 2), 1), Job_num
ORDER BY kit_timestamp desc

Any help would be appreciated. I'm not the greatest at SQL, but know this is possible somehow.

Here's an example of the original data:

The result I'm looking to get is this:

As you can see, the data has multiple days of data, and an 'A' or 'B' for the second character of the skid_serial, which signifies the side. I'm needing to extract the most recent job_num for each side. Some of these production lines only run a single side at times, so I need to account for that as well.

CodePudding user response：

So maybe this ROW_NUMBER() based solution will work for you. However I have doubts about your WHERE condition. Maybe just remove it, unless you don't want to show something from yesterday which happens to be the latest job for a side?

SELECT 
job_num 
FROM 
(
 SELECT job_num,
 ROW_NUMBER() OVER (PARTITION BY RIGHT(LEFT(Skid_Serial, 2), 1) ORDER BY 
 Kit_Timestamp DESC) as RN,
 Kit_Timestamp 
 FROM  Line_Cummins_MDC_Kit_Cell1.dbo.Line_Cummins_MDC_Kit LK
    WHERE CONVERT(date, kit_timestamp) > dateadd(day, - 1, CONVERT(date, getdate()))
) T
WHERE RN=1
ORDER BY Kit_Timestamp DESC

CodePudding user response：

the following WHERE condition

 WHERE CONVERT(date, kit_timestamp) > dateadd(day, - 1, CONVERT(date, getdate())) 

indicate you want today date in addition to above query you can use

cast(kit_timestamp as date) = cast(GETDATE() as date) 

however to get your desired result the where condtion must be change to

 WHERE kit_timestamp > dateadd(day, - 1, CONVERT(date, getdate()))

since your data is from yesterday

you can use Subquery and Join to get you desired result.

SELECT job_num
FROM   (SELECT Max(kit_timestamp)             AS Kit_Timestamp,
               RIGHT(LEFT(skid_serial, 2), 1) AS Side
        FROM   sample
        WHERE  kit_timestamp > Dateadd(day, -1, CONVERT(DATE, Getdate()))
        GROUP  BY RIGHT(LEFT(skid_serial, 2), 1)) S1
       JOIN sample S2
         ON S1.kit_timestamp = S2.kit_timestamp  

dbfiddle