December 2, 2007 at 4:29 pm
need help urgent
i need to create complex programing Stored Procedure for employees ID
(for test this Stored Procedure i start from only from one employee !!)
the insert must to fill all the month(for this employee) from 1 to >> 30/31 (from the first day of the month until the end of the month)
the value are ( first day ='1' for the second =1 for third =2 fourth =2 fifth3 sixth=3 sevent=4 eighth=5 and loop ................................
evry end of the month i need to create new month
-------------------------------------------------
for every month the same but like this !! >> 1, 1 ,2 ,2, 3 ,3 ,4 ,5---------
and in the next month >> it must start from where it end !!!
date in the month
date | value | employees ID
1.1.2007 | val=1 | 5555559
2.1.2007 | val=1 | 5555559
3.1.2007 | val=2 | 5555559
4.1.2007 | val=2 | 5555559
5.1.2007 | val=3 | 5555559
6=3 | 5555559
7=4 | 5555559
8=5
#####
9 val=1 | 5555559
10 val=1
11 val=2
12=2
13=3 | 5555559
14=3
15=4
16=5
#####
17=1 | 5555559
18=1
19=2
20=2
21=3
22=3
23=4
24=5
#####
25=1 | 5555559
26=1
27=2
28=2
29=3
30=3
31=4
and in the next month >> it must start from where it end !!!
from this values 1 1 2 2 3 3 4 5
like this
NEXT month(it must start from the values 5)
date in the month
date value | employees ID
1.2.2007=5 | 5555559
2.2.2007=1 | 5555559
3.2.2007=1
4=2
5=2
6=3
7=3
8=4
9=5
@@@@@
10=1
11=1
12=2
13=2
14=3
15=3
16=4
17=5
@@@@@
18=1
19=1
20=2
21=2
22=3
23=3
24=4
25=5
@@@@@
26=1
27=1
28=2
##############
NEXT month(it must start from the values 2)
date | value | employees ID
1 =2
2 =3
3 =3
4 =4
5 =5
.
.
.
-------------------------
TNX for WHO prepared to help me
December 2, 2007 at 4:49 pm
I'm sorry, I don't see the pattern...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2007 at 5:28 pm
December 2, 2007 at 5:29 pm
So, you're saying that no matter what happens, you want numbers that start on 01/01/2007 and follow the pattern of 1,1,2,2,3,3,4,5 for the full year?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2007 at 5:40 pm
what i mean is that
i need to to generate the next month that depending whre the last month end !
and i must check whre this employee ID (in the end of the month stop number and continue from whare it END !!)
like i do for this employee a "work roster" full month
and be considerate in the length of the month
and in the end
how to do it in 10 employees
that evry employee ID stop number (the VAL) is different
TNX
December 2, 2007 at 6:05 pm
midan1 (12/2/2007)
what i mean is thati need to to generate the next month that depending whre the last month end !
and i must check whre this employee ID (in the end of the month stop number and continue from whare it END !!)
like i do for this employee a "work roster" full month
and be considerate in the length of the month
TNX
Who boy! Big time languange barrier problems here, Midan... here's my best shot from what I understand... not sure where the employee comes in here...
DECLARE @Sequence TABLE
(
Modulo INT,
Value INT
)
INSERT INTO @Sequence
(Modulo,Value)
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
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value
FROM Master.dbo.spt_Values v,
@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2007 at 6:06 pm
shift
1=morning
2=evening
3=night
4=rest
5=home
December 2, 2007 at 6:23 pm
HI TNX
any idea how to insert to table with the employee and the val shift
maybe make 12 tables for each month in the year ???
TNX
December 2, 2007 at 7:31 pm
In your mind, you've adequately defined the requirements... in my mind, I still don't know what you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2007 at 1:33 am
HI TNX
CAN YOU SEE WHAT I mean
1) loop and insert 3 employees one after one to shift_table
2) evry time i generate the Stored Procedure i need only the next month
3) from the pattern of 1,1,2,2,3,3,4,5
4) in the next month >> it must start from where it end !!!
-------------------------
the shift_table
-------------------------
ID | date | shift
111157 1-01-07 1
222257 1-01-07 2
333367 1-01-07 3
111167 2-01-07 1
222257 2-01-07 2
333367 3-01-07 3
111167 3-01-07 2
222257 3-01-07 3
333367 3-01-07 4
111167 4-01-07 2
222257 4-01-07 3
333367 4-01-07 5
111167 4-01-07 3
222257 4-01-07 4
333367 4-01-07 1
111167 5-01-07 3
222257 5-01-07 5
333367 5-01-07 1
111167 6-01-07 4
222257 6-01-07 1
333367 6-01-07 2
111167 7-01-07 5
222257 7-01-07 1
333367 7-01-07 2
-----------------
DECLARE @Sequence TABLE
(
Modulo INT,Value INT,ID INT
)
DECLARE @val_id INT
set @val_id='111157'
INSERT INTO @Sequence
(Modulo,Value)
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
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value ,@val_id AS ID
FROM Master.dbo.spt_Values v,@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
-------------------------------------------------------------------------
TNX for help
December 3, 2007 at 12:23 pm
Build a lookup table with shift_index and shift_code as follows:
shift_index shift_code
0 1
1 1
2 2
3 2
4 3
5 3
6 4
7 5
Store the shift_index in your monthly tracking table along with the shift_code. Then, for each new month to be inserted, for each employee, the new shift_index will be equal to the last month's shift_index for that employee plus 1, modulo 8. The new shift code for that employee for that month is just done from a lookup of the new shift_index in the above lookup table.
--Ed
December 3, 2007 at 1:23 pm
hi am stuck in
someone can fix this for me
------------------------------------------------------------------
this is my problem and i need to add this (add pattern)=UNIT
add another pattern 1 , 2 , 3 , 4
---------------------------------------------------------
DECLARE @Sequence TABLE
(
Modulo INT,Value INT,ID INT,unit INT,Val INT
)
DECLARE @val_id INT
set @val_id='5555567'
INSERT INTO @Sequence
(Modulo,Value)
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
(unit,Val)
SELECT 0,1 UNION ALL --UNIT 1
SELECT 1,2UNION ALL -- UNIT 2
SELECT 2,3 UNION ALL -- UNIT 3
SELECT 3,4 -- UNIT 4
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value ,@val_id AS ID,s.Val
FROM Master.dbo.spt_Values v,@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
and (v.Number-1) % 4 = s.val
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
--------------------------------------------------
this is the orginal code by-Jeff Moden
-------------------------------------
DECLARE @Sequence TABLE
(
Modulo INT,Value INT,ID INT
)
DECLARE @val_id INT
set @val_id='5555567'
INSERT INTO @Sequence
(Modulo,Value)
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 -- ????
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value ,@val_id AS ID
FROM Master.dbo.spt_Values v,@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
TNX
December 3, 2007 at 2:50 pm
DECLARE @Sequence TABLE
(
Modulo INT,
Value INT
)
INSERT INTO @Sequence
(Modulo,Value)
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 INTO emp_shift
select a.empID, dateadd(mon,1,a.lastdate), (a.lastmodulo +1) % 8, b.value
from
(select empID, date as lastdate, modulo as lastmodulo
from emp_shift
where date = (select max(date) from emp_shift)
) as a
INNER JOIN @Sequence as b
ON (a.lastmodulo +1) % 8 = b.modulo
Two parts: first build a lookup table (@Sequence) that tracks the order in which the different types of shifts are assigned to each employee. Then in the main table used to track which shift an employye worked in a given month, define four columns: employee ID, the date when the shift change begins, the counter which identifies where in the shift sequence the employee is (named modulo in this example), and the code identifying the shift type being worked (named value here). The subquery in the insert statement selects all employees that worked last month, and returns their id, the last month's date, and the modulo value. For the new data being inserted, we leave the employee id alone, add 1 month to the date, add 1 to the modulo field (mod 8), and load the shift code from the lokup field that matches the new modulo value as this new month's shift code.
December 3, 2007 at 3:35 pm
can you please to generate the code to build the tables
1 ) emp_shift
i get error
----------
Msg 155, Level 15, State 1, Line 19
'mon' is not a recognized dateadd option.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'as'.
-----------------------------------------
2)how add another pattern (add pattern)=UNIT
the pattern is 1 , 2 , 3 , 4
that once in 30 day the employee change location (the UNIT)
so if the employee for example start in location (the UNIT)=1
in the 17 of month
in the next month in 18 the UNIT
location (the UNIT)=2
like this
once in 30 day the employee change location (from the pattern is 1 , 2 , 3 , 4)
i am appraiser your help
i am stuck in
TNX ilan
December 3, 2007 at 11:37 pm
ok
how to add this to this StoredProcedure
add another field "UNIT
so the table be like this
|empID| |date| |Modulo| |shift_code| |UNIT|
999777 |01/01/2007 00:00:00||0| |1| |1|
888888 |01/01/2007 00:00:00||0| |2| |4|
--------------------------
add another pattern (add pattern)=UNIT
the pattern is 1 , 2 , 3 , 4
that once in 30 day the employee change location (the UNIT)
so if the employee for example start in location (the UNIT)=1
in the 17 of month
in the next month in 18 the UNIT
location (the UNIT)=2
like this
once in 30 day the employee change location (from the pattern is 1 , 2 , 3 , 4)
----------------------------
DECLARE @Sequence TABLE
(
Modulo INT,
Value INT
)
INSERT INTO @Sequence
(Modulo,Value)
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 INTO emp_shift
select a.empID, dateadd(month,1,a.lastdate), (a.lastmodulo +1) % 8, b.value
from
(select empID, date as lastdate, modulo as lastmodulo
from emp_shift
where date = (select max(date) from emp_shift)
) as a
INNER JOIN @Sequence as b
ON (a.lastmodulo +1) % 8 = b.modulo
TNX
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply
