Difference between data of two different row belonging to two different columns in SQL

Sample Table:

Waiter      Customer_In_Time    Customer_Out_Time   Date
Pallavi            10:00                12:00        7/5/2020 
Pallavi            13:15                13:50        7/5/2020
Pallavi            15:10                16:35        7/5/2020
Pallavi            10:30                12:00        8/5/2020
Ayush              9:30                 10:45        7/5/2020
Ayush              10:30                12:00        8/5/2020
Ayush              13:45                15:00        8/5/2020

I need a column which calculates the time difference of Free time of a day. Example for Pallavi on 7/5/2020 between first customer out time and second customer in time that is 75 mins and if there is only a single customer for a day then the output for that waiter for that day is 0.

Answer

Use lag() to get the previous out time and then aggregate:

select waiter, date,
       coalesce(sum(datediff(minute, prev_cot, customer_in_time)), 0) as free_minutes
from (select t.*,
             lag(customer_out_time) over (partition by waiter, date order by customer_in_time) as prev_cot
      from t
     ) t
group by waiter, date;

In older, unsupported versions of SQL Server, you can use apply:

select waiter, date,
       coalesce(sum(datediff(minute, prev_cot, customer_in_time)), 0) as free_minutes
from (select t.*,
             tprev.customer_out_time as prev_cot
      from t outer apply
           (select top (1) t2.*
            from t t2
            where t2.waiter = t.waiter and t2.date = t.date and
                  t2.customer_in_time < t.customer_in_time
            order by t2.customer_in_time desc
           ) tprev
     ) t
group by waiter, date;