Stock Qty shows as per Material Issue Slip MIS

basit0079 · December 8, 2016, 6:43pm

Hi,

I have two tables called Stock Table and MIS table. In stock tables fields are Stk_ItmNo, Stk_Qty, Stk_Location
MIS tables columns are Mis_No, Mis_Date, Mis_ItmNo, Mis_Qty

What i'm looking for is suppose below is tock table

Stk_ItmNo Stk_Qty Stk_Location
111 4 A
222 6 B
333 8 A
if Suppose 2-Dec-2016 is i made MIS for following items

Mis_No Mis_Date Mis_ItmNo Mis_Qty
MIS-0001 2-Dec-2016 111 2
MIS-0002 2-Dec-2016 222 1
then after issue MIS stock table will below

Stk_ItmNo Stk_Qty Stk_Location
111 2 A
222 5 B
333 8 A

what i want is when i'm checking stock report dated 1-dec-2016 then stock will be shown as below

Stk_ItmNo Stk_Qty Stk_Location
111 4 A
222 6 B
333 8 A

What i'm looking for, if i select any particular date what was the stock on that date.

How to do this query?

Thanks
Basit.

basit0079 · December 9, 2016, 6:55am

What i tried is created a temporary table called Temp_Stock columns are temp_ItmNo, temp_StockQty, temp_Location then first enter whatever stock in stock table enter in Temp_Stock then check the MIS transaction and whatever select date after the MIS_Qty enter in Temp_Stock.

How to do query in sql query or any other best option.

Thanks
Basit.

stepson · December 9, 2016, 7:37am

Stock table holds the current stock records.
MIS table holds the transactions records.(from what I understand).

CREATE TABLE #Stock
(
	Stk_ItmNo INT NOT NULL
	,Stk_Qty  INT NOT NULL
	,Stk_Location CHAR(1) NOT NULL
	,CONSTRAINT PK_Stock_itmNo PRIMARY KEY CLUSTERED(Stk_ItmNo)
);
INSERT INTO #Stock(Stk_ItmNo,Stk_Qty,Stk_Location)
	VALUES(111, 2 ,'A') ,(222, 5, 'B'), (333, 8, 'A');

CREATE TABLE #MIS
(
	Mis_No CHAR(10) NOT NULL
	,Mis_Date DATE NOT NULL
	,Mis_ItmNo INT NOT NULL
	,Mis_Qty INT
	,CONSTRAINT PK_MIS_No PRIMARY KEY CLUSTERED(Mis_No)
);


INSERT INTO #MIS(Mis_No,Mis_Date,Mis_ItmNo,Mis_Qty)
VALUES ('MIS-0001', '2-Dec-2016', 111, 2)
		,('MIS-0002','2-Dec-2016', 222 ,1);

By joining the current stock (Stock tbl) with the transactions (MIS table) after a specific date, will returns the stock at a specific date.

DECLARE @d_MyDATE AS DATE = '20161201';

SELECT
	Stk_ItmNo = COALESCE(S.Stk_ItmNo,M.Mis_itmNo) 
	,Stk_Qty = COALESCE(S.Stk_Qty,0) + COALESCE(M.SUM_Mis_Qty,0)
	,Stk_Location = S.Stk_Location
FROM
	#Stock AS S  -- this is the current stock
	FULL JOIN 
	-- give me all the transactions after the reporting date
		( SELECT
				M.Mis_itmNo
				,SUM(M.Mis_Qty) AS SUM_Mis_Qty
			FROM
				#MIS AS M
			WHERE
				M.Mis_Date > @d_MyDATE
			GROUP BY
				M.Mis_itmNo
		)AS M
		ON S.Stk_ItmNo	=  M.Mis_ItmNo;

Output

Stk_ItmNo   Stk_Qty     Stk_Location
111         4           A
222         6           B
333         8           A

stepson · December 9, 2016, 7:54am

Is your MIS table holds all the transactions ? from start till present?
if yes, then you can "reconstruct" the stock table only by querying the MIS table up to that specific date. ( less location field - this is not present in table)

basit0079 · December 9, 2016, 9:01am

First Many Many thanks for your prompt respond. Really appreciated.
Yes MIS table holds all transaction from start till present. then how to reconstruct the stock table?

Thanks
Basit.

stepson · December 9, 2016, 9:10am

Something like this:

DECLARE @d_MyDATE AS DATE = '20161201';
        
SELECT
    Mis_No AS Stk_itmNo
    ,SUM(Mis_Qty) As Stk_Qty
FROM
    #MIS AS M
WHERE
    Mis_Date < DATEADD(DAY,1,@d_MyDATE)
GROUP BY
    Mis_No

Yours In and Out transactions are kept in MIS table.
Pls provide with more samples data for this table if the upper query is not quite exactly.

basit0079 · December 9, 2016, 9:29am

Many Thanks

Now i added in MIS Table column called Mis_AvgRate and in stock table Column Stk_AvgRate

Stock Table Was

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgQty
111 4 A 11
222 6 B 17
333 8 A 3

After enter MIS table is below.

Mis_No Mis_Date Mis_ItmNo Mis_AvgQty
MIS-0001 2016-12-02 111 2 11.00
MIS-0002 2016-12-02 222 1 17.00

and current stock table is

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgQty
111 2 A 5
222 5 B 2
333 8 A 3

When select Date='20161201'

then stock table should be.

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgQty
111 2 A 11
222 5 B 17
333 8 A 3

How to do the query.

i did the query

DECLARE @d_MyDATE AS DATE = '2016-12-01';

SELECT
Stk_ItmNo = COALESCE(S.Stk_ItmNo,M.Mis_itmNo)
,Stk_Qty = COALESCE(S.Stk_Qty,0) + COALESCE(M.SUM_Mis_Qty,0)
,Stk_Location = S.Stk_Location
,Stk_AvgRate = COALESCE(S.Stk_AvgRate,0) + COALESCE(M.SUM_Stk_AvgRate,0)

FROM
Stock AS S -- this is the current stock
FULL JOIN
-- give me all the transactions after the reporting date
( SELECT
M.Mis_itmNo
,SUM(M.Mis_Qty) AS SUM_Mis_Qty
,sum(M.Mis_AvgRate) AS SUM_Stk_AvgRate
FROM
MIS AS M
WHERE
M.Mis_Date > @d_MyDATE
GROUP BY
M.Mis_itmNo
)AS M
ON S.Stk_ItmNo = M.Mis_ItmNo;

then o/p is coming

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgRate
111 2 A 16
222 5 B 19
333 8 A 3

is taking sum of Stk_AvgQty what i want is what was the AvgRate on that Date
the o/p should be

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgRate
111 2 A 11
222 5 B 17
333 8 A 3

stepson · December 9, 2016, 9:51am

Yours Stk_AvgRate , I belive should be like this:

,Stk_AvgRate = (COALESCE(S.Stk_AvgRate,0) + COALESCE(M.SUM_Stk_AvgRate,0))
        /(CASE WHEN S.Stk_AvgRate IS NOT NULL AND M.SUM_Stk_AvgRate IS NOT NULL THEN 2
              ELSE 1  END)

-- you are doing the SUM of it but then divide it , to be AVG

and lower, SUM_Stk_AvgRate:

SELECT
    M.Mis_itmNo
    ,SUM(M.Mis_Qty) AS SUM_Mis_Qty
    ,AVG(M.Mis_AvgRate) AS SUM_Stk_AvgRate

basit0079 · December 9, 2016, 10:00am

Thanks A lot.

i did below query according to you.

DECLARE @d_MyDATE AS DATE = '2016-12-01';

SELECT
Stk_ItmNo = COALESCE(S.Stk_ItmNo,M.Mis_itmNo)
,Stk_Qty = COALESCE(S.Stk_Qty,0) + COALESCE(M.SUM_Mis_Qty,0)
,Stk_Location = S.Stk_Location
,Stk_AvgRate = (COALESCE(S.Stk_AvgRate,0) + COALESCE(M.SUM_Stk_AvgRate,0))
/(CASE WHEN S.Stk_AvgRate IS NOT NULL AND M.SUM_Stk_AvgRate IS NOT NULL THEN 2
ELSE 1 END)

FROM
Stock AS S -- this is the current stock
FULL JOIN
-- give me all the transactions after the reporting date
( SELECT
M.Mis_itmNo
,SUM(M.Mis_Qty) AS SUM_Mis_Qty
,AVG(M.Mis_AvgRate) AS SUM_Stk_AvgRate
FROM
MIS AS M
WHERE
M.Mis_Date > @d_MyDATE
GROUP BY
M.Mis_itmNo
)AS M
ON S.Stk_ItmNo = M.Mis_ItmNo;

but the output is coming

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgRate
111 2 A 8
222 5 B 9
333 8 A 3

o/p should be

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgRate
111 2 A 11
222 5 B 17
333 8 A 3

Thanks
Basit.

stepson · December 9, 2016, 1:37pm

Can you explain, in words, how you get to that values ? AvgRate?

basit0079 · December 9, 2016, 1:55pm

Many Thanks,

at the first i have this data.

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgQty
111 4 A 11
222 6 B 17
333 8 A 3

then i saved the last averagerate in MIS table.

Mis_No Mis_Date Mis_ItmNo Mis_AvgQty
MIS-0001 2016-12-02 111 2 11.00
MIS-0002 2016-12-02 222 1 17.00

after that couple of transaction passed.

but if i want to check what was the average date on 2016-02-02 then the data should be

Stk_ItmNo Stk_Qty Stk_Location Stk_AvgRate
111 2 A 11
222 5 B 17
333 8 A 3

What im really looking for the tables structure of Good receive Note [GRN] and Material Issue Slip [MIS] where my stock will add when i pass GRN transaction and reduce when i pass MIS.

for the reporting purpose i want query

when user select particular date on that time what was qty and what was average price on that date should appear.

thanks
basit.

stepson · December 9, 2016, 1:59pm

I'm refering to the field Stk_AvgRate.
Because , we can take simple from Stock table , with out the calculation

Stk_AvgRate = S.S.Stk_AvgRate

basit0079 · December 9, 2016, 2:07pm

Many Thanks

Can you please tell me my table structure are right for GRN & MIS

Good receive Note [GRN]

Trn_Grn

Grn_No
Grn_Number
Grn_Date
Grn_PoNo

Trn_GrnDetails

Grn_No
Grn_ItmNo
Grn_Qty
Grn_LastStockQty ----- while saving getting from stock table
Grn_LastAvgRate -------while saving getting from stock table

MIS

Trn_MIS

Mis_No
Mis_Number
Mis_Date

Trn_MisDetails

Mis_No
Mis_ItmNo
Mis_ItmQty
Mis_StockQty -------- While saving getting from Stock table what was Qty
Mis_AveragRate ---While saving getting from Stock table what was AvgRate

Stock Table

Stock_ItmNo
Stock_Qty
Stock_Location
Stock_AvgRate

Please check the table structure is right for GRN and MIS and Stock table as well.

Many many thanks once again for your support.

Thanks
Basit.

stepson · December 9, 2016, 2:17pm

About :

Grn_LastStockQty ----- while saving getting from stock table
Grn_LastAvgRate -------while saving getting from stock table
Mis_StockQty -------- While saving getting from Stock table what was Qty
Mis_AveragRate ---While saving getting from Stock table what was AvgRate

Those I will not save (if it is posibil to calculate ), but in the end is your decision.
If you have the formula for AVGRate, then this should be calculated in rpt query.

The structures are close to what I'm thinking.

Trn_Grn:
Grn_ID  - PK
Grn_No
Grn_Number
Grn_Date
Grn_PoNo

Trn_GrnDetails:
Grn_ID_D PK
Grn_ID - FK
Grn_No ? is this the foreign key ? is this the ID
Grn_ItmNo - this is the product
Grn_Qty - this is the quantity

the same for Trn

To get the stock at a particular date x,that is in past
you should to

Grn at X date
join
Trn at x date

basit0079 · December 9, 2016, 2:24pm

Thanks A lot.

What about MIS tables (Trn_MIS and Trn_MISDetails).

and can you please do this full query for stockqty and averagerate for particular date depend upon this table structure

To get the stock at a particular date x,that is in past
you should to

Grn at X date
join
Trn at x date

Thanks
Basit.

stepson · December 9, 2016, 2:28pm

The Mis and Mis_Details is the same as Grn

For stock at a particular date:

SELECT
	Stock_ItmNo = COALESCE(G.Grn_itmNo,T.Mis_itmNo)
	,Stock_Qty = COALESCE(G.Grn_Qty,0)- COALESCE(T.Mis_Qty,0)
    ,Grn_Qty_AVG = G.Grn_Qty_AVG
    ,Mis_Qty_AVG = T.Mis_Qty_AVG
FROM
	(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
                ,Grn_Qty_AVG = AVG(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<=@X
         GROUP BY 
                Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(Mis_Qty,0)
                ,Mis_Qty_AVG = AVG(Mis_Qty)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<=@X
        GROUP BY 
             Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo

basit0079 · December 9, 2016, 2:32pm

Many Many thanks.
What about the AvgRate in Query

Really thanks once again.

Thanks
Basit.

stepson · December 9, 2016, 2:35pm

For AVG ,do you have a formula for it? an algorithm ?
I mean , Yes, you can have an Grn_Qty_AVG and and Mis_Qty_AVG and then ...

For example , to Grn:

(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
                ,Grn_Qty_AVG = AVG(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<=@X
       GROUP BY 
               Grn_itmNo   
	)G

You should fill these tables with sample data , with records and start play with it, to see how it is working

basit0079 · December 9, 2016, 5:29pm

Many Thanks
i have created tables

CREATE TABLE [dbo].[Trn_GRN](
	[Grn_ID] [int] NOT NULL,
	[Grn_Number] [nvarchar](50) NULL,
	[Grn_Date] [date] NULL,
	[Grn_PoNo] [int] NULL,
 CONSTRAINT [PK_Trn_GRN] PRIMARY KEY CLUSTERED 
(
	[Grn_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Trn_GrnDetails](
	[Grn_ID_D] [int] NOT NULL,
	[Grn_ID] [int] NULL,
	[Grn_ItmNo] [int] NULL,
	[Grn_Qty] [numeric](18, 4) NULL,
	[Grn_LastStockQty] [numeric](18, 4) NULL,
	[Grn_LastAvgRate] [numeric](18, 4) NULL,
 CONSTRAINT [PK_Trn_GrnDetails] PRIMARY KEY CLUSTERED 
(
	[Grn_ID_D] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Trn_MIS](
	[Mis_ID] [int] NOT NULL,
	[Mis_Number] [nvarchar](50) NULL,
	[Mis_Date] [date] NULL,
 CONSTRAINT [PK_Trn_MIS] PRIMARY KEY CLUSTERED 
(
	[Mis_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Trn_MisDetails](
	[Mis_ID_D] [int] NOT NULL,
	[Mis_ID] [int] NULL,
	[Mis_ItmNo] [int] NULL,
	[Mis_Qty] [numeric](18, 4) NULL,
	[Mis_StockQty] [numeric](18, 4) NULL,
	[Mis_AveragRate] [numeric](18, 4) NULL,
 CONSTRAINT [PK_Trn_MisDetails] PRIMARY KEY CLUSTERED 
(
	[Mis_ID_D] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Mst_Stock](
	[Stock_ItmNo] [int] NULL,
	[Stock_Qty] [numeric](18, 4) NULL,
	[Stock_Location] [nvarchar](50) NULL,
	[Stock_AvgRate] [numeric](18, 4) NULL
) ON [PRIMARY]

GO

When i write your i got error

SELECT
	Stock_ItmNo = COALESCE(G.Grn_itmNo,T.Mis_itmNo)
	,Stock_Qty = COALESCE(G.Grn_Qty,0)- COALESCE(T.Mis_Qty,0)
    ,Grn_Qty_AVG = G.Grn_Qty_AVG
    ,Mis_Qty_AVG = T.Mis_Qty_AVG
FROM
	(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
                ,Grn_Qty_AVG = AVG(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20160130'
         GROUP BY 
                Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(Mis_Qty,0)
                ,Mis_Qty_AVG = AVG(Mis_Qty)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<='20160130'
        GROUP BY 
             Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo

error is

Msg 8120, Level 16, State 1, Line 23
Column 'Trn_MisDetails.Mis_Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 23
Column 'Trn_MisDetails.Mis_Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

another query what you sent that also got error.

(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
                ,Grn_Qty_AVG = AVG(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20160130'
       GROUP BY 
               Grn_itmNo   
	)G

error is

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'G'.

Once again many thanks

Thanks
Basit.

stepson · December 9, 2016, 5:42pm

It is a SUM that is not present in the query related to MIS

SELECT
	Stock_ItmNo = COALESCE(G.Grn_itmNo,T.Mis_itmNo)
	,Stock_Qty = COALESCE(G.Grn_Qty,0)- COALESCE(T.Mis_Qty,0)
    ,Grn_Qty_AVG = G.Grn_Qty_AVG
    ,Mis_Qty_AVG = T.Mis_Qty_AVG
FROM
	(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
        ,Grn_Qty_AVG = AVG(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20160130'
    GROUP BY 
        Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(SUM(Mis_Qty),0)
        ,Mis_Qty_AVG = AVG(Mis_Qty)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<='20160130'
    GROUP BY 
        Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo