complex programing HELP-Stored Procedure insert value after value like this into #temp_table

Question

Post reply

complex programing HELP-Stored Procedure insert value after value like this into #temp_table

midan1

SSCertifiable

Points: 5100
More actions
December 2, 2007 at 4:29 pm

#180875

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

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 1

I'm sorry, I don't see the pattern...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

midan1 SSCertifiable Points: 5100 More actions · Answer 2

a link to the picture pattern

http://www.turboimagehost.com/p/29550/1.jpg.html

TNX

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

midan1 SSCertifiable Points: 5100 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 5

midan1 (12/2/2007)
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
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

midan1 SSCertifiable Points: 5100 More actions · Answer 6

midan1

SSCertifiable

Points: 5100

December 2, 2007 at 6:06 pm

#756350

shift

1=morning

2=evening

3=night

4=rest

5=home

midan1 SSCertifiable Points: 5100 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

In your mind, you've adequately defined the requirements... in my mind, I still don't know what you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

midan1 SSCertifiable Points: 5100 More actions · Answer 9

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

Ed Heistand Ten Centuries Points: 1233 More actions · Answer 10

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

midan1 SSCertifiable Points: 5100 More actions · Answer 11

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

Ed Heistand Ten Centuries Points: 1233 More actions · Answer 12

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.

midan1 SSCertifiable Points: 5100 More actions · Answer 13

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

midan1 SSCertifiable Points: 5100 More actions · Answer 14

ok

how to add this to this StoredProcedure

add another field "UNIT

so the table be like this

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