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?

March 2, 2022 at 5:06 pm

This was removed by the editor as SPAM

Phil Parkin

SSC Guru

Points: 246988

March 5, 2022 at 3:07 pm

Answer

#3993983

Please try using my modified query and see whether it gives the results you are hoping for:

WITH AffectedVehicles
AS (SELECT DISTINCT
           ta.VehicleType
          ,ta.CarID
          ,ta.Piece
    FROM #TAudit ta
    WHERE ta.Mechanic = @Mechanic
          AND ta.Date >= @MessedUpStart
          AND ta.Date <= @MessedUpEnd)
    ,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
        JOIN AffectedVehicles av
            ON av.CarID = ta.CarID
               AND av.Piece = ta.Piece
               AND av.VehicleType = ta.VehicleType
    WHERE ta.Date < @MessedUpStart)
SELECT Note = 'Before Mess-up'
      ,ordered.VehicleType
      ,ordered.CarID
      ,ordered.Mechanic
      ,ordered.[Use]
      ,ordered.Piece
      ,ordered.Week
      ,ordered.Value1
      ,ordered.Value2
      ,ordered.Date
FROM ordered
WHERE ordered.rn = 1
UNION ALL
SELECT Note = 'During mess-up'
      ,ta.VehicleType
      ,ta.CarID
      ,ta.Mechanic
      ,ta.[Use]
      ,ta.Piece
      ,ta.Week
      ,ta.Value1
      ,ta.Value2
      ,ta.Date
FROM #TAudit ta
WHERE ta.Mechanic = @Mechanic
      AND ta.Date >= @MessedUpStart
      AND ta.Date <= @MessedUpEnd
UNION
SELECT Note = 'Before mess-up'
      ,ta.VehicleType
      ,ta.CarID
      ,''
      ,''
      ,ta.Piece
      ,-1
      ,-1
      ,-1
      ,'19000101'
FROM AffectedVehicles ta
WHERE NOT EXISTS
(
    SELECT 1
    FROM ordered o
    WHERE o.VehicleType = ta.VehicleType
          AND o.CarID = ta.CarID
          AND o.Piece = ta.Piece
)
ORDER BY ordered.Date;

(Remember that the statement preceding the WITH must be terminated by a semicolon.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 11 posts - 16 through 25 (of 25 total)

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

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

jbeclapez wrote:

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

Here is a direct quote from your initial post:

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...

My query returns the 3 lines in green, which is what you asked for. I do not have a clue what results you wish to see with this mess-up window complication. The engine requirement is something I asked about earlier, and I don't think you provided any response.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

Phil Parkin wrote:

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?

Phil, I think I get it. I used 2 DECLARE like this (below). To be honest it was a bit of a gamble, but it works.

I would like to thank you for your work.

Have a great day.

DECLARE @Mechanic VARCHAR(4) = 'Mike';
DECLARE @MessedUpStart DATETIME = '2000-01-03 14:00:00';
DECLARE @MessedUpEnd DATETIME = '2000-01-03 16: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 dbo.TAudit ta
WHERE ta.Mechanic = @Mechanic
AND ta.Date < @MessedUpEnd
AND ta.Date >@MessedUpStart)
SELECT *
FROM ordered
WHERE ordered.rn = 1
ORDER BY ordered.Date;

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

Phil Parkin wrote:

jbeclapez wrote:
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
Here is a direct quote from your initial post:
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...
My query returns the 3 lines in green, which is what you asked for. I do not have a clue what results you wish to see with this mess-up window complication. The engine requirement is something I asked about earlier, and I don't think you provided any response.

Sorry Phil, I did not see this post as it was on another page. I will try to answer your questions. I am still working on all this and will continue tomorrow...

1- Regarding the engine. When a mechanic start a work on a vehicle and he changes the engine, he logs his work and says he changed the engine. Now we want to know if someone else worked on this engine before, and if no one did then it is the "default" engine and therefore it wont show in the logs.

2- Regarding the time window : The idea I had that if a mechanic messed up for 3 hours, i need to check the logs and see all items he messed up. Then i look in the history and retrieve the properties of the vehicle before he messed up. In the original table, I should not have logged all the changes at the same precise time. In reality, at 14h he changes the engine and at 17h he changes the tires. So I need to know that from 14h to 17h... and this for all vehicle types and id.

I will continue to anlyse my query and confirm tomorrow if it is really good!

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

Hi Phil,

I discovered another issue.

Mike messed up at a certain time. So we need to find how it was before he messed up the car + vehicleid + piece. The trick is that any othher mechanic could have worked on the car before him. And that is where it is not working now. It only shows the previous work of Mike.

CREATE TABLE TAudit
    ([VehicleType] varchar(100), [CarID] int, [Mechanic] varchar(100), [Use] varchar(1), [Piece] varchar(100), [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, 'Peter', 'Y', 'break', 1, 104, 1, '2000-01-01 08:00:00'),
    ('Car', 100, 'Peter', 'Y', 'Tire', 1, 105, 1, '2000-01-01 08:00:00'),
    ('Car', 100, 'Peter', 'Y', 'break', 1, 104, 204, '2000-01-01 09:00:00'),
    ('Car', 100, 'Peter', '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')
;

DECLARE @Mechanic VARCHAR(100) = 'Mike';
--DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';
--DECLARE @MessedUpAt DATETIME = '2000-01-03 15:00:00';
DECLARE @MessedUpStart DATETIME = '2000-01-03 14:00:00';
DECLARE @MessedUpEnd DATETIME = '2000-01-03 16: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 dbo.TAudit ta
WHERE ta.Mechanic = @Mechanic
--AND ta.Date < @MessedUpAt)
AND ta.Date < @MessedUpEnd
AND ta.Date >@MessedUpStart)

SELECT *
FROM ordered
WHERE ordered.rn = 1
ORDER BY ordered.Date;

This query retrieves that - a listing of what Mike messed up during his messed-up period:

result3

It is a good start but now that we have the VehicleType + CardID + Piece we need to go back in the log and check the value of those combination in the past.

The resulting query should bring the lines in green + 1 line for the engine that was never changed - so not in the past logs.

I hope it helps.

Thanks

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

I had a quick go at this, see whether it helps.

DECLARE @Mechanic VARCHAR(4) = 'Mike';
DECLARE @MessedUpStart DATETIME = '2000-01-03 14:00:00';
DECLARE @MessedUpEnd DATETIME = '2000-01-03 16:00:00';

DROP TABLE IF EXISTS #TAudit;

CREATE TABLE #TAudit
(
    VehicleType VARCHAR(100) NOT NULL
   ,CarID INT NOT NULL
   ,Mechanic VARCHAR(100) 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, 'Peter', 'Y', 'break', 1, 104, 1, '2000-01-01 08:00:00')
,('Car', 100, 'Peter', 'Y', 'Tire', 1, 105, 1, '2000-01-01 08:00:00')
,('Car', 100, 'Peter', 'Y', 'break', 1, 104, 204, '2000-01-01 09:00:00')
,('Car', 100, 'Peter', '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 AffectedVehicles
AS (SELECT DISTINCT
           ta.VehicleType
          ,ta.CarID
          ,ta.Piece
    FROM #TAudit ta
    WHERE ta.Mechanic = @Mechanic
          AND ta.Date >= @MessedUpStart
          AND ta.Date <= @MessedUpEnd)
    ,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
        JOIN AffectedVehicles av
            ON av.CarID = ta.CarID
               AND av.Piece = ta.Piece
               AND av.VehicleType = ta.VehicleType
    WHERE ta.Date < @MessedUpStart)
SELECT Note = 'Before Mess-up'
      ,ordered.VehicleType
      ,ordered.CarID
      ,ordered.Mechanic
      ,ordered.[Use]
      ,ordered.Piece
      ,ordered.Week
      ,ordered.Value1
      ,ordered.Value2
      ,ordered.Date
FROM ordered
WHERE ordered.rn = 1
UNION ALL
SELECT Note = 'During mess-up'
      ,ta.VehicleType
      ,ta.CarID
      ,ta.Mechanic
      ,ta.[Use]
      ,ta.Piece
      ,ta.Week
      ,ta.Value1
      ,ta.Value2
      ,ta.Date
FROM #TAudit ta
WHERE ta.Mechanic = @Mechanic
      AND ta.Date >= @MessedUpStart
      AND ta.Date <= @MessedUpEnd
ORDER BY ordered.Date;

It's the union of two separate queries, one which identifies everything which Mike worked on during the mess-up period and one which identifies the latest rows, prior to mess-up start, for everything which Mike worked on during his mess-up period.

It also uses a 'cascading CTE' – the second CTE references results from the first.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

Brilliant Phil! Thanks (again) for your work! Appreciated.

I am just curious on how long it too you to solve this? Are we talking hours?

Is there a possibility to show in the BEFORE MESS UP the engine? As you see from the original data, Mike changed the engine but it was never changed in the past. If possible I would like to see an empty line with Vehicle Type + CarID + Piece with other fields empty. Just to show it was changed but in the past it was the default engine?

Phil Parkin SSC Guru Points: 246988 More actions · Answer 7

I should get a chance to look at this again tomorrow regarding the additional engine requirement.

Didn't take long, maybe 30 minutes in total for both solutions.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

30 minutes!! God I am far from that... and even with hours on it, I do not get the right result. Pretty amazing... Anyway, i think i understand your approach now :-). Thanks Phil and have a good week end!

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

jbeclapez

SSC-Addicted

Points: 405

March 20, 2022 at 5:49 pm

#4002839

Thanks Phil! Appreciated.