CODEWITHSUNDEEP
sqlsql-server
Vasiliy
Vasiliy

Reputation: 35

How to get maximal dates period

I have a query witch providing me the results, some thing like this:

CREATE TABLE t1 (
   contractId INT  NOT NULL 
  ,recordId   INTEGER  NOT NULL
  ,amount     NUMERIC(7,2) NOT NULL
  ,amountType INTEGER  NOT NULL
  ,date       datetime NOT NULL
);
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3248627,1444.00,266,'2008-02-27 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3248627,471.78,49622,'2008-02-27 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3321672,244.00,266,'2008-03-13 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3321672,0.00,49622,'2008-03-13 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3414133,765.00,266,'2008-04-11 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3414133,251.81,49622,'2008-04-11 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3555690,0.00,266,'2008-07-02 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,3555690,0.00,49622,'2008-07-02 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4229093,382.63,266,'2008-11-04 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4229093,28.51,49622,'2008-11-04 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4259641,382.63,266,'2008-11-05 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4259641,28.51,49622,'2008-11-05 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4291553,382.63,266,'2008-11-06 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4291553,28.51,49622,'2008-11-06 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4450231,5071.63,266,'2009-08-18 00:00:00.000');
INSERT INTO t1 (contractId,recordId,amount,amountType,date) VALUES (1,4450231,1201.67,49622,'2009-08-18 00:00:00.000');

Here we could see that we have two period with overdue amounts. The next query will represent it:

select  
  * 
  , (case when (SUM(amount) over(partition by contractId, recordId)) > 0 then 1 else 0 end) as [isOverdue]
from
  t1

You can try it here

So, I need to get the greatest period of overdue. Some thing like this:

contractId dateSatrt dateEnd days
1 2008-02-27 00:00:00.000 2008-04-11 00:00:00.000 datediff...
1 2008-11-04 00:00:00.000 2009-08-18 00:00:00.000 datediff...

From here i can get what i need easily.

This is my second attempt to get what I need. Here is my previous question. Here I am trying to be more clearly and accurate. Please, don't judge me too hard if I am doing something wrong.

Upvotes: 1

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If I understand correctly, this is a gaps-and-islands problem, where you are counting where your calculated sum is greater than 0.

You can define the islands by the count of the rows that are 0 (these are the same for the non-0 values). This looks like:

select contractid, min(date), max(date), datediff(day, min(date), max(date)) as days_diff
from (select t1.*, sum(1 - isOverdue) over (partition by contractId order by date) as island
      from (select t1.*,
                   (case when (SUM(amount) over(partition by contractId, recordId)) > 0 then 1 else 0 end) as isOverdue
            from t1
           ) t1
     ) t1
where isOverdue = 1
group by contractid, island;

Here is a db<>fiddle.

Upvotes: 2

Related Questions