SQL How to link lowest row from another select

I searching since a week for this problem. I try many code and I’m not able to get a correct answer, here’s the problem : I have a table of seller that have each time a customer contact this seller, if he sold something or not.

Table Seller (I will put Data for one seller only to gives you a more specific problem)

• SellerID – phonecalldate – Sequence – Selling (Bool)
• 100 – 2018-01-05 – 1 – 0
• 100 – 2018-01-05 – 2 – 0
• 100 – 2018-01-05 – 3 – 1
• 100 – 2018-01-05 – 4 – 0
• 100 – 2018-01-05 – 5 – 0
• 100 – 2018-01-10 – 1 – 0
• 100 – 2018-01-10 – 2 – 0
• 100 – 2018-01-10 – 3 – 0
• 100 – 2018-01-10 – 4 – 1
• 100 – 2018-01-10 – 5 – 0
• 100 – 2018-01-10 – 6 – 0
• 100 – 2018-01-10 – 7 – 0
• 100 – 2018-01-10 – 8 – 0
• 100 – 2018-01-10 – 9 – 0
• 100 – 2018-01-10 – 10 – 0

I want : how many times he didn’t sell between phone calls. I’m supposed to have in this model 3 answer:

1- a sequence of 2 during  2018-01-05 sequence 1 to  2018-01-05 sequence 2
2- a sequence of 5 during  2018-01-05 sequence 4 to  2018-01-10 sequence 3 
2- a sequence of 6 during  2018-01-10 sequence 5 to  2018-01-10 sequence 10

What I have done right now is : I try to find boundary and get the count between these 2 boundary (I know that the database start 2018-01-05) So I use 2018-01-01 as a lowest date for the lowest boundary And in the left join I use getdate()+1 to have the tomorrow date as the highest boundary:

Select '2018-01-01' as Date1, SellerID as Seller1, 0 as Sequence1  from Seller  Group by Seller
Union ALL
Select phonecalldate as Date1, SellerID as Seller1, 0 as Sequence1 from Seller  Where Selling = 'true' group by phonecalldate , SellerID , Sequence
) as Boundary1
Left JOIN (
Select * from (
Select getdate()+1 as Date2, SellerID as Seller2, 0 as Sequence2 from Seller    Group by Seller
Union ALL
Select phonecalldate as DateMatch2, SellerID as Seller2, Sequence as Sequence2 from Seller  Where Selling = 'true' ) as Boundary2 
ON Boundary1.Seller1 = Boundary2 .Seller2 and ((Boundary1.Date1 = Boundary2.Date2 and Boundary1.Sequence1 < Boundary2 .Sequence2) or (Boundary1.Date1 < Boundary2.Date2 )) 

This query return:

• (First Query) Left Join ( Second Query)
• 2018-01-01 – 100 – 0 – 2018-01-05 – 100 – 3
• 2018-01-01 – 100 – 0 – 2018-01-10 – 100 – 4
• 2018-01-01 – 100 – 0 – 2019-01-12 – 100 – 0
• 2018-01-05 – 100 – 3 – 2018-01-10 – 100 – 4
• 2018-01-05 – 100 – 3 – 2019-01-12 – 100 – 0
• 2018-01-10 – 100 – 4 – 2019-01-12 – 100 – 0

How to have only the lowest for each Seller1 like this ?

• 2018-01-01 – 100 – 0 – 2018-01-05 – 100 – 3
• 2018-01-05 – 100 – 3 – 2018-01-10 – 100 – 4
• 2018-01-10 – 100 – 4 – 2019-01-12 – 100 – 0

By the same way how to count the total between the 2 dates ???? the result is supposed to gives me (THIS) This is what I’m really looking for!!

• 2018-01-01 – 100 – 0 – 2018-01-05 – 100 – 3 (2)
• 2018-01-05 – 100 – 3 – 2018-01-10 – 100 – 4 (5)
• 2018-01-10 – 100 – 4 – 2019-01-12 – 100 – 0 (6)

Now I have my boundary, I think that I can find how many : Selling = false between my result.

Does someone have a better way to do this query to get this ?

Thanks

Answer

I would like this part to be done by the author of the question.

DECLARE @Seller TABLE (
  SellerID INT,
  phonecalldate DATE,
  [Sequence] INT,
  Selling INT CHECK(Selling IN(0, 1)),
  PRIMARY KEY (SellerID, phonecalldate, [Sequence])
);

INSERT @Seller(SellerID, phonecalldate, [Sequence], Selling)
VALUES
  (100, '20180105', 1, 0),
  (100, '20180105', 2, 0),
  (100, '20180105', 3, 1),
  (100, '20180105', 4, 0),
  (100, '20180105', 5, 0),
  (100, '20180110', 1, 0),
  (100, '20180110', 2, 0),
  (100, '20180110', 3, 0),
  (100, '20180110', 4, 1),
  (100, '20180110', 5, 0),
  (100, '20180110', 6, 0),
  (100, '20180110', 7, 0),
  (100, '20180110', 8, 0),
  (100, '20180110', 9, 0),
  (100, '20180110', 10, 0);

The data necessary to get the desired result can be obtained using the following query:

WITH
  a AS (
    SELECT
      SellerID,
      Selling,
      FORMAT(phonecalldate, 'yyyyMMdd') +
        FORMAT([Sequence], '00000000') AS dump,
      SUM(Selling) OVER
      (
        PARTITION BY SellerID
        ORDER BY phonecalldate, [Sequence]
      ) AS g
    FROM @Seller
  )
SELECT
  COUNT(*) AS PhoneCallQty,
  CAST(LEFT(MIN(dump), 8) AS DATE) AS StartDate,
  CAST(RIGHT(MIN(dump), 8) AS INT) AS StartSeq,
  CAST(LEFT(MAX(dump), 8) AS DATE) AS EndDate,
  CAST(RIGHT(MAX(dump), 8) AS INT) AS EndSeq
FROM a
WHERE Selling = 0
GROUP BY SellerID, g;

Output:

+--------------+------------+----------+------------+--------+
| PhoneCallQty | StartDate  | StartSeq |  EndDate   | EndSeq |
+--------------+------------+----------+------------+--------+
|            2 | 2018-01-05 |        1 | 2018-01-05 |      2 |
|            5 | 2018-01-05 |        4 | 2018-01-10 |      3 |
|            6 | 2018-01-10 |        5 | 2018-01-10 |     10 |
+--------------+------------+----------+------------+--------+