CODEWITHSUNDEEP
sqldate-arithmetic
Jenna
Jenna

Reputation: 75

How to get calculations from two rows

I started learning SQL recently and would like to know if it is possible to do the calculations as below.

Basically my table looks like this:

id    Date           Fill_nbr     
 1   01/01/2015       30      
 1   02/05/2015       30      
 1   03/02/2015       30      
 1   07/01/2015       30      
 1   07/26/2015       30      
 2   03/01/2015       30      
 ....

And I'd like to create a table like this:

 id  Date             Fill_nbr     Date_last      Gap    First
 1   01/01/2015       30           01/30/2015      0         1
 1   02/05/2015       30           03/04/2015      5         0
 1   03/02/2015       30           03/31/2015      0         0
 1   07/01/2015       30           07/30/2015      91        1
 1   07/26/2015       30           08/24/2015      0         0
 2   03/01/2015       30           03/30/2015      0         1
 ....

The rule for column 'Date_last' is Date_last = Date + fill_nbr which is easy to get.

The difficult part for me is the 'Gap' part. The rules are:

The rule for column 'First':

Upvotes: 1

Views: 63

Answers (2)

Jenna
Jenna

Reputation: 75

Thanks, Jason! I figured it out that the LAG or LEAD functions would work for this problem. So here is my solution which is similar with yours. Thanks again for your input!

select
   id,
   date,
   fill_nbr,
   date + fill_nbr - 1 AS date_last,
   LAG(date_last) OVER (PARTITION BY id OREDER by id, date) LagV,
   date - LagV - 1 as gap,
   ROW_NUMBER() OVER(PARTITION BY id IRDER BY id, date) AS rk,
   CASE
       WHEN (gap>30 or rk=1) then '1'
       ELSE '0'
   END AS first 
FROM table;

Upvotes: 0

Jason W
Jason W

Reputation: 13209

Looks like this question is about abandoned already since important details are still missing...thought it'd be interesting to at least find a solution. The solution below works for SQL Server 2012 or higher since it uses LAG.

SELECT
  id,
  [Date],
  Fill_nbr,
  (CASE WHEN LAG (DATEADD(DD, Fill_nbr - 1, [Date]), 1, NULL) OVER (
    PARTITION BY id ORDER BY [Date]) > [Date] THEN 0 ELSE
      COALESCE(DATEDIFF(DD, LAG (DATEADD(DD, Fill_nbr - 1, [Date]), 1, NULL) OVER (
      PARTITION BY id ORDER BY [Date]), [Date]) - 1, 0) END) AS Gap,
  DATEADD(DD, Fill_nbr - 1, [Date]) AS Date_last,
  CASE WHEN DATEPART(DD, [Date]) = 1 THEN 1 ELSE 0 END AS [First]
FROM Records

SQL Fiddle: http://sqlfiddle.com/#!6/a9b68/8

Upvotes: 2

Related Questions