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]
GOYes , 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 
@bitsmed - thanks
The easy part is SQL
The hard part is to understand exactly what is the request