December 4, 2007 at 4:02 am
Based on jeff's use of a table for the pattern...
-- 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
I make no claims about efficiency...
Derek
December 4, 2007 at 5:36 am
:D:DWOW WOW WOW :hehe:
it work i love you men
---------------
please
but there is a problem for me i need the unit also
id date(id) UNIT(date) UNIT
1234501/01/2007 00:00:00 17/01/2007 00:00:00 1
8887701/05/2007 00:00:00 23/01/2007 00:00:00 3
9999406/05/2007 00:00:00 31/01/2007 00:00:00 4
-----------------------
CAN YOU ADD THE UNIT also because the employee can change location (unit 1 2 3 4) for example in the 17 of the month??
TNX TNX TNX X (10000000000000000000000000000000000000000000000000000)
December 8, 2007 at 11:48 am
need help with this function !
first prolem
this line
--------------------------------------------------------
(convert(int,(datediff(dd,b.unit_date,d.dt) / 30)) % 4) + 1)
-------------------------------------------------------------
i don't get in unit value the correct division the "/30"
how to handling in month 28 days, 29 days, 30 days ,31 days ???
i only need to change the unit value evry first day of the month !!!
like checking the value of the previous Month and change it from "1" to "2" and next month "3" ....... 4 >> 1 >> 2...
the employee move location evry first of the month
second problem
how to add value date for field "unit"
i need to separate the common date in the field "unit" and field "empid"
need to add another field date for the unit
so i can fill the table like this
insert empbase (empid,basedate,unit_date) values (12345,'2007/1/1','2007/3/1')
like this i can
1) set date for "basedate" start DATE
2)set date for "unit_date" start DATE(once a month in the first day=1)
so how to fix this function and to add date for "unit_date" reference
like this i can add to the employee a shift forom date ="basedate"
and a UNIT from "unit_date" (once a month in the first day=1)
the "unit_date" is the start date for add to the employee the UNIT value (1-4)
TNX
-- 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
December 9, 2007 at 1:29 am
this function working ok whan spliting the employee shift value (1,1,2,2,3,3,4,5) .
but for the "UNIT" value for
the employee i need that value "UNIT" the value(1,2,3,4)
change evry first day in the month the "1" and only in the first day in the month !!
need to add another field date for the unit
so whan i need to change the "UNIT" value for the employee in the first day in the month
this function take the value date from the the new date call "UNIT_DATE"
like this thre no connection between "UNIT_DATE" and "basedate"
field "basedate" for the shift value (1,1,2,2,3,3,4,5)
field "Unit_date" for the UNIT value (1,2,3,4)
need help to do it
TNX
December 10, 2007 at 1:57 am
Please post what you've tried and, if I have time, I'll see where you are going wrong if I have time. Like most people here, I don't get paid for writing code for other people!
Derek
December 10, 2007 at 7:22 am
Derek Dongray (12/10/2007)
Please post what you've tried and, if I have time, I'll see where you are going wrong if I have time. Like most people here, I don't get paid for writing code for other people!
Derek Dongray i am apologize this not what i mean
i only ask for help
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply