Include/Exclude Data based on certain time frame

[scoobyroo]

The following code is part of a stored procedure that will show worked day versus when the worked time was entered into the system and the difference in days. They have added a new requirement that I can't seem to incorporate into what I have. They want to exclude clients 9012 - 9021 only for Feb 2009 - May 2009. Starting in June 2009, it is ok to include the clients. What would be the best way to approach this?




ALTER PROCEDURE dbo._usp_report_TIME_AvgDays_WorkPost

@EndPeriod INT

AS

DECLARE @Period1 INT
, @Period2 INT
, @Period3 INT
, @Period4 INT
, @Period5 INT


SELECT @Period5 = CASE WHEN RIGHT(@EndPeriod,2) > 1 THEN @EndPeriod - 1 ELSE @EndPeriod - 89 END
SELECT @Period4 = CASE WHEN RIGHT(@Period5,2) > 1 THEN @Period5 - 1 ELSE @Period5 - 89 END
SELECT @Period3 = CASE WHEN RIGHT(@Period4,2) > 1 THEN @Period4 - 1 ELSE @Period4 - 89 END
SELECT @Period2 = CASE WHEN RIGHT(@Period3,2) > 1 THEN @Period3 - 1 ELSE @Period3 - 89 END
SELECT @Period1 = CASE WHEN RIGHT(@Period2,2) > 1 THEN @Period2 - 1 ELSE @Period2 - 89 END


SELECT p.EMPLOYEE_CODE
, p.EMPLOYEE_NAME
, m.CLIENT_CODE
, p.DEPT
, d.DEPT_NAME
, p.PROF
, pc.PROF_CTR_DESC
, p.PERSNL_TYP_CODE
, pt.PERSNL_TYP_DESC
, (DATEPART(yy, t.TRAN_DATE) * 100) + DATEPART(mm, t.TRAN_DATE)
, t.TRAN_DATE
, t.POST_DATE
, DATEDIFF(Day, t.TRAN_DATE, t.POST_DATE)
FROM ADR_LIVE.dbo.TAT_TIME t
INNER JOIN ADR_LIVE.dbo.HBM_PERSNL p ON t.TK_EMPL_UNO = p.EMPL_UNO
INNER JOIN ADR_LIVE.dbo.HBL_DEPT d ON p.DEPT = d.DEPT_CODE
INNER JOIN ADR_LIVE.dbo.HBL_PROF_CTR pc ON p.PROF = pc.PROF_CTR_CODE
INNER JOIN ADR_LIVE.dbo.HBL_PERSNL_TYPE pt ON p.PERSNL_TYP_CODE = pt.PERSNL_TYP_CODE
INNER JOIN ADR_LIVE.dbo.HBM_MATTER m ON t.MATTER_UNO = m.MATTER_UNO
WHERE t.WIP_STATUS in ('B','P','W')
AND (DATEPART(yy,tran_date)*100)+DATEPART(mm,tran_date) between @Period1 and @EndPeriod
AND t.TRAN_DATE >= '2/1/09'

 

[bborissov]

Not sure what you want, but try this:

....
WHERE t.WIP_STATUS in ('B','P','W')
    AND (DATEPART(yy,tran_date)*100)+DATEPART(mm,tran_date) between @Period1 and @EndPeriod
    AND t.TRAN_DATE >= '20090201'
    AND 1 = CASE WHEN ClientId BETWEEN 9012 AND 9021
                      AND YEAR(DateField) = 2009
                      AND MONTH(DateField) BETWEEN 2 AND 5
                 THEN 0
            ELSE 1 END
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PDreyer]

scoobyroo, Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones.
The existing one can be changed to

...
declare @edt datetime
select @edt=dateadd(mm,1,convert(char(8),@EndPeriod*100+1))
select ...
where WIP_STATUS in ('B','P','W')
  and TRAN_DATE>=dateadd(mm,-6,@edt)
  and TRAN_DATE<@edt
  and TRAN_DATE>='20090201'

to add the new requirement just add e.g.

  and (MATTER_UNO not between 9012 and 9021 
       or TRAN_DATE>='20090601')