set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[STPGridList_TarazAmaliat] @__StartDate char(8)='00/00/00', @__EndDate char(8)='99/99/99', @FormCaption nvarchar(1000) output AS if @__StartDate='' set @__StartDate='00/00/00' if @__EndDate='' set @__EndDate='99/99/99' if not (@__StartDate='00/00/00' and @__EndDate='99/99/99') set @FormCaption='<' + @__EndDate + '~' + @__StartDate +'>' SELECT CASE WHEN Ammountin = 0 THEN NULL ELSE ammountin END AS AmmountIn, CASE WHEN Ammountout = 0 THEN NULL ELSE Ammountout END AS AmmountOut, (CASE WHEN (CASE WHEN Ammountin IS NULL THEN 0 ELSE ammountin END) - (CASE WHEN Ammountout IS NULL THEN 0 ELSE ammountout END) > 0 THEN (CASE WHEN Ammountin IS NULL THEN 0 ELSE ammountin END) - (CASE WHEN Ammountout IS NULL THEN 0 ELSE ammountout END) ELSE NULL END) AS RemainIn, (CASE WHEN (CASE WHEN Ammountin IS NULL THEN 0 ELSE ammountin END) - (CASE WHEN Ammountout IS NULL THEN 0 ELSE ammountout END) < 0 THEN (CASE WHEN Ammountout IS NULL THEN 0 ELSE ammountout END) - (CASE WHEN Ammountin IS NULL THEN 0 ELSE ammountin END) ELSE NULL END) AS RemainOut, CodeMNGUPTbl.Title, CodeMNGUPTbl.AccountSysCode0 FROM (SELECT SUM(VWDocumentDetail_DocHeader.AmmountIN) AS AmmountIN, SUM(VWDocumentDetail_DocHeader.AmmountOUT) AS AmmountOUT, CodeMNGDWNTbl.FK_AccountSysCode0 FROM CodeMNGDWNTbl LEFT OUTER JOIN VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode WHERE (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) GROUP BY CodeMNGDWNTbl.FK_AccountSysCode0) AS Gardesh RIGHT OUTER JOIN CodeMNGUPTbl ON Gardesh.FK_AccountSysCode0 = CodeMNGUPTbl.AccountSysCode0 WHERE (CodeMNGUPTbl.Kind <> 3)