SQLTeam.com | Weblogs | Forums

Stock Qty shows as per Material Issue Slip MIS

Many Many thanks.

Then from where i can get the avgrate?

Thanks
Basit.

Insert some records in these tables, see how it works and then ,base on this set , calculate the AvgRate

Thanks A lot.

This query is perfectly working when GRN & MIS from start.

but what my problem is i need to directly adjust the stock where there is no GRN and GIN.

first i will put the opening balance & avgrate in stock table, after that will start the GRN & MIS come to the picture

then what will be the query.

i have attached the o/p result from your query if system start from GRN & MIS.

but what my problem is i need to directly adjust the stock where there is no GRN and GIN.

What do you mean ? please elaborate it to better understand. (GIN ? who/what is GIN)

This query is perfectly working

What we need to adjust it , to make it as you need ?

ps: beside excel picture, please use Insert statements so that we can use them to work/test it.

Thanks a lot and really sorry for writing GIN.

below is the database structure and insert query.

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

INSERT INTO Trn_GRN(Grn_ID,Grn_Number,Grn_Date,Grn_PoNo) VALUES ('1','G-0001','12/12/2016','1');
INSERT INTO Trn_GRN(Grn_ID,Grn_Number,Grn_Date,Grn_PoNo) VALUES ('2','G-0002','12/13/2016','1');

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

INSERT INTO [Trn_GrnDetails](Grn_ID_D,Grn_ID,Grn_ItmNo,Grn_Qty,Grn_LastStockQty,Grn_LastAvgRate) VALUES ('1','1','111','1.0000','0.0000','0.0000');
INSERT INTO [Trn_GrnDetails](Grn_ID_D,Grn_ID,Grn_ItmNo,Grn_Qty,Grn_LastStockQty,Grn_LastAvgRate) VALUES ('2','1','222','2.0000','0.0000','0.0000');
INSERT INTO [Trn_GrnDetails](Grn_ID_D,Grn_ID,Grn_ItmNo,Grn_Qty,Grn_LastStockQty,Grn_LastAvgRate) VALUES ('3','2','111','3.0000','0.0000','0.0000');


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

INSERT INTO Trn_MIS(Mis_ID,Mis_Number,Mis_Date) VALUES ('1','I-0001','12/12/2016');
INSERT INTO Trn_MIS(Mis_ID,Mis_Number,Mis_Date) VALUES ('2','I-0001','12/13/2016');


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

INSERT INTO Trn_MisDetails(Mis_ID_D,Mis_ID,Mis_ItmNo,Mis_Qty,Mis_StockQty,Mis_AveragRate) VALUES ('1','1','111','1.0000','0.0000','0.0000');
INSERT INTO Trn_MisDetails(Mis_ID_D,Mis_ID,Mis_ItmNo,Mis_Qty,Mis_StockQty,Mis_AveragRate) VALUES ('2','1','222','1.0000','0.0000','0.0000');
INSERT INTO Trn_MisDetails(Mis_ID_D,Mis_ID,Mis_ItmNo,Mis_Qty,Mis_StockQty,Mis_AveragRate) VALUES ('3','2','222','1.0000','0.0000','0.0000');

when i passed the query as u sent.

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 = sum(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20161213'
    GROUP BY 
        Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(SUM(Mis_Qty),0)
        ,Mis_Qty_AVG = sum(Mis_Qty)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<='20161213'
    GROUP BY 
        Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo

with above the result is coming perfect.

--Above code is okay when GRN & MIS is from Starting in system, there is no stock exist.

--I already have stock without having GRN & MIS.

-- How to manage this, no GRN and MIS. I adjust the stock and put the Stock Qty & stock avg rate
-- manually in stock table.
-- Then how the query will be?

--below is the stock table.

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

INSERT INTO [Mst_Stock](Stock_ItmNo,Stock_Qty,Stock_Location,Stock_AvgRate) VALUES ('111','2','A','3.5000');
INSERT INTO [Mst_Stock](Stock_ItmNo,Stock_Qty,Stock_Location,Stock_AvgRate) VALUES ('222','2','B','4.5000');

then how to do the query.

Many Thanks
Basit.

Just add another join that holds the initial stock:

SELECT
	Stock_ItmNo = COALESCE(G.Grn_itmNo,T.Mis_itmNo,S.Stock_itmNo)
	,Stock_Qty = COALESCE(S.SUM_Stock_Qty,0) + 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 = sum(Grn_Qty)
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20161213'
    GROUP BY 
        Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(SUM(Mis_Qty),0)
        ,Mis_Qty_AVG = sum(Mis_Qty)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<='20161213'
    GROUP BY 
        Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo
    FULL JOIN
    (SELECT 
         Stock_ItmNo
         ,SUM(Stock_Qty) AS SUM_Stock_Qty
     FROM
        Mst_Stock
     GROUP BY
         Stock_ItmNo
     ) AS S
     ON G.Grn_itmNo = S.Stock_itmNo

see here: http://rextester.com/UZRB19691

Many Many Thanks,

But what about the avgrate?

What will the query? Becoz avgrate in Mst_Stock Table(Stock_AvgRate)

Thanks
Basit.

For AvgRate , I don't have an idea in this moment.
Let use our brain , and see what we come up with : maybe the direct use of AVG function or combination of SUM()/COUNT()

many many thanks.

what i'm thinking is while making GRN let save the PO unitprice value in GRN, and from Mst_Stock AvgunitPrice and Trn_GrnDetails Grn_Unitprice get the avgunitprice.

the below is the table

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,
	[Grn_UnitPrice] [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

Yes , nice one, and from now you can develop and extend the functionality of it

Really Manay Many Thanks.

Below is the code which i did.

i will enter multiple datas then come to know wheather it is perfectly work or not.

SELECT
	Stock_ItmNo = COALESCE(G.Grn_itmNo,T.Mis_itmNo,S.Stock_itmNo)
	,Stock_Qty = COALESCE(S.SUM_Stock_Qty,0) + COALESCE(G.Grn_Qty,0)- COALESCE(T.Mis_Qty,0)
	
    ,Grn_Qty_SUM = G.Grn_Qty_SUM
    ,Mis_Qty_SUM = T.Mis_Qty_SUM
    ,Stock_AvgUnitPrice = COALESCE(S.SUM_Stock_AvgUnitPrice,0) + COALESCE(G.Grn_AvgUnitPrice,0)- COALESCE(T.Mis_AvgUnitPrice,0)
    
    ,Grn_AvgUnitPrice_SUM = G.Grn_AvgUnitPrice_SUM
    ,Mis_AvgUnitPrice_SUM = T.Mis_AvgUnitPrice_SUM
    
FROM
	(SELECT 
		Grn_itmNo = Grn_itmNo
		,Grn_Qty = COALESCE(SUM(Grn_Qty),0)
        ,Grn_Qty_SUM = sum(Grn_Qty)
        
        ,Grn_AvgUnitPrice = COALESCE(AVG(Grn_AvgUnitPrice),0)
        ,Grn_AvgUnitPrice_SUM = AVG(Grn_AvgUnitPrice)
        
	FROM
		Trn_Grn AS G
		INNER JOIN Trn_GrnDetails AS GD
		ON G.Grn_ID = GD.Grn_ID
	WHERE
		G.Grn_Date<='20161213'
    GROUP BY 
        Grn_itmNo
	)G
	FULL JOIN
	(SELECT 
		Mis_itmNo = Mis_itmNo
		,Mis_Qty = COALESCE(SUM(Mis_Qty),0)
        ,Mis_Qty_SUM = sum(Mis_Qty)
        
        ,Mis_AvgUnitPrice = COALESCE(AVG(Mis_AvgUnitPrice),0)
        ,Mis_AvgUnitPrice_SUM = AVG(Mis_AvgUnitPrice)
	FROM
		Trn_MIS AS M
		INNER JOIN Trn_MisDetails AS MD
		ON M.Mis_ID = MD.Mis_ID
	WHERE
		M.Mis_Date<='20161213'
    GROUP BY 
        Mis_itmNo
	)T
	ON G.Grn_itmNo = T.Mis_itmNo
    FULL JOIN
    (SELECT 
         Stock_ItmNo
         ,SUM(Stock_Qty) AS SUM_Stock_Qty
         ,AVG(Stock_AvgUnitPrice) as SUM_Stock_AvgUnitPrice
     FROM
        Mst_Stock
     GROUP BY
         Stock_ItmNo
     ) AS S
     ON G.Grn_itmNo = S.Stock_itmNo

Once again many many thanks

Great! Good Luck

1 Like

@stepson you deserve a like for sticking with it to the end - great job :+1:

@bitsmed - thanks
The easy part is SQL
The hard part is to understand exactly what is the request