SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: A. Najafzadeh(+98)913-125-3620
-- Date: 2021-01-17
-- Description: از این پروسیجر برای نمایش گزارش مرخصیها استفاده می شود
-- =============================================
CREATE PROCEDURE [dbo].[SptblAbsentsForReportTotalSum]
@DateStartFrom Date
,@DateEndTo Date
,@PersonnelID BIGINT = NULL
,@CompanyID INT = NULL
,@AbsentTypeID SMALLINT = NULL
AS
BEGIN
SET NOCOUNT ON;
Declare @Sql Nvarchar(Max),
@Paramlist Nvarchar(Max),
@AllColumn Nvarchar(max)
set @Sql = N' SELECT
PersonnelID,
AbsentTypeID,
CompanyID,
DiensNummer,
VorName,
NachName,
AbsentDescription,
SUM(AbsentDays) AS ToralDays ,
CompName '
SET @Sql += N' From [dbo].[XtblAbresntsForReport] Where 1 = 1 '
SET @Sql += N' and
(
(DateFrom BETWEEN @DateStartFrom AND @DateEndTo ) OR
(DateTo BETWEEN @DateStartFrom AND @DateEndTo) OR
(DateFrom <= @DateStartFrom AND DateTo >= @DateEndTo)
)
'
If Not @PersonnelID Is Null
Set @Sql += N' And @PersonnelID = PersonnelID '
If Not @CompanyID Is Null
Set @Sql += N' And @CompanyID = CompanyID '
If Not @AbsentTypeID Is Null
Set @Sql += N' And @AbsentTypeID = AbsentTypeID '
set @Sql += N' GROUP BY PersonnelID, AbsentTypeID, CompanyID, DiensNummer, VorName, NachName, AbsentDescription, CompName '
Set @Paramlist = '
@DateStartFrom Date
,@DateEndTo Date
,@PersonnelID BIGINT = NULL
,@CompanyID INT = NULL
,@AbsentTypeID SMALLINT = NULL'
Exec sp_executesql @Sql, @Paramlist,
@DateStartFrom
,@DateEndTo
,@PersonnelID
,@CompanyID
,@AbsentTypeID
END
|