Opening stock, totalstockin, totalstockout, closingstock by date SQL query

Question

0.00/5 (No votes)

See more:

I have two tables with primary key and foreign key (ItemId)

Table 1 (Items)

ItemID	  ItemName	                         OPStock	 OPStockValue
76	      AML-MICRONUTRIENTS (40X250ML)	       0	     0
9	      AML-MICRONUTRIENTS(50X100ML BOTTAL)  0	     0
11	      AML-MICRONUTRINTS (100X50ML CASE)	   0	     0
10	      AMP-MICRONUTRIENTS(40X500GM DRUM)	   0	     0
82	      AMRIT ZINK	                       0	     0
110	      BENTONITE SULPHUR-1KG	               0	     0
26	      BHINDI SEEDS (KOMAL)	               0	     0
24	      BHINDI SEEDS MANSI(GAUCHO)250GM	   0	     0
25	      BINDI SEEDS MANSI(GAUCHO)500GM	   0	     0
81	      BIO-FERTILIZER SAP	               0	     0
94	      BOROFERT B 11%	                   0	     0
28	      BOTTLEGOURD SEEDS	                   0	     0
7	      CASTER                	           5	   2109.35
96	      CASTOR CAKE	                       525	  261250.5
45	      CERTFIED PADDY SEEDS PANT-12	       0	     0
48	      CERTIFIED PADDY SEEDS BPT5204	       0	     0
47	      CERTIFIED PADDY SEEDS MTU7029	       0	     0
46	      CERTIFIED PADDY SEEDS R. MANSURI	   0	     0
32	      CERTIFIED PADDY SEEDS(SARJU52)	   0	     0

Table 2 (Inventory)

TransType	VchNo ItemID ItemName	Qty	TaxableAmt	EntryDate
Sale	    90	    10	 AMP-MICRO	2	5900	    16-04-20
Purchase	14	    10	 AMP-MICRO	2	5700	    16-04-20
Purchase	58	    110	 BENTONITE 	30	89874	    16-04-20
Purchase	3	     7	 CASTER 	500	261250	    16-04-20
Purchase	18	     7	 CASTER 	500	270000	    16-04-20
Sale	    114	     7	 CASTER 	50	32500	    16-04-20
Sale	    135	     7	 CASTER 	60	39000	    16-04-20
Sale	    139	     7	 CASTER 	70	45500	    23-04-20
Sale	    53	     7	 CASTER 	30	19500	    23-04-20
Sale	    105	     7	 CASTER 	60	39000	    23-04-20
Sale	    106	     7	 CASTER 	60	39000	    22-04-20
Sale	    119	     7	 CASTER 	60	39000	    22-04-20
Sale	    107	     7	 CASTER 	60	39000	    02-05-20
Sale	    108	     7	 CASTER 	60	39000	    04-05-20
Sale	    109	     7	 CASTER 	60	39000	    04-05-20
Sale	    125	     7	 CASTER 	65	42250	    01-05-20
Sale	    60	     7	 CASTER 	40	26000	    06-05-20
Sale	    113	     7	 CASTER 	60	39000	    06-05-20
Sale	    63	     7	 CASTER 	60	39000	    06-05-20
Sale	    64	     7	 CASTER 	60	39000	    06-05-20
Sale	    65	     7	 CASTER 	40	26000	    08-05-20
Sale	    68	     7	 CASTER 	40	26000	    08-05-20
Sale	    69	     7	 CASTER 	40	26000	    28-05-20
Sale	    70	     7	 CASTER 	30	19500	    28-05-20

Now When I Pass query I want to output like below:

ItemName	                      Op.Qty	InQty  OutQty Cl.Stock
AML-MICRONUTRIENTS (40X250ML)	    0	    0	   0	  0
AML-MICRONUTRIENTS(50X100ML BOTTAL)	0	    0	   0	  0
AML-MICRONUTRINTS (100X50ML CASE)	0	    0	   0	  0
AMP-MICRONUTRIENTS(40X500GM DRUM)	0	    2	   2	  0
AMRIT ZINK	                        0	    0	   0	  0
BENTONITE SULPHUR-1KG	            0 	    30	   10	  20
BHINDI SEEDS (KOMAL)	            0	    0	   0	  0
BHINDI SEEDS MANSI(GAUCHO)250GM	    0	    0	   0	  0
BINDI SEEDS MANSI(GAUCHO)500GM	    0	    0	   0	  0
BIO-FERTILIZER SAP	                0	    0	   0	  0
BOROFERT B 11%	                    0	    0	   0	  0
BOTTLEGOURD SEEDS	                0	    0	   0	  0
CASTER OIL CAKE ORGANIC 	        5	   1000	  1005	  0
CASTOR CAKE	                       525	   1480	  1580	 425
CERTFIED PADDY SEEDS PANT-12	    0	    0	   0	  0
CERTIFIED PADDY SEEDS BPT5204	    0	    0	   0	  0
CERTIFIED PADDY SEEDS MTU7029	    0	    0	   0	  0
CERTIFIED PADDY SEEDS R. MANSURI	0	    0	   0	  0
CERTIFIED PADDY SEEDS(SARJU52)	    0	    0	   0	  0
CERTIFIED WHEAT SEEDS	            0	    0	   0	  0
De-oiled Cake	                    0	   680	   0	 680
ENERGY-31 	                        0	    0	   0	  0
FERTISULF	                        0	    0	   0	  0
GERMAN FORCE (BIO GROWTH STIMULAN)	0	    0	   0	  0
GERMAN POWER GOLD (1KG X 10)	    0	    0	   0	  0
HARIYALEE (FE-19%)	                0	   100	  100	  0
HY. BHINDI BH-1000	                0	    0	   0	  0
Hy. Maize-hapl-999 1kgx40	        0	    0	   0	  0
HYBRID MUSTARD SEEDS DHM-44	        0	    0	   0	  0
IPL DAP (IMPORTED)	               609	   2400	  2979	  30
IPL MOP	                           475	   1350	  1825	  0
IPL NPK 16-16-16	                0	   500	  500	  0
IPL UREA NEEM COTED	                0	   3500	  3500	  0
KFL UREA NEEM COTED	                99	   3150	  3249	  0

Database Link :
Download Database

What I have tried:

I am trying to solve using Left Join But the result is not correct

SQL

SELECT I.ItemID, I.ItemName, I.OPStock AS OpStock, SUM(SP.Qty) AS InQty, SUM(SS.Qty) AS OutQty 
FROM items AS I
LEFT JOIN 
inventory AS SP 
ON SP.ItemID = I.ItemID 
LEFT JOIN
inventory AS SS
ON I.ItemID = SS.ItemID 
WHERE SP.TransactionType = 'Purchase' AND SS.TransactionType = 'Sale' 
GROUP BY I.ItemID 
ORDER BY I.ItemName ASC

Posted 7-Jul-21 8:07am

Vivek Kumar

Updated 9-Jul-21 9:53am

v4

Add a Solution

Comments

[no name] 7-Jul-21 15:37pm

- Every SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in every SELECT statement must also be in the same order

Vivek Kumar 8-Jul-21 2:39am

i use left join instead of Union, but output on correct

Maciej Los 8-Jul-21 3:20am

What is expected output?

Vivek Kumar 8-Jul-21 3:57am

https://prnt.sc/19frqox

output like this

CHill60 8-Jul-21 4:48am

Post the expected results in your question - as text, not as an image

CHill60 8-Jul-21 5:13am

Your expected output does not match the sample data you have provided at all!
You also haven't given us any sample data for items 5, 67 or 97.
Help us to help you and at least make sure all the data lines up.

Vivek Kumar 8-Jul-21 10:01am

sorry i provide u database link
https://drive.google.com/drive/folders/1UAeKiUVqELSyhFZsouitonEuKIU_Le-U?usp=sharing

CHill60 8-Jul-21 11:26am

I am at work. I cannot open that link. However, did my solution solve the problem?

Vivek Kumar 8-Jul-21 11:50am

no sir

CHill60 8-Jul-21 12:11pm

Then type in some sample data and the expected results for that sample data and I will try to figure out what is wrong with your code

Vivek Kumar 11-Jul-21 11:32am

I have corrected the question.

1 solution

Add a Solution

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)

CHill60 · Answer 1 · 2021-07-07T23:42:00

Quote:
But the result is not correct

Statements like this do not help us to help you. There is actually no result because your sql does not compile - there are insufficient columns in the group by clause. It needs to be

SQL

GROUP BY I.ItemID, I.ItemName,I.OPStock, I.OPStockValue

The next problem is that this will give you item names of 'Pen' and 'Pencil' rather than the item names you show in your image. ItemName should not be part of the Inventory table - it should be on the Items table - you need to normalize your data correctly see Database Normalization in SQL with Examples – SQLServerCentral[^]

If you need more help with this then see my comment about sample data and expected results actually matching up.