How to agregate according to some timeframe
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
sql
sql-server aggregate-functions asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
add a comment |
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
sql
sql-server aggregate-functions asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
add a comment |
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
sql
sql-server aggregate-functions asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
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
sql
sql-server aggregate-functions sql
sql-server aggregate-functions asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
edited Nov 13 '18 at 12:57
Mathias F
asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
asked Nov 13 '18 at 10:56
Mathias FMathias F
8,7841769127
8,7841769127
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279474%2fhow-to-agregate-according-to-some-timeframe%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
add a comment |
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.
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
add a comment |
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.
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
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.
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
answered Nov 13 '18 at 11:53
Gordon LinoffGordon Linoff
772k35306407
772k35306407
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
add a comment |
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
That works perfectly
– Mathias F
Nov 13 '18 at 12:56
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53279474%2fhow-to-agregate-according-to-some-timeframe%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
