SQL to get the dates between start date is included and end date is excluded
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
SELECT [order id],
pickingdate
FROM td_order1
WHERE sku = xyz
GROUP BY pickingdate,
[order id]
HAVING pickingdate >= Min(pickingdate)
AND pickingdate <= Max(pickingdate)
ORDER BY pickingdate
Is there anything wrong in this query?
sql
add a comment |
SELECT [order id],
pickingdate
FROM td_order1
WHERE sku = xyz
GROUP BY pickingdate,
[order id]
HAVING pickingdate >= Min(pickingdate)
AND pickingdate <= Max(pickingdate)
ORDER BY pickingdate
Is there anything wrong in this query?
sql
1
pickingdate < Max(pickingdate)
, if you want to exclude end date
– mbharanidharan88
Nov 16 '18 at 11:28
add a comment |
SELECT [order id],
pickingdate
FROM td_order1
WHERE sku = xyz
GROUP BY pickingdate,
[order id]
HAVING pickingdate >= Min(pickingdate)
AND pickingdate <= Max(pickingdate)
ORDER BY pickingdate
Is there anything wrong in this query?
sql
SELECT [order id],
pickingdate
FROM td_order1
WHERE sku = xyz
GROUP BY pickingdate,
[order id]
HAVING pickingdate >= Min(pickingdate)
AND pickingdate <= Max(pickingdate)
ORDER BY pickingdate
Is there anything wrong in this query?
sql
sql
edited Nov 16 '18 at 11:26
mbharanidharan88
4,11542455
4,11542455
asked Nov 16 '18 at 11:24
Teja Teja
1
1
1
pickingdate < Max(pickingdate)
, if you want to exclude end date
– mbharanidharan88
Nov 16 '18 at 11:28
add a comment |
1
pickingdate < Max(pickingdate)
, if you want to exclude end date
– mbharanidharan88
Nov 16 '18 at 11:28
1
1
pickingdate < Max(pickingdate)
, if you want to exclude end date– mbharanidharan88
Nov 16 '18 at 11:28
pickingdate < Max(pickingdate)
, if you want to exclude end date– mbharanidharan88
Nov 16 '18 at 11:28
add a comment |
2 Answers
2
active
oldest
votes
What is wrong with the query is that you are using unaggregated columns in the having
. My recommendation is to use window functions:
SELECT o.*
FROM (SELECT o.*,
MIN(pickingdate) OVER (PARTITION BY sku) as min_pd,
MAX(pickingdate) OVER (PARTITION BY sku) as max_pd
FROM td_order1 o
WHERE sku = xyz
) o
WHERE pickingdate > min_pd and pickingdate < max_pd
ORDER BY pickingdate;
add a comment |
Modify your condition as :
pickingdate < Max(pickingdate)
to exclude end date
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%2f53336902%2fsql-to-get-the-dates-between-start-date-is-included-and-end-date-is-excluded%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
What is wrong with the query is that you are using unaggregated columns in the having
. My recommendation is to use window functions:
SELECT o.*
FROM (SELECT o.*,
MIN(pickingdate) OVER (PARTITION BY sku) as min_pd,
MAX(pickingdate) OVER (PARTITION BY sku) as max_pd
FROM td_order1 o
WHERE sku = xyz
) o
WHERE pickingdate > min_pd and pickingdate < max_pd
ORDER BY pickingdate;
add a comment |
What is wrong with the query is that you are using unaggregated columns in the having
. My recommendation is to use window functions:
SELECT o.*
FROM (SELECT o.*,
MIN(pickingdate) OVER (PARTITION BY sku) as min_pd,
MAX(pickingdate) OVER (PARTITION BY sku) as max_pd
FROM td_order1 o
WHERE sku = xyz
) o
WHERE pickingdate > min_pd and pickingdate < max_pd
ORDER BY pickingdate;
add a comment |
What is wrong with the query is that you are using unaggregated columns in the having
. My recommendation is to use window functions:
SELECT o.*
FROM (SELECT o.*,
MIN(pickingdate) OVER (PARTITION BY sku) as min_pd,
MAX(pickingdate) OVER (PARTITION BY sku) as max_pd
FROM td_order1 o
WHERE sku = xyz
) o
WHERE pickingdate > min_pd and pickingdate < max_pd
ORDER BY pickingdate;
What is wrong with the query is that you are using unaggregated columns in the having
. My recommendation is to use window functions:
SELECT o.*
FROM (SELECT o.*,
MIN(pickingdate) OVER (PARTITION BY sku) as min_pd,
MAX(pickingdate) OVER (PARTITION BY sku) as max_pd
FROM td_order1 o
WHERE sku = xyz
) o
WHERE pickingdate > min_pd and pickingdate < max_pd
ORDER BY pickingdate;
answered Nov 16 '18 at 11:52
Gordon LinoffGordon Linoff
794k37318421
794k37318421
add a comment |
add a comment |
Modify your condition as :
pickingdate < Max(pickingdate)
to exclude end date
add a comment |
Modify your condition as :
pickingdate < Max(pickingdate)
to exclude end date
add a comment |
Modify your condition as :
pickingdate < Max(pickingdate)
to exclude end date
Modify your condition as :
pickingdate < Max(pickingdate)
to exclude end date
answered Nov 16 '18 at 11:31
codeLovercodeLover
2,2551620
2,2551620
add a comment |
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%2f53336902%2fsql-to-get-the-dates-between-start-date-is-included-and-end-date-is-excluded%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
1
pickingdate < Max(pickingdate)
, if you want to exclude end date– mbharanidharan88
Nov 16 '18 at 11:28