Click here to Skip to main content
15,936,709 members
Please Sign up or sign in to vote.
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
Updated 16-Apr-18 2:18am
v2
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900