Calculate Month to date , 6 month and Year to date for Union All Query
I am trying to calculate the month to date and year to date for query created using Union All for two different tables one for receiving and one for sale. They both have the same Vendor name. I have created the query as bellow but can calculate the Month to date, last six month or year to date as I get error each time. Any idea please? I used the following code
SELECT VEND_NO,
ACTV_DAT,
SUM(RECVR_SUB_TOT)AS RECV_TOT,
SUM(CALC_EXT_PRC) AS SAL_TOT
FROM ((SELECT P.VEND_NO, P.RECVR_DAT as ACTV_DAT, P.RECVR_SUB_TOT, 0 as CALC_EXT_PRC
FROM PO_RECVR_HIST P
) UNION ALL
(SELECT S.ITEM_VEND_NO, S.BUS_DAT, 0, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
)
) PS
GROUP BY VEND_NO,
ACTV_DAT,
CALC_EXT_PRC
ORDER BY VEND_NO,
ACTV_DAT;
sql-server-2008-r2 sum union-all monthcalendar
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
add a comment |
I am trying to calculate the month to date and year to date for query created using Union All for two different tables one for receiving and one for sale. They both have the same Vendor name. I have created the query as bellow but can calculate the Month to date, last six month or year to date as I get error each time. Any idea please? I used the following code
SELECT VEND_NO,
ACTV_DAT,
SUM(RECVR_SUB_TOT)AS RECV_TOT,
SUM(CALC_EXT_PRC) AS SAL_TOT
FROM ((SELECT P.VEND_NO, P.RECVR_DAT as ACTV_DAT, P.RECVR_SUB_TOT, 0 as CALC_EXT_PRC
FROM PO_RECVR_HIST P
) UNION ALL
(SELECT S.ITEM_VEND_NO, S.BUS_DAT, 0, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
)
) PS
GROUP BY VEND_NO,
ACTV_DAT,
CALC_EXT_PRC
ORDER BY VEND_NO,
ACTV_DAT;
sql-server-2008-r2 sum union-all monthcalendar
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
1
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23
add a comment |
I am trying to calculate the month to date and year to date for query created using Union All for two different tables one for receiving and one for sale. They both have the same Vendor name. I have created the query as bellow but can calculate the Month to date, last six month or year to date as I get error each time. Any idea please? I used the following code
SELECT VEND_NO,
ACTV_DAT,
SUM(RECVR_SUB_TOT)AS RECV_TOT,
SUM(CALC_EXT_PRC) AS SAL_TOT
FROM ((SELECT P.VEND_NO, P.RECVR_DAT as ACTV_DAT, P.RECVR_SUB_TOT, 0 as CALC_EXT_PRC
FROM PO_RECVR_HIST P
) UNION ALL
(SELECT S.ITEM_VEND_NO, S.BUS_DAT, 0, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
)
) PS
GROUP BY VEND_NO,
ACTV_DAT,
CALC_EXT_PRC
ORDER BY VEND_NO,
ACTV_DAT;
sql-server-2008-r2 sum union-all monthcalendar
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
I am trying to calculate the month to date and year to date for query created using Union All for two different tables one for receiving and one for sale. They both have the same Vendor name. I have created the query as bellow but can calculate the Month to date, last six month or year to date as I get error each time. Any idea please? I used the following code
SELECT VEND_NO,
ACTV_DAT,
SUM(RECVR_SUB_TOT)AS RECV_TOT,
SUM(CALC_EXT_PRC) AS SAL_TOT
FROM ((SELECT P.VEND_NO, P.RECVR_DAT as ACTV_DAT, P.RECVR_SUB_TOT, 0 as CALC_EXT_PRC
FROM PO_RECVR_HIST P
) UNION ALL
(SELECT S.ITEM_VEND_NO, S.BUS_DAT, 0, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
)
) PS
GROUP BY VEND_NO,
ACTV_DAT,
CALC_EXT_PRC
ORDER BY VEND_NO,
ACTV_DAT;
sql-server-2008-r2 sum union-all monthcalendar
sql-server-2008-r2 sum union-all monthcalendar
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
edited Nov 19 '18 at 1:38
Used_By_Already
22.8k21938
22.8k21938
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
asked Nov 19 '18 at 1:19
Bahaa GergesBahaa Gerges
135
135
1
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23
add a comment |
1
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23
1
1
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23
add a comment |
1 Answer
1
active
oldest
votes
Remove CALC_EXT_PRC from your group by clause
SELECT
VEND_NO
, ACTV_DAT
, SUM( RECVR_SUB_TOT ) AS RECV_TOT
, SUM( CALC_EXT_PRC ) AS SAL_TOT
FROM (
SELECT
P.VEND_NO
, P.RECVR_DAT AS ACTV_DAT
, P.RECVR_SUB_TOT
, 0 AS CALC_EXT_PRC
FROM PO_RECVR_HIST P
UNION ALL
SELECT
S.ITEM_VEND_NO
, S.BUS_DAT
, 0
, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
) PS
GROUP BY
VEND_NO
, ACTV_DAT
ORDER BY
VEND_NO
, ACTV_DAT;
Also you don't need parentheses for each part of the union.
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
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%2f53367138%2fcalculate-month-to-date-6-month-and-year-to-date-for-union-all-query%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
Remove CALC_EXT_PRC from your group by clause
SELECT
VEND_NO
, ACTV_DAT
, SUM( RECVR_SUB_TOT ) AS RECV_TOT
, SUM( CALC_EXT_PRC ) AS SAL_TOT
FROM (
SELECT
P.VEND_NO
, P.RECVR_DAT AS ACTV_DAT
, P.RECVR_SUB_TOT
, 0 AS CALC_EXT_PRC
FROM PO_RECVR_HIST P
UNION ALL
SELECT
S.ITEM_VEND_NO
, S.BUS_DAT
, 0
, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
) PS
GROUP BY
VEND_NO
, ACTV_DAT
ORDER BY
VEND_NO
, ACTV_DAT;
Also you don't need parentheses for each part of the union.
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
add a comment |
Remove CALC_EXT_PRC from your group by clause
SELECT
VEND_NO
, ACTV_DAT
, SUM( RECVR_SUB_TOT ) AS RECV_TOT
, SUM( CALC_EXT_PRC ) AS SAL_TOT
FROM (
SELECT
P.VEND_NO
, P.RECVR_DAT AS ACTV_DAT
, P.RECVR_SUB_TOT
, 0 AS CALC_EXT_PRC
FROM PO_RECVR_HIST P
UNION ALL
SELECT
S.ITEM_VEND_NO
, S.BUS_DAT
, 0
, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
) PS
GROUP BY
VEND_NO
, ACTV_DAT
ORDER BY
VEND_NO
, ACTV_DAT;
Also you don't need parentheses for each part of the union.
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
add a comment |
Remove CALC_EXT_PRC from your group by clause
SELECT
VEND_NO
, ACTV_DAT
, SUM( RECVR_SUB_TOT ) AS RECV_TOT
, SUM( CALC_EXT_PRC ) AS SAL_TOT
FROM (
SELECT
P.VEND_NO
, P.RECVR_DAT AS ACTV_DAT
, P.RECVR_SUB_TOT
, 0 AS CALC_EXT_PRC
FROM PO_RECVR_HIST P
UNION ALL
SELECT
S.ITEM_VEND_NO
, S.BUS_DAT
, 0
, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
) PS
GROUP BY
VEND_NO
, ACTV_DAT
ORDER BY
VEND_NO
, ACTV_DAT;
Also you don't need parentheses for each part of the union.
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
Remove CALC_EXT_PRC from your group by clause
SELECT
VEND_NO
, ACTV_DAT
, SUM( RECVR_SUB_TOT ) AS RECV_TOT
, SUM( CALC_EXT_PRC ) AS SAL_TOT
FROM (
SELECT
P.VEND_NO
, P.RECVR_DAT AS ACTV_DAT
, P.RECVR_SUB_TOT
, 0 AS CALC_EXT_PRC
FROM PO_RECVR_HIST P
UNION ALL
SELECT
S.ITEM_VEND_NO
, S.BUS_DAT
, 0
, CALC_EXT_PRC
FROM PS_TKT_HIST_LIN S
) PS
GROUP BY
VEND_NO
, ACTV_DAT
ORDER BY
VEND_NO
, ACTV_DAT;
Also you don't need parentheses for each part of the union.
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
answered Nov 19 '18 at 1:43
Used_By_AlreadyUsed_By_Already
22.8k21938
22.8k21938
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%2f53367138%2fcalculate-month-to-date-6-month-and-year-to-date-for-union-all-query%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
What error are you getting?
– DancingFool
Nov 19 '18 at 1:28
Conversion failed when converting character string to smalldatetime data type.
– Bahaa Gerges
Nov 19 '18 at 2:23