How to count days between 2 rows multiple scenarios

Question

I would like to count days from table like this:

[ enter image description here ]]1

I have 2 columns with status and date, I need to count how many days one row had status with 1 and 3, for example: first row has staus 1 second row has status 2 the difference between those two rows is 3 days, the same eight and nine row. All those calculation I neet to do in sql.

Most people here want formatted text, not images (or links to images.) — jarlh, Feb 16 '18 at 08:43

gpeche · Answer 1 · 2018-02-16T09:23:42.687

You can do that if you have analytic (aka window) functions available (Oracle, SQL Server, Postgresql, MySQL). The basic idea would be (using Oracle SQL dialect):

select
sum(trunc(next_date) - trunc(dat))
from (
select 
  status,
  dat,
  lead(dat) over (order by dat asc) next_date
from
      (  -- This is a "fake" data table
      select 1 as status, to_date('15/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 2 as status, to_date('18/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 3 as status, to_date('20/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 2 as status, to_date('23/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 4 as status, to_date('24/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 7 as status, to_date('27/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 9 as status, to_date('28/2/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 3 as status, to_date('1/3/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 14 as status, to_date('4/3/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 15 as status, to_date('7/3/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 1 as status, to_date('10/3/2018' ,'dd/mm/yyyy') dat from dual
      union all
      select 2 as status, to_date('14/3/2018' ,'dd/mm/yyyy') dat from dual
      ) d
  ) d2
where
  status in (1, 3);

Other dialects may be slightly different but the idea is the same.

unfortunetelly lead is not recognized function name, and I am not db administrator to do this. I am using MS SQL Server 2017 — Bogusław Boczkowski, Feb 16 '18 at 09:16
@BogusławBoczkowski: SQL Server 2017 absolutely supports the `lead()` function: https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql — , Feb 16 '18 at 09:20

score 0 · Accepted Answer · answered Feb 16 '18 at 10:01

This will work for sql-server 2012. I was unable to find any documentation that would prevent this syntax in express.

DECLARE @t table(Status int, Date datetime)
INSERT @t
VALUES
(1,'2018-02-15'),
(2,'2018-02-18'),
(3,'2018-02-20'),
(2,'2018-02-23')

;WITH CTE as
(
  SELECT Date, lead(Date)over(order by date) NextDate, Status
  FROM @t
)
SELECT sum(DateDiff(day, Date, NextDate))
FROM CTE
WHERE Status in (1,3)

How to count days between 2 rows multiple scenarios

2 Answers2