How to agregate according to some timeframe

Question

I have a sequence of entries that got invoiced. At invoice creation all items before the date of the invoice which are not invoiced will get the current invoice id.

I now have the list of entries and one of the entries that got invoiced. I need the query that assigns the correct invoiceItemId to the entries.

This is my sample data

    Create table #InvoiceItems 
(
    ID int,
    SegmentID int,
    Created dateTime,
    [Type] int,
    InvoiceItemID int
)

INSERT INTO #InvoiceItems values (1002330   , 1000322,  '2018-05-29 16:06:31.257',  1,  NULL)
INSERT INTO #InvoiceItems values (1002331   , 1000322,  '2018-05-29 16:06:31.257',  2,  NULL)
INSERT INTO #InvoiceItems values (1002332   , 1000322,  '2018-05-29 16:06:31.257',  3,  NULL)
INSERT INTO #InvoiceItems values (1002333   , 1000322,  '2018-05-29 16:06:31.257',  4,  344)
INSERT INTO #InvoiceItems values (1002334   , 1000322,  '2018-05-29 16:06:31.257',  5,  NULL)
INSERT INTO #InvoiceItems values (1002335   , 1000322,  '2018-05-29 16:06:31.257',  6,  NULL)
INSERT INTO #InvoiceItems values (1002370   , 1000322,  '2018-05-30 15:33:48.453',  2,  NULL)
INSERT INTO #InvoiceItems values (1002371   , 1000322,  '2018-05-30 15:33:48.453',  3,  NULL)
INSERT INTO #InvoiceItems values (1002604   , 1000322,  '2018-06-04 10:25:56.603',  3,  NULL)
INSERT INTO #InvoiceItems values (1002605   , 1000322,  '2018-06-04 10:25:56.603',  4,  344)
INSERT INTO #InvoiceItems values (1002685   , 1000369,  '2018-06-05 10:45:53.570',  1,  NULL)
INSERT INTO #InvoiceItems values (1002686   , 1000369,  '2018-06-05 10:45:53.570',  2,  NULL)
INSERT INTO #InvoiceItems values (1002687   , 1000369,  '2018-06-05 10:45:53.570',  3,  NULL)
INSERT INTO #InvoiceItems values (1002688   , 1000369,  '2018-06-05 10:45:53.570',  4,  274)
INSERT INTO #InvoiceItems values (1002689   , 1000369,  '2018-06-05 10:45:53.570',  5,  NULL)
INSERT INTO #InvoiceItems values (1002690   , 1000369,  '2018-06-05 10:45:53.570',  6,  NULL)
INSERT INTO #InvoiceItems values (1002691   , 1000369,  '2018-06-05 12:26:52.153',  3,  NULL)
INSERT INTO #InvoiceItems values (1002692   , 1000369,  '2018-06-05 12:26:52.153',  4,  275)
INSERT INTO #InvoiceItems values (1002761   , 1000369,  '2018-06-06 11:19:11.613',  2,  NULL)
INSERT INTO #InvoiceItems values (1002762   , 1000369,  '2018-06-06 11:19:11.613',  3,  NULL)
INSERT INTO #InvoiceItems values (1002763   , 1000369,  '2018-06-06 11:19:18.503',  2,  NULL)
INSERT INTO #InvoiceItems values (1002764   , 1000369,  '2018-06-06 11:19:18.503',  3,  NULL)
INSERT INTO #InvoiceItems values (1002765   , 1000369,  '2018-06-06 11:20:10.333',  2,  NULL)
INSERT INTO #InvoiceItems values (1002766   , 1000369,  '2018-06-06 11:20:10.333',  3,  NULL)
INSERT INTO #InvoiceItems values (1002767   , 1000369,  '2018-06-06 11:20:10.333',  4,  276)
INSERT INTO #InvoiceItems values (1002768   , 1000369,  '2018-06-06 11:20:32.677',  2,  NULL)
INSERT INTO #InvoiceItems values (1002769   , 1000369,  '2018-06-06 11:20:32.677',  3,  NULL)

select  * from #InvoiceItems order by SegmentID , Created 

DROP table #InvoiceItems

The picture shows how items belong together. An entry with type 4 defines the time when an invoice got generated. The last 2 items (id 1002768, 1002769) did not get invoiced and should not get an InnvoiceItemID. The entries with the id 1002333 and 1002605 have the same InvoiceItemID which is more an edge case. If this make things difficult then please assume its 2 different InvoiceItemIds

This is the expected result

user_1144035 · Answer

You can define the groups with the same invoice id by counting the number of invoice ids with a time larger than any given time.

Once you have the group, then the invoice id is calculated using a window function:

select ii.*, max(InvoiceItemID) over (partition by grp)
from (select ii.*,
             count(InvoiceItemID) over (order by created desc) as grp
      from InvoiceItems ii
     ) ii
order by created;

Here is a db<>fiddle.

How to agregate according to some timeframe

1 Answers