Count how many days a record has existed in my Table

Question

I have a table with different products that gets registered constantly into different locations. I want to know for how long this product existed since the first scan it had and where it's current location is.

here is an example:

CREATE TABLE Products
(
ProductNR varchar (14),
Location int,
Scanned Datetime
);
Insert Into Products(ProductNR, Location, Scanned)
Values('1111', 1, '2017-03-15 09:30'), 
      ('1111', 2, '2017-03-16 11:35'), 
      ('1111', 3, '2017-03-21 12:37'), 
      ('2222', 1, '2017-03-21 09:50'),
      ('2222', 5, '2017-03-21 12:58');

Expected output:

-----------------------------------------------------
| ID           | Current Location | Age
-----------------------------------------------------
| 1111         |  3               | > 5 days
| 2222         |  5               | < 5 days
-----------------------------------------------------

My current query

select ProductNR, 
         Location,
         Scanned,
         case When Scanned >= DATEADD(day, -5, GETDATE()) Then 'Less than 5 days old' 
              When scanned <= DATEADD(day, -5, GETDATE()) Then 'More than 5 days old' else '0' end AS Age    
  From Products

I think this Query is completely wrong and it's also showing every record, I spent ages trying to figur out how to write this correctly. I hope someone can help

May i suggest you make a new column named `createdAt`. You can just get that value and calculate the "age" of a record. many frameworks work with columns like `created_at`, `updated_at`, etc. Maybe you will need values like these more along the way. — Rein, Mar 22 '17 at 10:30
It's actually already added!, just didn't included on my example :) — pancake, Mar 22 '17 at 11:17

score 3 · Accepted Answer · answered Mar 22 '17 at 10:32

3

Try this query:

select p.ProductNR, p.Location, p.Scanned
    ,case
        when p.FirstScanned >= dateadd(day, -5, getdate()) then 'Less than 5 days old'
        when p.FirstScanned <= dateadd(day, -5, getdate()) then 'More than 5 days old'
        else '0'
     end as Age
from
(
    select p.ProductNR
        ,p.Location
        ,p.Scanned
        ,min(p.Scanned) over (partition by p.ProductNR) as FirstScanned
        ,max(p.Scanned) over (partition by p.ProductNR) as LastScanned
    from Products p
) p
where p.LastScanned = p.Scanned

answered Mar 22 '17 at 10:32

oryol

5,178
2
23
18

You sir saved my life – pancake Mar 22 '17 at 10:39
Can I ask how to only count working days? minus saturday and sunday? – pancake Mar 27 '17 at 13:10
http://stackoverflow.com/questions/252519/count-work-days-between-two-dates – oryol Mar 27 '17 at 13:37

score 2 · Answer 2 · answered Mar 22 '17 at 10:32

Can you try this?

SELECT PRODUCTNR, CASE WHEN DATEDIFF(dd,MIN_DATE, SCANNED) > 5 THEN 'More than 5 days old' eLSE 'Less than 5 days old' END AS LAST_SCANN
FROM (
SELECT PRODUCTNR, LOCATION, SCANNED , ROW_NUMBER() OVER(PARTITION BY PRODUCTNR ORDER BY SCANNED DESC) AS RN
    , MIN(SCANNED) OVER(PARTITION BY PRODUCTNR ) AS MIN_DATE
FROM      PRODUCTS
    ) X WHERE RN=1;

Count how many days a record has existed in my Table

2 Answers2