AvgRate, Sql Server

Language: Layout:

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]

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]

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]

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]

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');
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]

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');

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

View schema
Execution time: 0,09 sec, rows selected: 2, rows affected: 12, absolute service time: 0,23 sec