Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Count days Between Two Dates from One Column

I am trying to count the number of days between two dates that are present in the same column! when the ID is are same.

Here is a sample below…


ID       Date    
a       01/12/2024    
a       01/13/2024    
b       01/12/2024    
b       01/20/2024    
b       01/25/2024    
c       01/12/2024    
c       01/12/2024

Counted Days should appear in new column.


ID       Date        Days 
a       01/12/2024    0   
a       01/13/2024    1   
b       01/12/2024    0   
b       01/20/2024    8   
b       01/25/2024    5   
c       01/12/2024    0   
c       01/12/2024    0

Any help is much appreciated (y).

Tags:

2

Answers


  1. 0

    You can do it using window function LAG() to retrieve previous rows and DATEDIFF function to calculate the difference in days between two dates:

    SELECT *, DATEDIFF(dt, LAG(dt, 1, dt) OVER (PARTITION BY ID ORDER BY dt)) as Days 
FROM mytable;

    Demo here

    Login or Signup to reply.
  2. 0

    Does this example help?

    create table #temp(

    id varchar(10) null,
    theDate datetime null
    )
    go

    insert into #temp (id, theDate) values ('a','20240112')
    insert into #temp (id, theDate) values ('a','20240113')
    insert into #temp (id, theDate) values ('b','20240112')
    insert into #temp (id, theDate) values ('b','20240120')
    insert into #temp (id, theDate) values ('b','20240125')
    insert into #temp (id, theDate) values ('c','20240112')
    insert into #temp (id, theDate) values ('c','20240112')


select d.id, d.theDate, case when  case when DATEDIFF(day,LAG(d.theDate) over (order by d.id, d.theDate),d.theDate) < 0 then 0 else  DATEDIFF(day,LAG(d.theDate) over (order by d.id, d.theDate),d.theDate) end is null then '' else case when DATEDIFF(day,LAG(d.theDate) over (order by d.id, d.theDate),d.theDate) < 0 then 0 else  DATEDIFF(day,LAG(d.theDate) over (order by d.id, d.theDate),d.theDate) end end Days
from #temp d


id  theDate                 Days
a   2024-01-12 00:00:00.000 0
a   2024-01-13 00:00:00.000 1
b   2024-01-12 00:00:00.000 0
b   2024-01-20 00:00:00.000 8
b   2024-01-25 00:00:00.000 5
c   2024-01-12 00:00:00.000 0
c   2024-01-12 00:00:00.000 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search