sql - days between dates based off a different column

Question

so i have been experimenting with OVER and PARTITION BY but have not been successful.

here is what i have: 2 columns; 1 is serial number and will have multiple entries with the same serial number. the 2nd is date.

what i am trying to do is count the days between 2 dates based off of serial number. i can do the datediff easily i cannot figure out how to only calculate the difference between dates for the specific serial numbers.

example: serial number 1234 has date of 1/1/2016, the record below has serial number 1234 and date of 12/30/2015 so the difference is 2 days, the record below THAT has serial number 1234 and a date of 12/27/2015 so the difference is 3 days, but the record below THAT has serial number 4321 and a date of 12/25/2015: the subtraction needs to stop there since that serial number is different and start over looking for the same 4321 serial number.

any help is greatly appreciated.

Answer 1

Your question could be formatted better (more data, less pros)

From what I can gather, does this help?

Declare @YourTable table (Serial int,SomeDate date)
Insert Into @YourTable values
(1234,'2016-01-01'),
(1234,'2015-12-30'),
(1234,'2015-12-27'),
(4321,'2015-12-25')

Select A.*
      ,NbrOfDays = DateDiff(DD,Lag(SomeDate,1,SomeDate) over (Partition By Serial Order By SomeDate),SomeDate)
 From  @YourTable A
 Order By Serial,SomeDate Desc

Returns

Serial  SomeDate    NbrOfDays
1234    2016-01-01  2
1234    2015-12-30  3
1234    2015-12-27  0
4321    2015-12-25  0

Answer 2

SELECT serial_number,
       DATEDIFF( date, 
                 LEAD( date, 1 ) OVER ( PARTITION BY serial_number ORDER BY 
                 date DESC ) 
                ) AS count_days
  FROM table;