Gaps in Date Ranges

Question

I have some rental information that includes the date a rental started and a possible stop date (if no stop we assume still on rent). I also have invoice information about these items that includes the start and stop of the billing cycle for each invoice for that item.

Example Data:

Rental Contract | Item | Rental Start | Rental Stop | Invoice Number | Bill Start | Bill Stop 
1234            |A     | 4/1/2019     |             | 4444           | 4/1/2019   | 4/30/2019  
1234            |A     | 4/1/2019     |             | 4445           | 5/1/2019   | 5/31/2019
1234            |A     | 4/1/2019     |             | 4444           | 6/5/2019   | 6/30/2019
1234            |B     | 4/1/2019     | 5/15/2019   | 4445           | 5/1/2019   | 5/15/2019

I want to return that there is a missed period from 6/1 - 6/4 on item A and a missed period from 4/1 - 4/30 on item B. What I need to do is return a data set that returns the rows above but have the missing rows inserted so they can be highlighted on the final report.

This is what I need to return:

Rental Contract | Item | Rental Start | Rental Stop | Invoice Number | Bill Start | Bill Stop    
1234            |A     | 4/1/2019     |             | 4444           | 4/1/2019   | 4/30/2019     
1234            |A     | 4/1/2019     |             | 4445           | 5/1/2019   | 5/31/2019    
1234            |A     | 4/1/2019     |             |                | 6/1/2019   | 6/4/2019    
1234            |A     | 4/1/2019     |             | 4444           | 6/5/2019   | 4/30/2019    
1234            |B     | 4/1/2019     | 5/15/2019   |                | 4/1/2019   | 4/30/2019    
1234            |B     | 4/1/2019     | 5/15/2019   | 4445           | 5/1/2019   | 5/15/2019

Edit:

So this is what I have tried. I can narrow down to the rows I know have issues and see the gap. I just need to work the data some more to change these to show what the missing start and stop should be and then union that back to the original data. I just couldn't wrap my head around it the other day and seeing the answers provided here helped me out. Thanks everyone.

DECLARE @TicketNumber AS DECIMAL(19, 6) = 109138;

WITH rentals
    AS (SELECT DTH.Rental_Ticket
                                                                            --,DTH.Order_Date
                                                                            --,DTH.Customer_Code
                                                                            --,DTH.Customer_Name
                                                                            --,DTH.Last_Invoice
              ,DTL.Inventory_Item_ID
                                                                            --,DTL.Line_Description AS Ticket_Line_Description
                                                                            --,DTL.Delivery_Date
                                                                            --,DTL.Original_Start_Rent_Date
              ,COALESCE(DTL.Original_Start_Rent_Date, DTL.Delivery_Date, DTH.Order_Date) AS Rental_Start_Date
                                                                            --,dtl.Rental_Stop_Date
                                                                            --,dtl.Return_Date
              ,COALESCE(DTL.Rental_Stop_Date, DTL.Return_Date) AS Rental_Stop_Date
              ,IL.Invoice_Number
              ,LAG(IL.Rental_Stop_Date) OVER (PARTITION BY DTH.Rental_Ticket
                                                          ,DTL.Inventory_Item_ID
                                              ORDER BY DTH.Rental_Ticket
                                                      ,DTL.Inventory_Item_ID
                                                      ,IL.Billing_Cycle_Period_Start_DateTime
                                                      ,IL.Rental_Stop_Date) AS Prev_Stop_Bill_Date
              ,IL.Billing_Cycle_Period_Start_DateTime AS Billing_Start_Date --Invoice_Billing_Start
              ,IL.Rental_Stop_Date AS Billing_Stop_Date                     --Invoice_Billing_Stop
              ,LEAD(IL.Rental_Stop_Date) OVER (PARTITION BY DTH.Rental_Ticket
                                                           ,DTL.Inventory_Item_ID
                                               ORDER BY DTH.Rental_Ticket
                                                       ,DTL.Inventory_Item_ID
                                                       ,IL.Billing_Cycle_Period_Start_DateTime
                                                       ,IL.Rental_Stop_Date) AS Next_Start_Bill_Date

        --,il.Billing_Logic_Itype
        --,il.Line_Description AS Invoice_Line_Description
        --,il.Gross_Line_Pre_Tax_Total
        FROM dbo.CSView_DEL_Ticket_Header_Master AS DTH
            LEFT JOIN dbo.CSView_DEL_Ticket_Lines_Master AS DTL
                ON DTH.Rental_Ticket = DTL.Rental_Ticket
            LEFT JOIN dbo.CSView_INVC_Lines_Master AS IL
                ON DTL.Rental_Ticket = IL.Rental_Ticket_or_Tag_Number
                    AND DTL.Rental_Ticket_Unique_Line_ID = IL.Rental_Ticket_Unique_Line_ID_or_Tag_OrigCounter
        WHERE DTH.Ticket_Type IN ('D', 'T')
            AND DTH.Rental_Ticket NOT IN
                  (SELECT DV.dticket
                   FROM dbo.deltick_void AS DV)
            AND IL.Billing_Logic_Itype <> 'C'
            -- AND DTH.Order_Date BETWEEN '4/1/2018' AND '9/20/2018'
            AND DTH.Rental_Ticket = @TicketNumber)
SELECT R.Rental_Ticket
      ,R.Inventory_Item_ID
      ,R.Rental_Start_Date
      ,R.Rental_Stop_Date
      ,R.Invoice_Number
      ,R.Prev_Stop_Bill_Date
      ,R.Billing_Start_Date
      ,R.Billing_Stop_Date
      ,R.Next_Start_Bill_Date
FROM rentals AS R
WHERE (R.Rental_Start_Date <> R.Billing_Start_Date
          AND R.Prev_Stop_Bill_Date IS NULL) --first billing where there is a gap between rental start and bill start
    OR (DATEADD(DAY, 1, R.Prev_Stop_Bill_Date) <> R.Billing_Start_Date) --where there is a gap between last billing stop and current billing start
ORDER BY R.Rental_Ticket
        ,R.Inventory_Item_ID
        ,R.Billing_Start_Date
        ,R.Billing_Stop_Date

Answer 1

If I understand correctly, you can get the gaps using:

select contract, item, billstart, dateadd(day, -1, rentalstart) as billend
from t
where billstart < rentalstart
union all
select contract, item, dateadd(day, 1, rentalend) as billstart, billend
from t
where billend > rentalend;

You can then union in the original data and choose the columns you actually need to get your final result set.

Answer 2

I think this will do it. Comments inline.

declare @Rentals table (contract int, item varchar(1), rentstart date, rentstop date, invoice int, billstart date, billstop date)

insert into @Rentals
    values (1234,'A','2019-04-01',null,4444,'2019-04-01','2019-04-30')
        ,(1234,'A','2019-04-01',null,4445,'2019-05-01','2019-05-31')
        ,(1234,'A','2019-04-01',null,4444,'2019-06-05','2019-06-30')
        ,(1234,'B','2019-04-01','2019-05-15',4445,'2019-05-01','2019-05-15')

-- Start with the known data
select *
from @rentals r

union all

( -- union all to add the gaps in the bills
    select contract,item,rentstart,rentstop,null invoice,dateadd("dd",1,billstop) billstart, dateadd("dd",-1,nextstart) billstop
    from (  -- use the min billstart to get the next closest start after the bill end
        select (select min(r2.billstart) from @rentals r2 where r2.item=r.item and r2.contract=r.contract and r2.billstart > r.billstop) nextstart,*
        from @rentals r
    ) a
    where dateadd("dd",1,billstop) <> nextstart -- there is no gap if the next bill starts the day after the current bill ends
)

union all

( -- union all to add the gaps from rental start to first bill
    select r.contract,r.item,rentstart,rentstop,null invoice,rentstart,dateadd("dd",-1,start.billstart)
    from @rentals r
    inner join ( -- min billstart is the first time billed
        select contract,item,min(billstart) billstart
        from @rentals
        group by contract,item
    ) start
        on start.billstart <> r.rentstart -- we don't need the ones when billing started at the same time as rent
        and start.item=r.item
        and start.contract=r.contract
)
order by contract,item,billstart

output:

contract    item    rentstart   rentstop    invoice billstart   billstop
1234        A       2019-04-01  NULL        4444    2019-04-01  2019-04-30
1234        A       2019-04-01  NULL        4445    2019-05-01  2019-05-31
1234        A       2019-04-01  NULL        NULL    2019-06-01  2019-06-04
1234        A       2019-04-01  NULL        4444    2019-06-05  2019-06-30
1234        B       2019-04-01  2019-05-15  NULL    2019-04-01  2019-04-30
1234        B       2019-04-01  2019-05-15  4445    2019-05-01  2019-05-15