Query earliest date for each members. How do I make this?

Question

Post reply

Query earliest date for each members. How do I make this?

jbeclapez

SSC-Addicted

Points: 405

March 2, 2022 at 10:54 am

Go to Answer

#3991589

Hello,

SUMMARY : I have a mechanic that works on different vehicle types and different cars. Sometimes he makes a change that he should not have done and we need to find how it was BEFORE his change.

See : http://sqlfiddle.com/#!18/36660/1

DETAILED : I made a table below with an example. I also prepared some SQL for later.

In our example, we know that Mike messed up his work on the 2000-01-03 at 15:00:00.

In yellow/orange you have the day when Mike messed up. We can see from the orange lines that are filtered on Mike that he messed up 2 VehicleType (Truck and Car) and only 2 CardID (100 and 200). So it means 2 carID (as a carID is not unique - it is combined with the VehicleType).

Now that we know that Mike messed up 2 VehicleID+CardID we need to get the list of the Piece he messed up and we see that he messed up the car 200 Tyre and also the car 100 Engine, Break and Tyre. (This is query 1 below)

Now my goal is to retrieve the lines with the way it was before Mike did his changes. Only the latest change per VehicleType, per CardID per Piece.

In our example, i expect the query to provide me 3 lines - the ones in green. See that there is no info on the Engine as it was the default engine. In that case I would like to see an empty line or written default (better!). I tried to make a subquery from the first query and the result is in the query 2 (see below). My second query does not work as it should... i do not know of to filter on the latest dates. I used TOP with no success...

Car

Query 1

SELECT
TAudit.VehicleType
,TAudit.CarID
,TAudit.Piece
FROM dbo.TAudit
WHERE TAudit.Mechanic = 'Mike'
AND TAudit.Date BETWEEN '2000-01-03' AND '2000-01-03 23:59:59'
GROUP BY TAudit.VehicleType
,TAudit.CarID
,TAudit.Piece

Query 2

SELECT 
  TAudit.VehicleType
 ,TAudit.CarID
 ,TAudit.Piece
 ,TAudit_1.Mechanic
 ,TAudit_1.[Use]
 ,TAudit_1.Week
 ,TAudit_1.Value1
 ,TAudit_1.Value2
 ,TAudit_1.Date
FROM (SELECT
    TAudit.VehicleType
   ,TAudit.CarID
   ,TAudit.Mechanic
   ,TAudit.Piece
   ,TAudit.Date
  FROM dbo.TAudit) TAudit
INNER JOIN dbo.TAudit TAudit_1
  ON TAudit.VehicleType = TAudit_1.VehicleType
    AND TAudit.CarID = TAudit_1.CarID
    AND TAudit.Piece = TAudit_1.Piece
WHERE TAudit.Mechanic = 'Mike'
AND TAudit.Date BETWEEN '2000-01-03' AND '2000-01-03 23:59:59'
 And TAudit_1.Date < '2000-01-03'
GROUP BY TAudit.VehicleType
        ,TAudit.CarID
        ,TAudit.Piece
        ,TAudit_1.Mechanic
        ,TAudit_1.[Use]
        ,TAudit_1.Week
        ,TAudit_1.Value1
        ,TAudit_1.Value2
        ,TAudit_1.Date
ORDER BY TAudit_1.Date DESC

Create Table

CREATE TABLE TAudit
    ([VehicleType] varchar(5), [CarID] int, [Mechanic] varchar(4), [Use] varchar(1), [Piece] varchar(10), [Week] int, [Value1] int, [Value2] int, [Date] datetime)
;
    
INSERT INTO TAudit
    ([VehicleType], [CarID], [Mechanic], [Use], [Piece], [Week], [Value1], [Value2], [Date])
VALUES
    ('Bike', 100, 'Paul', 'Y', 'break', 1, 100, 200, '2000-01-01 08:00:00'),
    ('Car', 100, 'Mike', 'Y', 'Tire', 2, 101, 201, '2000-01-01 08:00:00'),
    ('Bike', 300, 'Paul', 'N', 'break', 1, 102, 202, '2000-01-01 08:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'Tire', 2, 103, 203, '2000-01-01 08:00:00'),
    ('Car', 100, 'Mike', 'Y', 'break', 1, 104, 1, '2000-01-01 08:00:00'),
    ('Car', 100, 'Mike', 'Y', 'Tire', 1, 105, 1, '2000-01-01 08:00:00'),
    ('Car', 100, 'Mike', 'Y', 'break', 1, 104, 204, '2000-01-01 09:00:00'),
    ('Car', 100, 'Mike', 'Y', 'Tire', 1, 105, 205, '2000-01-02 10:00:00'),
    ('Truck', 100, 'Tim', 'N', 'break', 1, 106, 206, '2000-01-02 10:00:00'),
    ('Truck', 200, 'Tim', 'Y', 'Tire', 1, 107, 207, '2000-01-02 10:00:00'),
    ('Truck', 100, 'Tim', 'Y', 'break', 2, 108, 208, '2000-01-02 10:00:00'),
    ('Truck', 300, 'Tim', 'N', 'Tire', 2, 109, 209, '2000-01-02 10:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'break', 2, 110, 210, '2000-01-02 10:00:00'),
    ('Car', 200, 'Mike', 'N', 'Tire', 1, 111, 211, '2000-01-02 10:00:00'),
    ('Bike', 100, 'Paul', 'N', 'break', 1, 112, 212, '2000-01-02 10:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'Tire', 1, 113, 213, '2000-01-02 10:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'break', 1, 10, 20, '2000-01-03 15:00:00'),
    ('Car', 100, 'Mike', 'Y', 'Engine', 2, 11, 21, '2000-01-03 15:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'Tire', 2, 13, 23, '2000-01-03 15:00:00'),
    ('Car', 100, 'Mike', 'Y', 'break', 1, 14, 24, '2000-01-03 15:00:00'),
    ('Car', 100, 'Mike', 'Y', 'Tire', 1, 15, 25, '2000-01-03 15:00:00'),
    ('Truck', 300, 'Tim', 'N', 'Tire', 2, 19, 29, '2000-01-03 15:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'break', 2, 20, 30, '2000-01-03 15:00:00'),
    ('Car', 200, 'Mike', 'N', 'Tire', 1, 21, 31, '2000-01-03 15:00:00'),
    ('Bike', 100, 'Paul', 'N', 'break', 1, 22, 32, '2000-01-03 15:00:00'),
    ('Bike', 100, 'Paul', 'Y', 'Tire', 1, 23, 33, '2000-01-03 15:00:00')
;

This topic was modified 2 years, 8 months ago by jbeclapez.
This topic was modified 2 years, 8 months ago by jbeclapez.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

I need a bit of clarification.

Presumably the inputs to this query would be

a) Mechanic (Mike, in your example), and

b) MessedUpAt (2000-01-03 at 15:00:00, in your example)

Is this correct?

Based on these inputs, you want to see the most-recent rows of data for the selected mechanic, before 'MessedUpAt', for each combination of CarId and Piece, is that correct?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Why does a single datetime of a "mess up" apply to every vehicle worked on subsequently during that day? What if the mistake was made at 11:59PM there's only 1 minute of subsequent "mess up" time? Did Mike mess up a truck (VehicleType) as well as a car or just cars? Just cars per the data or so it seems. These type of "what did the data look like at a point in time" queries are made simple by using system versioned temporal tables. Is that a possibility?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jbeclapez SSC-Addicted Points: 405 More actions · Answer 3

Hi Phil,

I think you are correct : Ultimately we need to show the " most-recent rows of data for the selected mechanic, before 'MessedUpAt', for each combination of CarId and Piece, is that correct?"

And in our example that would be the 3 lines in greens and maybe an added line with the "Engine" as it has no past record.

By doing this we would now for each piece of the car the previous status.

Thanks

jbeclapez SSC-Addicted Points: 405 More actions · Answer 4

Hi Phil,

In my case I can not use system versioned temporal tables. I liked that idea though but it is not available to me. It all starts u nfortunaly with the table I shared. Please read the comment I wrote above to Phil. I hope it can help.

Thanks Phil.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Please check this out and let me know what you think. I used a temp table and converted your VARCHAR(1) to CHAR(1). If you want to know why VARCHAR(1) is a bad idea, we can have a separate discussion. I do not understand the 'engine' requirement ... please expand on that.

DECLARE @Mechanic VARCHAR(4) = 'Mike';
DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';

DROP TABLE IF EXISTS #TAudit;

CREATE TABLE #TAudit
(
    VehicleType VARCHAR(5) NOT NULL
   ,CarID INT NOT NULL
   ,Mechanic VARCHAR(4) NOT NULL
   ,[Use] CHAR(1) NOT NULL
   ,Piece VARCHAR(10) NOT NULL
   ,Week INT NOT NULL
   ,Value1 INT NOT NULL
   ,Value2 INT NOT NULL
   ,Date DATETIME NOT NULL
);

INSERT #TAudit
(
    VehicleType
   ,CarID
   ,Mechanic
   ,[Use]
   ,Piece
   ,Week
   ,Value1
   ,Value2
   ,Date
)
VALUES
('Bike', 100, 'Paul', 'Y', 'break', 1, 100, 200, '2000-01-01 08:00:00')
,('Car', 100, 'Mike', 'Y', 'Tire', 2, 101, 201, '2000-01-01 08:00:00')
,('Bike', 300, 'Paul', 'N', 'break', 1, 102, 202, '2000-01-01 08:00:00')
,('Bike', 100, 'Paul', 'Y', 'Tire', 2, 103, 203, '2000-01-01 08:00:00')
,('Car', 100, 'Mike', 'Y', 'break', 1, 104, 1, '2000-01-01 08:00:00')
,('Car', 100, 'Mike', 'Y', 'Tire', 1, 105, 1, '2000-01-01 08:00:00')
,('Car', 100, 'Mike', 'Y', 'break', 1, 104, 204, '2000-01-01 09:00:00')
,('Car', 100, 'Mike', 'Y', 'Tire', 1, 105, 205, '2000-01-02 10:00:00')
,('Truck', 100, 'Tim', 'N', 'break', 1, 106, 206, '2000-01-02 10:00:00')
,('Truck', 200, 'Tim', 'Y', 'Tire', 1, 107, 207, '2000-01-02 10:00:00')
,('Truck', 100, 'Tim', 'Y', 'break', 2, 108, 208, '2000-01-02 10:00:00')
,('Truck', 300, 'Tim', 'N', 'Tire', 2, 109, 209, '2000-01-02 10:00:00')
,('Bike', 100, 'Paul', 'Y', 'break', 2, 110, 210, '2000-01-02 10:00:00')
,('Car', 200, 'Mike', 'N', 'Tire', 1, 111, 211, '2000-01-02 10:00:00')
,('Bike', 100, 'Paul', 'N', 'break', 1, 112, 212, '2000-01-02 10:00:00')
,('Bike', 100, 'Paul', 'Y', 'Tire', 1, 113, 213, '2000-01-02 10:00:00')
,('Bike', 100, 'Paul', 'Y', 'break', 1, 10, 20, '2000-01-03 15:00:00')
,('Car', 100, 'Mike', 'Y', 'Engine', 2, 11, 21, '2000-01-03 15:00:00')
,('Bike', 100, 'Paul', 'Y', 'Tire', 2, 13, 23, '2000-01-03 15:00:00')
,('Car', 100, 'Mike', 'Y', 'break', 1, 14, 24, '2000-01-03 15:00:00')
,('Car', 100, 'Mike', 'Y', 'Tire', 1, 15, 25, '2000-01-03 15:00:00')
,('Truck', 300, 'Tim', 'N', 'Tire', 2, 19, 29, '2000-01-03 15:00:00')
,('Bike', 100, 'Paul', 'Y', 'break', 2, 20, 30, '2000-01-03 15:00:00')
,('Car', 200, 'Mike', 'N', 'Tire', 1, 21, 31, '2000-01-03 15:00:00')
,('Bike', 100, 'Paul', 'N', 'break', 1, 22, 32, '2000-01-03 15:00:00')
,('Bike', 100, 'Paul', 'Y', 'Tire', 1, 23, 33, '2000-01-03 15:00:00');

WITH ordered
AS (SELECT ta.VehicleType
          ,ta.CarID
          ,ta.Mechanic
          ,ta.[Use]
          ,ta.Piece
          ,ta.Week
          ,ta.Value1
          ,ta.Value2
          ,ta.Date
          ,rn = ROW_NUMBER() OVER (PARTITION BY ta.CarID, ta.Piece ORDER BY ta.Date DESC)
    FROM #TAudit ta
    WHERE ta.Mechanic = @Mechanic
          AND ta.Date < @MessedUpAt)
SELECT *
FROM ordered
WHERE ordered.rn = 1
ORDER BY ordered.Date;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

This is an old article, but it shows one way to deal with versioned and backdated data. It's still very applicable. Also a follow-up article showing another mechanism. Those could help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jbeclapez SSC-Addicted Points: 405 More actions · Answer 7

Thanks Phil, you did it!

Please do you mind, explain high level how this works? You probably see that I do not have your level and I am interested in trying to understand that.

WITH ORDERED is new to me. I know order by but not with the WITH statement.

According to what i read, you took another approach and you do not use SubQueries, as it is replaced with WITH statement.

Thanks again Phil!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

jbeclapez wrote:

Thanks Phil, you did it! Please do you mind, explain high level how this works? You probably see that I do not have your level and I am interested in trying to understand that. WITH ORDERED is new to me. I know order by but not with the WITH statement. According to what i read, you took another approach and you do not use SubQueries, as it is replaced with WITH statement. Thanks again Phil!

WITH ORDERED is not a thing, per se 🙂 I could have said WITH CHICKENCHOWMEIN and as long as the SELECT query at the end referenced CHECKINCHOWMEIN instead of ORDERED, all would have been well. The

WITH x as (select some stuff)

Construction is called a Common Table Expression (CTE). In this case, other than the different syntax, it's usage is identical to that of a subquery. I find CTEs easier to write than subqueries, and, in some cases, they can do things which subqueries cannot (recursion, for example).

The ROW_NUMBER() function is where the real magic happens. It allows you to number the rows in the returned resultset according to an order which you specify, but optionally also partitioned (so that the numbering starts at 1 every time the partition changes). It's one of several 'windowing functions' and it's very useful. I suggest you check out the documentation for yourself.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbeclapez SSC-Addicted Points: 405 More actions · Answer 9

Phil, I am sorry, I spoke too quickly.

I see that you DECLARE a fixed time

DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';

But the mechanic can mess up for 2 hours.... so we need to cover that.

DECLARE @MessedUpAt DATETIME BETWEEN '2000-01-03 00:00:00' AND '2000-01-03 15:00:01'

But it does not seem to be working!

jbeclapez SSC-Addicted Points: 405 More actions · Answer 10

Thanks for those explanations Phil. I will look at those CTE. I just asked you a change as the MessedUpTime should be a range!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

jbeclapez wrote:

Phil, I am sorry, I spoke too quickly.
I see that you DECLARE a fixed time DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';
But the mechanic can mess up for 2 hours.... so we need to cover that. DECLARE @MessedUpAt DATETIME BETWEEN '2000-01-03 00:00:00' AND '2000-01-03 15:00:01'
But it does not seem to be working!

Can you explain what effect this has on the results, please? 'Cover that' does not explain the required logic.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbeclapez SSC-Addicted Points: 405 More actions · Answer 12

For example, On the first query I wrote (See below).

SELECT
TAudit.VehicleType
,TAudit.CarID
,TAudit.Piece
FROM dbo.TAudit
WHERE TAudit.Mechanic = 'Mike'
AND TAudit.Date BETWEEN '2000-01-03' AND '2000-01-03 23:59:59'
GROUP BY TAudit.VehicleType
,TAudit.CarID
,TAudit.Piece

In this query i was getting the list of pieces that Mike changed per CarID and VehicleType. I mean by this that during this day, Mike could have messsed up many parts of the car. This query gets the list of the parts he messed up. Then we investigate how it was before on those parts, like you did...

Another example is that Mike could have messed up only for 1 hour '2000-01-03 14:00:00' AND '2000-01-03 16:00:00'

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

So do you want two queries? One showing the situation before mess-up (which is the one already provided) and one showing the mess-up? That is, two separate resultsets?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbeclapez SSC-Addicted Points: 405 More actions · Answer 14

No, I only want to make it in OneQuery.

Remember, i only used this first query to get the list of what was changed during a period from a user per VehicleType and CardID