How to get opening, receipt, issue and closing stock from SQL server 2008

Question

0.00/5 (No votes)

See more:

Hello,Everyone

i am working on stockmanagement system.I have stockmaster table which contains all entries like good receipt note(GRN),PRODUCTION(PROD),BYPRODUCTS(BYPRODUCTS),AND TRANSFER ENRTY(ISSUE).Now i want to show stock ledger date wise.
i.e 1) how many grn quantity (itemwise,lengthwise,datewise,and categorywise)
2).how many issue quantity
3). how many in closing quantity

in following format

SRNO DATE TYPE/FLAG DOCNO QTY
RECEIPT| ISSUE | BALANCE

1 2018-04-14 GRN GRNGD/0001/17-18 110 110
2 2018-04-14 GRN TRNGD/0001/17-18 45 65

Please help me.
Its very urgent.

What I have tried:

SQL

SELECT * FROM
( 
    SELECT TYPE, 
           LENGTH,
           SUM(QTY) QTY  
    FROM dbo.tbl_StockMaster 
    WHERE DIA = '6 mm'  
    AND   LENGTH > 2.1 
    AND   FLAG NOT IN ('PROD','ISSUE') 
    AND   CONVERT(DATE, DOC_DT) <= CONVERT(DATE, '2018-04-16')  
    GROUP BY LENGTH,TYPE ) a 
WHERE a.qty > 0

Posted 16-Apr-18 1:28am

SujataJK

Updated 16-Apr-18 2:18am

#realJSOP

v2

Add a Solution

Comments

SujataJK 17-Apr-18 1:43am

tHIS IS WHAT I HAVE TRIED NOW

SELECT DOC_ID,DOC_DT,QTY,
CASE WHEN QTY > 0 THEN QTY
ELSE '' END AS RECEIPT,
CASE WHEN QTY < 0 THEN QTY
ELSE '' END AS ISSUE
FROM tbl_StockMaster

AND OUTPUT OF THIS SOMETHING LIKE THIS
DOC_ID DOC_DT QTY RECEIPT ISSUE
MRNGD/0001/18-19 2018-04-17 10:22:12.000 24 24 0
MRNGD/0001/18-19 2018-04-17 10:22:12.000 38 38 0
MRNGD/0001/18-19 2018-04-17 10:22:12.000 45 45 0
MRNGD/0002/18-19 2018-04-17 10:23:58.000 25 25 0
MRNGD/0002/18-19 2018-04-17 10:23:58.000 35 35 0
MRNGD/0002/18-19 2018-04-17 10:23:58.000 25 25 0
MRNGD/0003/18-19 2018-04-17 10:25:07.000 50 50 0
MRNGD/0003/18-19 2018-04-17 10:25:07.000 25 25 0
MRNGD/0003/18-19 2018-04-17 10:25:07.000 33 33 0
PRDGD/0001/18-19 2018-04-17 10:26:51.000 45 45 0
PRDGD/0001/18-19 2018-04-17 10:26:51.000 -24 0 -24
PRDGD/0001/18-19 2018-04-17 10:26:51.000 -21 0 -21
PRDGD/0001/18-19 2018-04-17 10:26:51.000 21 21 0
PRDGD/0002/18-19 2018-04-17 10:36:50.000 35 35 0
PRDGD/0002/18-19 2018-04-17 10:36:50.000 -35 0 -35
TRNGD/0001/18-19 2018-04-17 00:00:00.000 -45 0 -45

NOW I WANT ANOTHER COLUMN AFTER ISSUE COLUMN i.e BALANCE(STOCK) COLUMN WHICH SOMETHING LIKE THIS

DOC_ID DOC_DT QTY RECEIPT ISSUE BALANCE
MRNGD/0001/18-19 2018-04-17 24 24 0 24.00
MRNGD/0001/18-19 2018-04-17 38 38 0 62.00
MRNGD/0001/18-19 2018-04-17 45 45 0 107.00
MRNGD/0002/18-19 2018-04-17 25 25 0 132.00
MRNGD/0002/18-19 2018-04-17 35 35 0 167.00
MRNGD/0002/18-19 2018-04-17 25 25 0 192.00
MRNGD/0003/18-19 2018-04-17 50 50 0 245.00
MRNGD/0003/18-19 2018-04-17 25 25 0 270.00
MRNGD/0003/18-19 2018-04-17 33 33 0 303.00
PRDGD/0001/18-19 2018-04-17 45 45 0 348.00
PRDGD/0001/18-19 2018-04-17 -24 0 -24 324.00
PRDGD/0001/18-19 2018-04-17 -21 0 -21 303.00
PRDGD/0001/18-19 2018-04-17 21 21 0 324.00
PRDGD/0002/18-19 2018-04-17 35 35 0 359.00
PRDGD/0002/18-19 2018-04-17 -35 0 -35 324.00
TRNGD/0001/18-19 2018-04-17 -45 0 -45 279.00

Please quide me

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)