Add Math function to SQL Query The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL table
Expectation in a stochastic differential equation
Yu-Gi-Oh cards in Python 3
A question about free fall, velocity, and the height of an object.
Which one is the true statement?
What does "shotgun unity" refer to here in this sentence?
(How) Could a medieval fantasy world survive a magic-induced "nuclear winter"?
Is there a difference between "Fahrstuhl" and "Aufzug"?
What CSS properties can the br tag have?
My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?
How did Beeri the Hittite come up with naming his daughter Yehudit?
What connection does MS Office have to Netscape Navigator?
What happened in Rome, when the western empire "fell"?
"Eavesdropping" vs "Listen in on"
Is fine stranded wire ok for main supply line?
Does destroying a Lich's phylactery destroy the soul within it?
What would be the main consequences for a country leaving the WTO?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact
How to find image of a complex function with given constraints?
Graph of the history of databases
How to use ReplaceAll on an expression that contains a rule
free fall ellipse or parabola?
Why don't programming languages automatically manage the synchronous/asynchronous problem?
Reshaping json / reparing json inside shell script (remove trailing comma)
Add Math function to SQL Query
The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?SQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL table
New to SQL and I am trying to run a query that pulls all our item codes, lot number, and qty on hand.
Each lot number has multiple entries due to adjustments. I need a way of running my query and having it add or subtract to get the actual qty on hand for each lot and only show me lots that are in the negatives. I have tried playing with SSRS but I cant get it right. I'm using SQL 2008R2.
SELECT
IMLAYER.ITEM_CODE
,IMMSTR.ITEM_DESC
,IMLAYER.LOT_NO
,IMLAYER.QTY_ON_HAND
FROM
IMLAYER
INNER JOIN
IMMSTR
ON
IMLAYER.ITEM_CODE = IMMSTR.ITEM_CODE
WHERE
(IMLAYER.QTY_ON_HAND < 0);
sql
sql-server sql-server-2008-r2 edited Mar 8 at 17:25
Eric Brandt
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
add a comment |
New to SQL and I am trying to run a query that pulls all our item codes, lot number, and qty on hand.
Each lot number has multiple entries due to adjustments. I need a way of running my query and having it add or subtract to get the actual qty on hand for each lot and only show me lots that are in the negatives. I have tried playing with SSRS but I cant get it right. I'm using SQL 2008R2.
SELECT
IMLAYER.ITEM_CODE
,IMMSTR.ITEM_DESC
,IMLAYER.LOT_NO
,IMLAYER.QTY_ON_HAND
FROM
IMLAYER
INNER JOIN
IMMSTR
ON
IMLAYER.ITEM_CODE = IMMSTR.ITEM_CODE
WHERE
(IMLAYER.QTY_ON_HAND < 0);
sql
sql-server sql-server-2008-r2 edited Mar 8 at 17:25
Eric Brandt
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
1
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function likeSUM().
– Arnaud Peralta
Mar 8 at 17:25
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43
add a comment |
New to SQL and I am trying to run a query that pulls all our item codes, lot number, and qty on hand.
Each lot number has multiple entries due to adjustments. I need a way of running my query and having it add or subtract to get the actual qty on hand for each lot and only show me lots that are in the negatives. I have tried playing with SSRS but I cant get it right. I'm using SQL 2008R2.
SELECT
IMLAYER.ITEM_CODE
,IMMSTR.ITEM_DESC
,IMLAYER.LOT_NO
,IMLAYER.QTY_ON_HAND
FROM
IMLAYER
INNER JOIN
IMMSTR
ON
IMLAYER.ITEM_CODE = IMMSTR.ITEM_CODE
WHERE
(IMLAYER.QTY_ON_HAND < 0);
sql
sql-server sql-server-2008-r2 edited Mar 8 at 17:25
Eric Brandt
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
New to SQL and I am trying to run a query that pulls all our item codes, lot number, and qty on hand.
Each lot number has multiple entries due to adjustments. I need a way of running my query and having it add or subtract to get the actual qty on hand for each lot and only show me lots that are in the negatives. I have tried playing with SSRS but I cant get it right. I'm using SQL 2008R2.
SELECT
IMLAYER.ITEM_CODE
,IMMSTR.ITEM_DESC
,IMLAYER.LOT_NO
,IMLAYER.QTY_ON_HAND
FROM
IMLAYER
INNER JOIN
IMMSTR
ON
IMLAYER.ITEM_CODE = IMMSTR.ITEM_CODE
WHERE
(IMLAYER.QTY_ON_HAND < 0);
sql
sql-server sql-server-2008-r2 sql
sql-server sql-server-2008-r2 edited Mar 8 at 17:25
Eric Brandt
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
edited Mar 8 at 17:25
Eric Brandt
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
edited Mar 8 at 17:25
Eric Brandt
3,08511127
edited Mar 8 at 17:25
Eric Brandt
3,08511127
edited Mar 8 at 17:25
Eric Brandt
3,08511127
3,08511127
asked Mar 8 at 17:23
Morad Morad
31
asked Mar 8 at 17:23
Morad Morad
31
asked Mar 8 at 17:23
Morad Morad
31
31
1
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function likeSUM().
– Arnaud Peralta
Mar 8 at 17:25
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43
add a comment |
1
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function likeSUM().
– Arnaud Peralta
Mar 8 at 17:25
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43
1
1
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function like
SUM().– Arnaud Peralta
Mar 8 at 17:25
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function like
SUM().– Arnaud Peralta
Mar 8 at 17:25
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43
add a comment |
1 Answer
1
active
oldest
votes
I believe I understand the requirements correctly, but if not please comment and I can update the query:
SELECT
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
IMLAYER L
INNER JOIN
IMMSTR M
ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
HAVING
SUM(L.QTY_ON_HAND) < 0
HAVING is the trick you are looking for to be able to use an aggregate function for filtering.
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
add a comment |
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%2f55068099%2fadd-math-function-to-sql-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
I believe I understand the requirements correctly, but if not please comment and I can update the query:
SELECT
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
IMLAYER L
INNER JOIN
IMMSTR M
ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
HAVING
SUM(L.QTY_ON_HAND) < 0
HAVING is the trick you are looking for to be able to use an aggregate function for filtering.
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
add a comment |
I believe I understand the requirements correctly, but if not please comment and I can update the query:
SELECT
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
IMLAYER L
INNER JOIN
IMMSTR M
ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
HAVING
SUM(L.QTY_ON_HAND) < 0
HAVING is the trick you are looking for to be able to use an aggregate function for filtering.
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
add a comment |
I believe I understand the requirements correctly, but if not please comment and I can update the query:
SELECT
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
IMLAYER L
INNER JOIN
IMMSTR M
ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
HAVING
SUM(L.QTY_ON_HAND) < 0
HAVING is the trick you are looking for to be able to use an aggregate function for filtering.
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
I believe I understand the requirements correctly, but if not please comment and I can update the query:
SELECT
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
,'SUM_OF_QTY_ON_HAND' = SUM(L.QTY_ON_HAND)
FROM
IMLAYER L
INNER JOIN
IMMSTR M
ON L.ITEM_CODE = M.ITEM_CODE
GROUP BY
M.ITEM_CODE
,M.ITEM_DESC
,L.LOT_NO
HAVING
SUM(L.QTY_ON_HAND) < 0
HAVING is the trick you are looking for to be able to use an aggregate function for filtering.
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
answered Mar 8 at 18:16
Jacob HJacob H
2,1301626
2,1301626
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
add a comment |
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
Thanks. Sorry I couldn't explain it better but your solution worked perfect.
– Morad
Mar 8 at 18:32
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
No problem. I come from background in accounting/ERP software so I am familiar with this type of project.
– Jacob H
Mar 8 at 18:59
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%2f55068099%2fadd-math-function-to-sql-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
Hello, we need expected result and samples of data. But i think you can find some solution with the GROUP BY clause and aggregation function like
SUM().– Arnaud Peralta
Mar 8 at 17:25
Your question is not clear what you are trying to do. Here is a great place to get ideas. Also we need some sample data and such. Please see this post for what you should post.
– Sean Lange
Mar 8 at 17:33
So you want to SUM all of the QTY_ON_HAND, but then only return the records where the SUM is negative? I assume this is for an inventory replenishment?
– Jacob H
Mar 8 at 17:43