set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[STPGridList_Taraz] @__StartDate char(8)='00/00/00', @__EndDate char(8)='99/99/99', @FormCaption nvarchar(1000) output AS 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 = 1) union SELECT NULL AS RemainIn, SUM(-AmountIn + AmountOut) AS RemainOut, N'سود عملکرد' AS Title, 1000 AS AccountSysCode0 FROM dbo.FunTarazSoodKol(@__StartDate, @__EndDate) AS FunTarazSoodKol_1 GROUP BY temp union SELECT AmountOut AS RemainIn,null as RemainOut, Title, 0 AS AccountSysCode0 FROM dbo.FunTarazSoodKol(@__StartDate, @__EndDate) AS FunTarazSoodKol_1 WHERE (RowNo = 5)