CODEWITHSUNDEEP
sql-serverdatetimecase
pancake
pancake

Reputation: 600

Count how many days a record has existed in my Table

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

Upvotes: 0

Views: 51

Answers (2)

oryol
oryol

Reputation: 5248

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

Upvotes: 3

etsa
etsa

Reputation: 5060

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;

Upvotes: 2

Related Questions