locate BUG in function symmetrical equal the shift

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

locate BUG in function symmetrical equal the shift

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2007-12-17 : 17:00:58

need help locate BUG in function !
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 17:04:13

And where is the sample data? And expected output?
How could we else know if there is a bug or not?

E 12°55'05.25"
N 56°04'39.16"

midan1
Starting Member

39 Posts

Posted - 2007-12-17 : 17:26:38

see
look this is the right column

day in the week is after the the date left

quote:

4807756 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
4807756 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
4807756 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
4807756 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
4807756 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
4807756 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
4807756 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
4807756 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
4807756 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
4807756 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
4807756 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
4807756 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
4807756 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
4807756 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
4807756 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
4807756 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
4807756 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
4807756 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
4807756 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
4807756 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
4807756 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
4807756 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
4807756 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
4807756 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
4807756 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
4807756 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
4807756 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
4807756 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
4807756 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
4807756 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
4807756 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
9819590 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
9819590 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
9819590 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
9819590 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
9819590 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
9819590 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
9819590 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
9819590 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
9819590 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
9819590 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
9819590 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
9819590 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
9819590 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
9819590 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
9819590 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
9819590 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
9819590 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
9819590 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
9819590 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
9819590 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
9819590 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
9819590 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
9819590 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
9819590 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
9819590 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
9819590 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
9819590 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
9819590 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
9819590 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
9819590 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
9819590 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
28895472 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
28895472 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
28895472 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
28895472 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
28895472 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
28895472 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
28895472 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
28895472 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
28895472 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
28895472 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
28895472 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
28895472 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
28895472 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
28895472 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
28895472 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
28895472 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
28895472 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
28895472 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
28895472 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
28895472 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
28895472 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
28895472 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
28895472 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
28895472 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
28895472 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
28895472 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
28895472 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
28895472 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
28895472 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
28895472 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
28895472 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
51679900 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
51679900 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
51679900 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
51679900 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
51679900 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
51679900 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
51679900 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
51679900 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
51679900 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
51679900 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
51679900 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
51679900 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
51679900 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
51679900 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
51679900 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
51679900 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
51679900 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
51679900 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
51679900 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
51679900 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
51679900 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
51679900 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
51679900 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
51679900 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
51679900 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
51679900 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
51679900 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
51679900 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
51679900 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
51679900 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
51679900 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
54693130 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
54693130 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
54693130 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
54693130 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
54693130 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
54693130 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
54693130 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
54693130 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
54693130 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
54693130 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
54693130 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
54693130 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
54693130 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
54693130 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
54693130 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
54693130 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
54693130 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
54693130 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
54693130 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
54693130 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
54693130 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
54693130 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
54693130 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
54693130 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
54693130 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
54693130 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
54693130 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
54693130 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
54693130 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
54693130 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
54693130 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
55901961 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
55901961 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
55901961 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
55901961 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
55901961 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
55901961 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
55901961 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
55901961 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
55901961 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
55901961 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
55901961 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
55901961 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
55901961 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
55901961 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
55901961 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
55901961 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
55901961 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
55901961 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
55901961 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
55901961 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
55901961 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
55901961 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
55901961 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
55901961 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
55901961 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
55901961 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
55901961 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
55901961 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
55901961 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
55901961 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
55901961 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
56157795 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
56157795 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
56157795 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
56157795 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
56157795 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
56157795 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
56157795 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
56157795 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
56157795 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
56157795 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
56157795 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
56157795 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
56157795 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
56157795 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
56157795 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
56157795 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
56157795 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
56157795 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
56157795 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
56157795 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
56157795 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
56157795 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
56157795 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
56157795 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
56157795 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
56157795 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
56157795 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
56157795 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
56157795 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
56157795 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
56157795 2007-01-31 00:00:00.000 3 31 1 7 4 7 4
56496581 2007-01-01 00:00:00.000 1 1 1 1 1 1 1
56496581 2007-01-02 00:00:00.000 2 2 1 2 1 2 1
56496581 2007-01-03 00:00:00.000 3 3 1 3 2 3 2
56496581 2007-01-04 00:00:00.000 4 4 1 4 2 4 2
56496581 2007-01-05 00:00:00.000 5 5 1 5 3 5 3
56496581 2007-01-06 00:00:00.000 6 6 1 6 3 6 3
56496581 2007-01-07 00:00:00.000 7 7 1 7 4 7 3
56496581 2007-01-08 00:00:00.000 1 8 1 8 5 8 5
56496581 2007-01-09 00:00:00.000 2 9 1 1 1 1 1
56496581 2007-01-10 00:00:00.000 3 10 1 2 1 2 1
56496581 2007-01-11 00:00:00.000 4 11 1 3 2 3 2
56496581 2007-01-12 00:00:00.000 5 12 1 4 2 4 2
56496581 2007-01-13 00:00:00.000 6 13 1 5 3 5 3
56496581 2007-01-14 00:00:00.000 7 14 1 6 3 6 3
56496581 2007-01-15 00:00:00.000 1 15 1 7 4 7 4
56496581 2007-01-16 00:00:00.000 2 16 1 8 5 8 5
56496581 2007-01-17 00:00:00.000 3 17 1 1 1 1 1
56496581 2007-01-18 00:00:00.000 4 18 1 2 1 2 1
56496581 2007-01-19 00:00:00.000 5 19 1 3 2 3 2
56496581 2007-01-20 00:00:00.000 6 20 1 4 2 4 2
56496581 2007-01-21 00:00:00.000 7 21 1 5 3 5 2
56496581 2007-01-22 00:00:00.000 1 22 1 6 3 6 3
56496581 2007-01-23 00:00:00.000 2 23 1 7 4 7 4
56496581 2007-01-24 00:00:00.000 3 24 1 8 5 8 5
56496581 2007-01-25 00:00:00.000 4 25 1 1 1 1 1
56496581 2007-01-26 00:00:00.000 5 26 1 2 1 2 1
56496581 2007-01-27 00:00:00.000 6 27 1 3 2 3 2
56496581 2007-01-28 00:00:00.000 7 28 1 4 2 4 2
56496581 2007-01-29 00:00:00.000 1 29 1 5 3 5 3
56496581 2007-01-30 00:00:00.000 2 30 1 6 3 6 3
56496581 2007-01-31 00:00:00.000 3 31 1 7 4 7 4

TNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 03:23:59

You're welcome.

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-18 : 04:49:56

Peso TNX

ilan
Starting Member

21 Posts

Posted - 2007-12-19 : 08:58:50

please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 09:03:04

We don't even know the business rules for calculating anything.

1) Explain what you have
2) Explain what you want

Post proper and accurate sample data in this format

DECLARE @Sample TABLE (Col1 INT, Col2 DATETIME, etc etc)
INSERT @Sample
SELECT 1, '20070801' UNION ALL
SELECT 2, '20070901'

Then post what the expected output is based on the above provided sample data.

And last you explain the business rules how you come to that conclusion.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 09:04:17

If the above post is not clear to you, you have to read and follow the advice in this post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-19 : 09:06:08

oh dear, still on the same issue ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93917

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 09:12:57

I see other people as well has asked for the very same information I asked above...

E 12°55'05.25"
N 56°04'39.16"

midan1
Starting Member

39 Posts

Posted - 2007-12-19 : 19:56:59

i explain

i have function that division for the employee shifts

generate daily shift pattern 1,1,2,2,3,3,4,5

1 =morning

2=evening

3=night

4=rest

5=home

this function go to table "empbase" check on filed "basedate" and last shift month that the employee finish !
and from this point
the function give to the employee shifts 1,1,2,2,3,3,4,5 for evry day one shift !

if the employee on Thursday start shift 2 OR 3 the employee must finish the week-end

for example

employee ID=12345 get "work roster " for December from first of the month to the end of the month

from series of shifts 1,1,2,2,3,3,4,5 evry day one

and on Thursday if the employee start shift 2 or 3 the employee must finish the week-end

Thursday=2

Friday=2

Saturday=2

OR

Thursday=3

Friday=3

Saturday=3

and olso i have a rule
so this functin do this
generate daily shift pattern 1,1,2,2,3,3,4,5,...

my rule
if the employee get the shift 2 OR 3 on Thursday !!!
but only if it Thursday !
(the week-end start from Thursday until Sunday morning)

the order for this employee id be 2,2,2 or 3,3,3
i explain
the employee must start the week-end and finish it with the same shift
but only if it start a series 2 OR 3 (2=evening 3=night) ON Thursday .
and after continue
if the employee on Thursday start shift 2=evening than after 2,2,2 3,3,4,5,1,1,2,2,3,3,4,5,..
if the employee on Thursday start shift 3=night than after 3,3,3 4,5,1,1,2,2,3,3,4,5,..

so like this if the employee on Thursday start a series value 2 OR 3 the employee must to end it on the week-end from Thursday until Sunday morning
-------------
this is my table "empbase"
the last date that the emplpyee work with last "shift" asn last UNIT date
empid basedate unit_date shift
----------- ----------- ------- -----
28895472 28/04/2007 00:00:00 01/01/2007 00:00:00 1
56496581 28/04/2007 00:00:00 01/01/2007 00:00:00 3
56157795 28/04/2007 00:00:00 01/01/2007 00:00:00 5
51679900 28/04/2007 00:00:00 01/01/2007 00:00:00 4
54693130 28/04/2007 00:00:00 01/01/2007 00:00:00 4
-----------------------------------

so friends need saving-for this complex function
can someone help ?
from my boos to firing me !!

and and olso get rid the function F_TABLE_DATE !

TNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 21:25:11

The main problem here is that if you only go for the last date and that day's shift, you can't tell which shift to continue with.

For example; the shift for last date is 2.
How to you know if you should continue with the second 2-shift, or continue with first 3-shift?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 21:30:35

quote:
Originally posted by midan1

so friends need saving-for this complex function
can someone help ?
from my boos to firing me !!

If you want to keep your job, then maybe you should listen to us and provide the information we request?

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-20 : 02:53:06

ok i think i fix this the code is below
------------------------------------------------
but i have main problem how to check the last shift + last date
from my table "empbase"
the problem in this line !!



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))

my table
----------------------
empid basedate unit_date shift
----------------------------------------------
28895472 28/04/2007 28/01/2007 1
56496581 28/04/2007 30/01/2007 3
56157795 28/04/2007 11/01/2007 5
51679900 28/04/2007 12/01/2007 4
54693130 28/04/2007 10/01/2007 4
----------------------------------------
this is the last changes in my Function

---------------------------------------


USE [nili]
GO
/****** Object:  UserDefinedFunction [dbo].[shif]    Script Date: 12/19/2007 16:10:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[shif] (@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)=1 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)<>1 then 1
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
--sun
when wday=7 and (select shiftType from rs d where d.rn=r.rn-2 and d.empid=r.empid)=1 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)<>1 then 1
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=7and (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
--mon
when wday=1 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)=1 and (select shiftType from rs d where d.rn=r.rn-4 and d.empid=r.empid)<>1 then 2
when wday=1 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)=2 and (select shiftType from rs d where d.rn=r.rn-4 and d.empid=r.empid)<>2 then 3
when wday=1 and (select shiftType from rs d where d.rn=r.rn-3 and d.empid=r.empid)=3 and (select shiftType from rs d where d.rn=r.rn-4 and d.empid=r.empid)<>3 then 4
when wday=2 and (select shiftType from rs d where d.rn=r.rn-4 and d.empid=r.empid)=1 and (select shiftType from rs d where d.rn=r.rn-5 and d.empid=r.empid)<>1 then 2
else shiftType end as newShifType

from rs r

)

TNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 03:59:54

Exactly!

How do you know that for this record

28895472 28/04/2007 28/01/2007 1

that the present "1" shift is the first or second "1" shift?
If it is the first "1" shift, then there should be another "1" shift for the 29th of January.
If it is the second "1" shift, then there should be a "2" shift for the 29th of January.

Unless there are some business rules you haven't told us?

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-20 : 04:54:43

hi Peso

think i can do this no ??
---------------------------------------------
select id, max(Date), max(unit) ,max(ShiftID)
from empbase
group by id
-------------------------------------------------
like this i can know what is the last shift and start from this point no ?

Peso can you help me to make peace this puzzle ?
my problem that i have many prts of good codes

see this code
BUT it not suitable for all component
and it is not function !!



-- need a list of employee ids with a basedate set to when they start with shift_code=1, unit=1
-- this is a minimal tale to show the format
-- extra columns could be added with other info (e.g. name) 
create table empbase (
empid int,
basedate datetime
)

-- fill with test data
insert empbase (empid,basedate) values (12345,'2007/1/1')
insert empbase (empid,basedate) values (88877,'2007/1/5')
insert empbase (empid,basedate) values (98765,'2007/1/20') 
insert empbase (empid,basedate) values (99994,'2007/6/5')
go
-------------------------------
create function shifts (
@mth tinyint,
@yr smallint
)
returns
@table_var
table (
empid int, 
date datetime,
shift_code int,
unit int)
as
-- generate daily shift pattern 1,1,2,2,3,3,4,5,... changing units 1,2,3,4,... every 30 days.
begin
declare @d1 datetime
declare @d31 datetime
set @d1=convert(datetime,convert(char(8),@ yr*10000+@mth*100+1))
set @d31=dateadd(dd,-1,dateadd(mm,1,@d1))

;with n01 (i) as (select 0 as 'i' union all select 1)
,seq (n) as (
select 
d1.i+(2*d2.i)+(4*d3.i)+(8*d4.i)+(16*d5.i) as 'n'
from 
n01 as d1
cross join
n01 as d2
cross join 
n01 as d3
cross join
n01 as d4
cross join
n01 as d5)
,dates (dt) as (
select
dateadd(dd,n,@d1) as 'dt'
from
seq
where
dateadd(dd,n,@d1) <= @d31)
,modval (mod,val) as (
select 0,1 union all
select 1,1 union all
select 2,2 union all
select 3,2 union all
select 4,3 union all
select 5,3 union all
select 6,4 union all
select 7,5)
insert @table_var
select
b.empid ,
d.dt,
(select val from modval where mod=(datediff(dd,b.basedate,d.dt) % 8)),
((convert(int,(datediff(dd,b.basedate,d.dt) / 30)) % 4) + 1)
from
empbase b, dates d
where
b.basedate <= d.dt
return 
end
go

 

 

-- test for various months
select * from shifts(1,2007) order by empid,date
select * from shifts(2,2007) order by empid,date
select * from shifts(3,2007) order by empid,date
select * from shifts(4,2007) order by empid,date 
select * from shifts(5,2007) order by empid,date
select * from shifts(12,2007) order by empid,date

ilan
Starting Member

21 Posts

Posted - 2007-12-20 : 06:02:42

yes i have one
business rule
this
-------------------------------------------------------------
unit = (DATEDIFF(MONTH, e.unit_date, f.[DATE])% 4) + 1
--------------------------------------------------------
the employee move location evry month from unit 1,2,3,4
once a month
this line work ok 100% it change the location from the unit_date
TNx

midan1
Starting Member

39 Posts

Posted - 2007-12-21 : 07:52:47

maybe any genius can rescue me

Friends!

TNX for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 08:06:25

No one can rescue you until you provide the information we need.
We have asked for this informatin at least zillion of times.

E 12°55'05.25"
N 56°04'39.16"

midan1
Starting Member

39 Posts

Posted - 2007-12-21 : 08:53:28

Peso please try to understand me and help(my English is not perfect)
i am desperate

explanation

i have only one table of employee
with fields

1 empid
2 basedate
3 shift
4 unit_date
5 unit_val

A)the field basedate + shift
is "reference" to give the employee to next month the shift order (1,1,2,2,3,3,4,5)

so if the employee finish in shift=3
then next month the employee start with shift=4

B)the field unit_date + unit_val
unit_date is "reference" to give the employee new location to next month only once a month 1 to 4 (1,2,3,4) evry month next number

C) i need to take care to fill the long weekend =Friday + Saturday + Sunday
so if the employee start a long weekend with shift (but only if he start) shfit value 1 or 2 or 3

1=morning
2=evening
3=night
----------------------
4=day rest
5=home

i perform to you the ideas and direction
and olso examples of codes that don't working 100%
if you think that is missing OK
try to help me to do it
this all the information
Peso TNX

midan1
Starting Member

39 Posts

Posted - 2007-12-21 : 16:39:01

i think i have idea how to start the new shift month



----------------------------------------this is the last day of the last month

select id, max(Date), max(unit) ,max(ShiftID)
from employee
group by id

---------------------------------------this is the day before last day in the last month

select id, max(Date)-1, max(unit) ,max(ShiftID)
from employee
group by id

so if i know the last day last month and
day before last day in the month
i can do this
---------------



SELECT 

 [new_shift_next_month] =   
 CASE 
 WHEN     
select id, max(Date)-1, max(unit)-1 ,max(ShiftID)-1
from employee
group by id
ShiftID=5
and
select id, max(Date), max(unit) ,max(ShiftID)
from employee
group by id
ShiftID=1
than [new_shift_next_month]=1

like this
if ShiftID (day before last day in the month)=5
AND
if ShiftID (day before last day in the month)=1
then
new_shift_next_month=1

this is the order

if
5
1
than 1
------
if
1
1
then 2
if
1
2
then 2
if
2
2
then 3
if
2
3
then 3
if
3
3
then 4
if
3
4
then 5
if
4
5
then 1

---------------
and after this i can go to the orginal order
(1,1,2,2,3,3,4,5)

TNX
wait for any help

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources