set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[FunDocumentDetail_Archive_FromDate] ( @ArchiveName nvarchar(100), @FromDate Char(8)='' ) RETURNS @table_variable TABLE ( DetailCode int, AmmountIn Money, AmmountOut Money, UserRowNo int, DocDate char(8), Comment nvarchar(300), ArchiveName nvarchar(100), SysDate datetime, SndFk_AccountSyscode int, DocCode int, AccountCode int ) AS BEGIN --- find از قبل if not (@FromDate is null or @FromDate='' or @FromDate='00/00/00') INSERT INTO @table_variable SELECT - 1 AS DetailCode, SUM(AmmountIN) AS AmmountIN, SUM(AmmountOUT) AS AmmountOut, NULL AS UserRowNo, NULL AS DocDate, N'....' + MIN(DocDate) + N' - ' + MAX(DocDate) + N'...' AS Comment, NULL AS ArchiveName, NULL AS SysDate, NULL AS SndFk_AccountSyscode,null as DocCode,null as Fk_AccountSyscode FROM VWDocumentDetail_DocHeader WHERE (DocDate < @FromDate) GROUP BY DetailCode * 0 else set @FromDate='' --- find other Archive if not (@ArchiveName is null) begin INSERT INTO @table_variable SELECT - 2 AS DetailCode, SUM(AmmountIN) AS AmmountIN, SUM(AmmountOUT) AS AmmountOut, NULL AS UserRowNo, NULL AS DocDate, ArchiveName + N' : ' + MIN(DocDate) + N' - ' + MAX(DocDate) AS Comment, ArchiveName, NULL AS SysDate, NULL AS SndFk_AccountSyscode, NULL AS DocCode, NULL AS Fk_AccountSyscode FROM VWDocumentDetail_DocHeader WHERE (DocDate >= @FromDate) GROUP BY ArchiveName HAVING (ArchiveName <> @ArchiveName) ORDER BY ArchiveName --- Main list with archive INSERT INTO @table_variable SELECT DetailCode, AmmountIN, AmmountOUT, UserRowNo, DocDate, Comment, ArchiveName, SysDate, SndFk_AccountSyscode, Fk_docSysCode, Fk_AccountSyscode FROM VWDocumentDetail_DocHeader WHERE (DocDate >= @FromDate) AND (ArchiveName = @ArchiveName) end else INSERT INTO @table_variable SELECT DetailCode, AmmountIN, AmmountOUT, UserRowNo, DocDate, Comment, ArchiveName, SysDate, SndFk_AccountSyscode, Fk_docSysCode, Fk_AccountSyscode FROM VWDocumentDetail_DocHeader WHERE (DocDate >= @FromDate) return END