Subtract dates for the same serial number in Access

Question

Hi I need an SQL code for subtracting dates for MsAccess

For instance

serialNumber    serviceWoNumber createdDate type    Results should be
900017672280000 1700028713      17.01.2017   CD 
731127155164500 1600729135      14.12.2016   CD     =14.12.2016-7.12.2016=7
731127155164500 1600712441      7.12.2016    CD     =7.12.2016-5.12.2016=2

Thank you very much for any help.

Answer 1

I'm guessing we are missing some data in the question, but I think I understand your question.

You have a table containing records using the above setup you described (serialNumber, serviceNumber, createdDate, and Type). You want to check how long since the last date an item has been serviced.

You will need to perform some form of a self-join of the table, and then find the use DATEDIFF to perform the calculation. It'd look something like the following (pseudo-code):

SELECT
    a.serialNumber, DATEDIFF('d', a.createdDate, b.createdDate) AS serviceInterval
FROM
    myTable a
    LEFT JOIN myTable b ON a.serialNumber = b.serialNumber 
    AND a.createdDate > b.createdDate

You perform the self-join on the serial number, and the component in a being serviced later in the dataset aliased as a than in b.

This does not take into account items you never serviced before, so probably you need to add some data to deal with NULL values in dataset b.

Let me know if I totally misunderstood your issue.

**** Update following comment ****

Following your comment that you'd like to add the subtractionresults, you could use something like the following (Assuming you added a column called serviceInterval:

UPDATE myTable updateMe
SET 
    updateMe.serviceInterval = a.serviceInterval
FROM
    myTable a
    LEFT JOIN myTable b ON a.serialNumber = b.serialNumber 
    AND a.createdDate > b.createdDate
WHERE
    updateMe.serialNumber = a.serialNumber