December 17, 2007 at 3:11 pm
need help locate BUG in function condition !
this my function and i have sum problem in it
this is the condition !
case
--Friday
when wday=6 and (select shiftType from rs d where d.rn=r.rn-1 and d.empid=r.empid)=2 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)<>2 then 2
when wday=6 and (select shiftType from rs d where d.rn=r.rn-1 and d.empid=r.empid)=3 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)<>3 then 3
--Saturday
when wday=7 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)=2 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)<>2 then 2
when wday=7 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)=3 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)<>3 then 3
else shiftType end as newShifType
from rs r
)
the problem
it not symmetrical equal the shift
in this condition evry employee get (1,1,2,2,3,3,4,5)
now if the "condition of Thursday" on value =2
do this (1,1,2,2,2,3,4,5)
give less "3"
it must do this
(1,1,2,2,2,3,3,4,5)
and
if the "condition of Thursday" on value =3
do this (1,1,2,2,3,3,3,5) no 4 it subtract 4
it must do this
(1,1,2,2,3,3,3,4,5)
for example if the employee don't get the value 4
4= rest at home - after 3 shift night he need to rest
the all function
alter function shifts (@mth tinyint,@yr smallint)
returns table
as
return
(
with ptrn as
(
select 1 as shiftType, 1 as prn
union all
select 1 ,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
union all
select 3,6
union all
select 4,7
union all
select 5,8
),
emp as (
select
e.empid,
f.date as basedate,
datepart(dw,f.date ) as wday,
row_number() over (partition by empid order by f.date ) as rn,
unit = (DATEDIFF(MONTH, e.unit_date, f.[DATE])% 4) + 1
from empbase e ,[dbo].[F_TABLE_DATE](dateadd(m,@mth-1,dateadd(yy,@yr-1900,0)),dateadd(m,@mth,dateadd(yy,@yr-1900,0))) f
where f.DATE >= dateadd(m,@mth-1,dateadd(yy,@yr-1900,0))
and f.DATE < dateadd(m,@mth,dateadd(yy,@yr-1900,0))
),
emp_r as (
select empid,basedate,wday ,rn,unit,
row_number() over (partition by empid,((rn-1)/8) order by basedate) as rnd
from emp
),
rs as (
select *
from emp_r e
inner join ptrn p
on e.rnd=p.prn)
select *,
case
--Friday
when wday=6 and (select shiftType from rs d where d.rn=r.rn-1 and d.empid=r.empid)=2 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)<>2 then 2
when wday=6 and (select shiftType from rs d where d.rn=r.rn-1 and d.empid=r.empid)=3 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)<>3 then 3
--Saturday
when wday=7 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)=2 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)<>2 then 2
when wday=7 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)=3 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)<>3 then 3
else shiftType end as newShifType
from rs r
)
TNX
December 18, 2007 at 1:56 am
Have you considered using a calendar table? See this article for a few ideas:
For a better answer, please, describe your requirements in more detail (in business terms - tell us *what* you want, not *how* you want it done).
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply