SQL query to find last date a product was checked before it was installed

Question

Post reply

SQL query to find last date a product was checked before it was installed

dcaulton73

SSC Veteran

Points: 286
More actions
February 15, 2021 at 3:38 pm
Go to Answer
#3845265

I have two tables, one covers jobs for a product and the other covers the last time a product was checked over and by who. What I am trying to do is get the most last date the product was checked before it was installed. There is no set time period that the check could be done before the installation, it could be days or it could be hours.
Say lets say table1 (JobsTable) looks like this:
JobNumber JobType ProductType Date
1 Installation ProductA 2020/02/01 13:00
2 Fault ProductA 2020/02/13 12:22
3 Installation ProductB 2021/02/12 09:00
4 Installation ProductA 2021/02/02 08:00
And Table2 (ProductChecks) looks like this:
DateOfCheck ProductType CheckDoneBy
2020/01/01 09:00 ProductA Person1
2020/01/03 10:00 ProductA Person2
2020/01/31 17:00 ProductA Person3
2021/01/12 10:00 ProductA Person1
2021/01/19 10:00 ProductB Person1
2021/01/31 10:00 ProductB Person2
What I need to capture for each installation from table1 is the most recent check done on that product before it was installed. So the results using the examples above should look like this.
JobNumber JobType ProductType Date DateOfCheckBeforeInstallation CheckDoneBy
1 Installation ProductA 2020/02/01 13:00 2020/01/31 17:00 Person3
3 Installation ProductB 2021/02/12 09:00 2021/01/31 10:00 Person2
4 Installation ProductA 2021/02/02 08:00 2021/01/12 10:00 Person1
Does anyone have any ideas have I can achieve this result using a SQL query?
Thanks in advance.

Mark Cowne

One Orange Chip

Points: 26944

February 15, 2021 at 3:51 pm

Answer

#3845267

This should work for you

select j.JobNumber,j.JobType,j.ProductType,j.Date,ca.DateOfCheckBeforeInstallation,ca.CheckDoneBy
from JobsTable j
cross apply(select top 1 p.DateOfCheck,p.CheckDoneBy
            from ProductChecks p
where j.JobType = 'Installation'
  and p.ProductType = j.ProductType
  and p.DateOfCheck < j.[Date]
  order by p.DateOfCheck desc) ca(DateOfCheckBeforeInstallation,CheckDoneBy);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

February 17, 2021 at 6:36 am

This was removed by the editor as SPAM
February 18, 2021 at 6:46 am

This was removed by the editor as SPAM
January 29, 2024 at 8:01 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

Assuming tables are joined on product type you could run through a CTE, use Row_number() to identify the most recent check and return your full list .....

;with res as (

select a.jobtype,b.*
,row_number() over (partition by b.ProductType    order by DateOfCheckdesc) as RN --order descending to ensure most recent =1 
from JobsTable a 
join ProductChecks b on a.ProductType=b.ProductType
where a.JobType='Installation'
)
select * from res
where rn=1

***The first step is always the hardest *******

dcaulton73 SSC Veteran Points: 286 More actions · Answer 2

This has done the trick, many thanks for your help on this. 🙂

dcaulton73 SSC Veteran Points: 286 More actions · Answer 3

Mark Cowne wrote:

This should work for you

select j.JobNumber,j.JobType,j.ProductType,j.Date,ca.DateOfCheckBeforeInstallation,ca.CheckDoneBy
from JobsTable j
cross apply(select top 1 p.DateOfCheck,p.CheckDoneBy
            from ProductChecks p
where j.JobType = 'Installation'
  and p.ProductType = j.ProductType
  and p.DateOfCheck < j.[Date]
  order by p.DateOfCheck desc) ca(DateOfCheckBeforeInstallation,CheckDoneBy);

This has done the trick. Many thanks for your help with this.

Gislason91 Grasshopper Points: 11 More actions · Answer 4

I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio).

I found that in SQL Server 2000 it wasn't possible to check modify date.

Is it possible to check it in SQL Server 2008? Does MS add some new feature in system tables that allow to check it?

My CC Pay

This reply was modified 3 years, 1 month ago by Gislason91.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 5

Gislason91 wrote:

I need to check when function was changed last time

sys.objects gained a [modifed_date] column at some point, but I can't remember which SQL Server version

SELECT *
FROM sys.objects AS O
WHERE O.name = 'YourFunctionName'

will show you all the columns available. If [modifed_date] is one of them then your SQL Version supports that feature 🙂