how to solve this problem calculation.

 hi Puneet Kankar,

 

I try my level best .again trying code please attatched this procedure...

 

ALTER PROC [dbo].[GET_STOCK_MEDICINE_DIS_INFORMATION_GRID_SP]

--EXEC GET_STOCK_MEDICINE_DIS_INFORMATION_GRID_SP '1','1','22/07/2017','1','10','0','1','1','100'

@MediDisPatchId VARCHAR=NULL,
@MedecinId VARCHAR=NULL,
@MediDisPatchDate VARCHAR=NULL,
@MediDistribuId VARCHAR=NULL,
@DispatchBalance VARCHAR=NULL,
@Remarks VARCHAR=NULL,
@CreationId VARCHAR=NULL,
@ModifiedId VARCHAR=NULL,
@MedicineDisName VARCHAR=NULL,
@InicialStockBl VARCHAR=NULL

AS

SELECT MedicineDispatch.MediDisPatchId,MedicineDispatch.MedecinId,MedicineDispatch.MediDisPatchDate,MedicineDispatch.MediDistribuId ,
(ISNULL(InicialStockMedicine.Balance,0)) - (ISNULL(MedicineDispatch.DispatchBalance,0)) AS DISBAL,
MedicineDispatch.Remarks,MedicineDispatch.CreationId,MedicineDispatch.ModifiedId,MedicineDispatch.MedicineDisName,MedicineDispatch.InicialStockBl as Balance

FROM MedicineDispatch

LEFT OUTER JOIN MedecinMST ON MedicineDispatch.MedecinId= MedecinMST.MedecinId
LEFT OUTER JOIN MedecineDistribution ON MedicineDispatch.MediDistribuId=MedecineDistribution.MediDistribuId
LEFT OUTER JOIN InicialStockMedicine ON MedicineDispatch.InicialStockBl=InicialStockMedicine.Balance



--WHERE MedecinMST.MedecinName IS NOT NULL


--GROUP BY MedecinMST.MedecinId,MedecinName 

hi Puneet Kankar,

 i have two table one table InicialStock and another table MedicineDispatch table actually i create a procedure where calculated two table field join and balance calculation i need inicial balance substract from medicineDispatch table . given that below table field...

 

inicialStock:

 

 

and this store procedure below :

 

 ALTER PROCEDURE [dbo].[GET_STOCK_MEDICINE_DISPATCH_INFORMATION_GRID_SP]

--exec GET_STOCK_MEDICINE_DISPATCH_INFORMATION_GRID_SP '5','5','7/02/2017','0','ddd','1','1','3','36'

@MediDisPatchId VARCHAR=NULL,
@MedecinId VARCHAR=NULL,
@MediDisPatchDate VARCHAR=NULL,
@MediDistribuId VARCHAR=NULL,
@DispatchBalance VARCHAR=NULL,
@Remarks VARCHAR=NULL,
@CreationId VARCHAR=NULL,
@ModifiedId VARCHAR=NULL,
@InicialStockBl VARCHAR=NULL

AS

SELECT MedicineDispatch.MediDisPatchId,MedicineDispatch.MedecinId,MedicineDispatch.MediDisPatchDate,MedicineDispatch.MediDistribuId,

MedicineDispatch.Remarks,MedicineDispatch.CreationId,MedicineDispatch.ModifiedId,MedicineDispatch.MedicineDisName,MedicineDispatch.ModifiedId,MedicineDispatch.InicialStockBl

FROM MedicineDispatch

INNER JOIN MedecinMST on MedicineDispatch.MedecinId= MedecinMST.MedecinId
INNER JOIN MedecineDistribution on MedicineDispatch.MediDistribuId=MedecineDistribution.MediDistribuId
INNER JOIN InicialStockMedicine ON MedicineDispatch.InicialStockBl=InicialStockMedicine.Balance

GROUP BY MedecinMST.MedecinId,Medecin

 

 

how to sabstract in this store procedure in  inicialStock from dispatchMedicine .so please any one solve this .