smart way to create a master list (avoiding cross joins)
I need to create a Table of date,product and inventory count only for the days inventory 0 , something like this
Date Product store Inv
Jan1 1 1 0
Feb4 1 1 0
From the inventory table that only has a record whenever inventory changes
Like this
Store Product start_date end_date Inv
1 1 Jan 4 Jan10 5
1 1 Jan10 jan 15 4
I know I can create a master table by cross joining all store,product and calendar days in a year and then join only with days where date falls between start and end date of the inventory table. Is there a better way than this ? Can cross join be avoided ? Thanks
sql netezza
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
add a comment |
I need to create a Table of date,product and inventory count only for the days inventory 0 , something like this
Date Product store Inv
Jan1 1 1 0
Feb4 1 1 0
From the inventory table that only has a record whenever inventory changes
Like this
Store Product start_date end_date Inv
1 1 Jan 4 Jan10 5
1 1 Jan10 jan 15 4
I know I can create a master table by cross joining all store,product and calendar days in a year and then join only with days where date falls between start and end date of the inventory table. Is there a better way than this ? Can cross join be avoided ? Thanks
sql netezza
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
I am using netezza
– mezz
Nov 19 '18 at 18:58
1
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30
add a comment |
I need to create a Table of date,product and inventory count only for the days inventory 0 , something like this
Date Product store Inv
Jan1 1 1 0
Feb4 1 1 0
From the inventory table that only has a record whenever inventory changes
Like this
Store Product start_date end_date Inv
1 1 Jan 4 Jan10 5
1 1 Jan10 jan 15 4
I know I can create a master table by cross joining all store,product and calendar days in a year and then join only with days where date falls between start and end date of the inventory table. Is there a better way than this ? Can cross join be avoided ? Thanks
sql netezza
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
I need to create a Table of date,product and inventory count only for the days inventory 0 , something like this
Date Product store Inv
Jan1 1 1 0
Feb4 1 1 0
From the inventory table that only has a record whenever inventory changes
Like this
Store Product start_date end_date Inv
1 1 Jan 4 Jan10 5
1 1 Jan10 jan 15 4
I know I can create a master table by cross joining all store,product and calendar days in a year and then join only with days where date falls between start and end date of the inventory table. Is there a better way than this ? Can cross join be avoided ? Thanks
sql netezza
sql netezza
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
edited Nov 19 '18 at 19:00
a_horse_with_no_name
297k46452547
297k46452547
asked Nov 19 '18 at 18:19
mezzmezz
72129
asked Nov 19 '18 at 18:19
mezzmezz
72129
asked Nov 19 '18 at 18:19
mezzmezz
72129
72129
I am using netezza
– mezz
Nov 19 '18 at 18:58
1
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30
add a comment |
I am using netezza
– mezz
Nov 19 '18 at 18:58
1
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30
I am using netezza
– mezz
Nov 19 '18 at 18:58
I am using netezza
– mezz
Nov 19 '18 at 18:58
1
1
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30
add a comment |
2 Answers
2
active
oldest
votes
Are you looking for lag():
select t.*
from (select t.*,
lag(inventory) over (partition by product, store order by date) as prev_inventory
from t
) t
where prev_inventory is null or prev_inventory <> inventory;
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
add a comment |
Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
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%2f53380513%2fsmart-way-to-create-a-master-list-avoiding-cross-joins%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Are you looking for lag():
select t.*
from (select t.*,
lag(inventory) over (partition by product, store order by date) as prev_inventory
from t
) t
where prev_inventory is null or prev_inventory <> inventory;
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
add a comment |
Are you looking for lag():
select t.*
from (select t.*,
lag(inventory) over (partition by product, store order by date) as prev_inventory
from t
) t
where prev_inventory is null or prev_inventory <> inventory;
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
add a comment |
Are you looking for lag():
select t.*
from (select t.*,
lag(inventory) over (partition by product, store order by date) as prev_inventory
from t
) t
where prev_inventory is null or prev_inventory <> inventory;
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
Are you looking for lag():
select t.*
from (select t.*,
lag(inventory) over (partition by product, store order by date) as prev_inventory
from t
) t
where prev_inventory is null or prev_inventory <> inventory;
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
answered Nov 19 '18 at 18:36
Gordon LinoffGordon Linoff
773k35306408
773k35306408
add a comment |
add a comment |
Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
add a comment |
Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
add a comment |
Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
answered Nov 21 '18 at 1:56
joebjoeb
2,20611519
2,20611519
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
add a comment |
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
could you elaborate your suggestion, please ?
– mezz
Nov 21 '18 at 19:49
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%2f53380513%2fsmart-way-to-create-a-master-list-avoiding-cross-joins%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
I am using netezza
– mezz
Nov 19 '18 at 18:58
1
Please provide a sample set of desired input and output records- February is not mentioned in your input, and the two dates mentioned in your output seems ‘out of the blue’ ?
– Lars G Olsen
Nov 21 '18 at 17:30