January 26, 2008 at 12:52 pm
ok
see my code for backward insert
my code start from last day and insert the list of shift backward
my code all the time start with the same shift
whan i try to do the same thing in MATT MILLER code
i canot do this and insert shift backward and date backward
MATT MILLER code is quick and wise !!
TNX
see example
empid ShiftDate
----------------------
1111 17/03/2008 8
1111 16/03/2008 7
1111 15/03/2007 6
1111 17/03/2008 5
1111 16/03/2008 4
1111 15/03/2007 3
1111 14/03/2008 2
1111 13/03/2008 1
1111 12/03/2007 8
2222 17/03/2008 8
2222 16/03/2008 7
2222 15/03/2007 6
2222 17/03/2008 5
2222 16/03/2008 4
2222 15/03/2007 3
2222 14/03/2008 2
2222 13/03/2008 1
2222 12/03/2007 8
this code work shift backward and date backward
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 8 UNION ALL
SELECT 7 UNION ALL
SELECT 6 UNION ALL
SELECT 5 UNION ALL
SELECT 4 UNION ALL
SELECT 3 UNION ALL
SELECT 2 UNION ALL
SELECT 1
set
@i=@i+1
end
DECLARE
@Start_Date datetime
DECLARE
@End_Date datetime
-- first day of next month
SET
@Start_Date = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) )-1
-- last day of next month
SET
@End_Date = DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )-1
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL)
INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,8,CONVERT(DATETIME, '01/03/2008', 103), CONVERT(DATETIME, '17/03/2008', 103)union all
SELECT 222222,8,CONVERT(DATETIME, '01/03/2008', 103), CONVERT(DATETIME, '17/03/2008', 103)union all
SELECT 222222,8,CONVERT(DATETIME, '01/03/2008', 103), CONVERT(DATETIME, '10/03/2008', 103)
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,@End_Date,emp.StartDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID ,@last_shift_id ,@StartDate,@EndDate
SET @current = @EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
While (@current > @StartDate)
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Sunday ' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5,8))
OR
-- if it is Saturday nd we are on one of the first shift we don't move to next shift type .
(DATENAME(dw ,@current) = 'Saturday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in (1,3,5))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,-1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @EndDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
January 26, 2008 at 1:16 pm
Jeff Moden (1/24/2008)
the BUG
on date '15/03/2008' the shift must start with 8
on date '12/03/2008' the shift must start with 8
on date '11/03/2008' the shift must start with 8
WHY? Why do you have two adjacent days with the same shift and 3 days out of 5 with the same shift? What are the rules for this calculation? I'm still waiting to see a complete set of rules so I can stop trying to guess what you actually want to do. You started out with a simple request and you keep adding to the problem. Please summarize the rules...
Please post a complete correctly ordered set of rules for this problem and some correct sample data along with reasons why the data looks like it does. In most previous examples you've given, you've not shown anytime where the same shift is used on two adjacent days like you have in the quote above.
soory Jeff i explain
i need go generate a work roster for next month
and the secretary want to pick only one date for the next month for each employee
for example
empid date
--------------
1111 17/03/2008
2222 10/03/2008
3333 05/03/2008
----------------------
now i have tow stord prosege
one stord prosege go forward 17/03/2008 >>> to 31/03/2008 (end of the month) and the shift go forward(1,2,3,4,5,6,7,8)
the seconde stord prosege go backward 17/03/2008 >>> to 01/03/2008 (start fo the month)and the shift go backward (8,7,6,5,4,3,2,1)
this is what i need
TNX
January 26, 2008 at 5:41 pm
So, why do you have to count down from the middle of the month and count up from that same date to the end. And, why does the secretary have to pick the date?
And, why can't you just count up from the beginning of the month?
I guess I just don't understand the need for all the hoops you're running through to make a simple schedule.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2008 at 1:47 am
Jeff Moden (1/26/2008)
So, why do you have to count down from the middle of the month and count up from that same date to the end. And, why does the secretary have to pick the date?And, why can't you just count up from the beginning of the month?
I guess I just don't understand the need for all the hoops you're running through to make a simple schedule.
Jeff i think you don't wont to help
only make it difficult
you think i am don't know "how to make simple schedule" or bother you in purpose
you are insult me in purpose !
i ask you a question
how can you do this !
take one employee and start for him for next month shift morning (morning=1)
in 18/02/2008 (Monday)
and from this point filling for him work roster forward and backward
(the secretary have an old DOS software that do this the same !!)
i am not argue with the secretary why why why (she want it like this she do it 20 years like this point)
i am want to help and improve it and move to sql server and web application
she constant take group of employee and set for them work roster like this
empid next_start_date stat_shift
------------------------------------------
11111 04/02/2008 1=morning
22222 11/02/2008 2= evening
......
and from this point generate work roster forward and backward full next month
this why i have tow stord prosege one forward and the second backward
now you want to help me ???
January 27, 2008 at 8:59 am
Jeff i think you don't wont to help
only make it difficult
you think i am don't know "how to make simple schedule" or bother you in purpose
you are insult me in purpose !
No, Midan... I do want to help. If you want to see "difficult", go back an look at the string of breadcrumbs you've given us for a problem definition. And, I don't give a hoot about a secretary's reasoning... all I care about is that we calculate the whole month because it's easier and THAT's why I asked! I'm not trying to make it difficult on you... I'm trying to get you to cleanly identify the problem instead of going back and forth a 100 times with language barrier problems to boot! And, trust me, if I wanted to insult you, there would be no question in your or anyone else's mind as to whether you've been insulted. :hehe:
Ok... let's get back to the problem... your latest definition said this...
soory Jeff i explain
i need go generate a work roster for next month
and the secretary want to pick only one date for the next month for each employee
for example
empid date
--------------
1111 17/03/2008
2222 10/03/2008
3333 05/03/2008
----------------------
now i have tow stord prosege
one stord prosege go forward 17/03/2008 >>> to 31/03/2008 (end of the month) and the shift go forward(1,2,3,4,5,6,7,8)
the seconde stord prosege go backward 17/03/2008 >>> to 01/03/2008 (start fo the month)and the shift go backward (8,7,6,5,4,3,2,1)
this is what i need
TNX
Again, you have failed to provide an adequate definition! You have NOT defined which shift number to use for the 17/03 date for EmpID 1111! According to your description above, EmpID 1111 should have two shifts on 17/03... shift 1 and 8! But I'm pretty sure that's NOT what you want...
IF what you really mean is...
one stord prosege go forward 17/03/2008 >>> to 31/03/2008 (end of the month) and the shift go forward(1,2,3,4,5,6,7,8)
the seconde stord prosege go backward 16/03/2008 >>> to 01/03/2008 (start fo the month)and the shift go backward (8,7,6,5,4,3,2,1)
...and the output for the 1111 EmpID is to look like this... (Notice, it counts forward, starting at 1 starting on 17/03/2008 and counts backward starting at 8 starting on 16/03/2008)... A simple listing like this at the beginning of your post would have saved a whole lot of time...
EmpIDDateShift
1111131/03/20087
1111130/03/20086
1111129/03/20085
1111128/03/20084
1111127/03/20083
1111126/03/20082
1111125/03/20081
1111124/03/20088
1111123/03/20087
1111122/03/20086
1111121/03/20085
1111120/03/20084
1111119/03/20083
1111118/03/20082 --<< SHIFTS COUNT FORWARDS FROM HERE
1111117/03/20081 --<<BASE DATE MUST HAVE SHIFT = 1 FOR THIS EMPLOYEE
1111116/03/20088 --<<SHIFTS COUNT BACKWARDS FROM HERE
1111115/03/20087
1111114/03/20086
1111113/03/20085
1111112/03/20084
1111111/03/20083
1111110/03/20082
1111109/03/20081
1111108/03/20088
1111107/03/20087
1111106/03/20086
1111105/03/20085
1111104/03/20084
1111103/03/20083
1111102/03/20082
1111101/03/20081
Now, is THAT what you want, or not? If not, then please be very specific...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2008 at 12:35 pm
TNX for the help
11111 19/03/2008 3
11111 18/03/2008 2 --<< SHIFTS COUNT FORWARDS FROM HERE
11111 17/03/2008 1 --<<BASE DATE MUST HAVE SHIFT = 1 FOR THIS EMPLOYEE
11111 16/03/2008 8 --<<SHIFTS COUNT BACKWARDS FROM HERE
11111 15/03/2008 7
1)YES this is what i need to do (for MULTI employee INSERT)
2 BUT i have a business rule for the forward
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5)) OR
-- if it is Saturday nd we are on one of the first shift we don't move to next shift type .
(DATENAME(dw ,@current) = 'Saturday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in (8))
-- do nothing
--set @last_shift_id=@last_shift_id
3 i have the inverse business rule for the backward
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Sunday ' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5,8))
OR
-- if it is Saturday nd we are on one of the first shift we don't move to next shift type .
(DATENAME(dw ,@current) = 'Saturday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in (1,3,5))
-- do nothing
--set @last_shift_id=@last_shift_id
you think i can do it in one stord prosege ?and olso use my business rule for the backward + business rule for the forward :w00t::w00t:
I wish you do it
the all stord prosege "forward"
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
DECLARE
@Start_Date datetime
DECLARE
@End_Date datetime
-- first day of next month
SET
@Start_Date = DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )
-- last day of next month
SET
@End_Date = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1)-1
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,@End_Date FROM empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
SET @current = @StartDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5)) OR
-- if it is Saturday nd we are on one of the first shift we don't move to next shift type .
(DATENAME(dw ,@current) = 'Saturday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in (8))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
--INSERT INTO [nili].[dbo].[Table_1]
--([empID]
--,[ShiftDate]
--,[d]
--,[shiftType])
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
the all stord prosege "backward"
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 8 UNION ALL
SELECT 7 UNION ALL
SELECT 6 UNION ALL
SELECT 5 UNION ALL
SELECT 4 UNION ALL
SELECT 3 UNION ALL
SELECT 2 UNION ALL
SELECT 1
set
@i=@i+1
end
DECLARE
@Start_Date datetime
DECLARE
@End_Date datetime
-- first day of next month
SET
@Start_Date = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) )-1
-- last day of next month
SET
@End_Date = DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )-1
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
DEclare
@st_shift_id int
set @st_shift_id=8
Declare
@input_empID int
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,@st_shift_id,@End_Date,emp.StartDate FROM v_empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID ,@last_shift_id ,@StartDate,@EndDate
SET @current = @EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
While (@current > @StartDate)
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Sunday ' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,3,5,8))
OR
-- if it is Saturday nd we are on one of the first shift we don't move to next shift type .
(DATENAME(dw ,@current) = 'Saturday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in (1,3,5))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,-1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @EndDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
--INSERT INTO [nili].[dbo].[Table_1]
-- ([empID]
-- ,[ShiftDate]
-- ,[d]
--,[shiftType])
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
TNX
January 28, 2008 at 7:02 am
Would you carefully fill in the question marks to meet the Friday/Saturday rules on the following, please... again, not trying to be difficult... trying to get it right...
EmpID Date DAY Shift
11111 22/03/2008 Tu ?
11111 21/03/2008 Mo ?
11111 20/03/2008 Su ?
11111 19/03/2008 Sa ?
11111 18/03/2008 Fr ? --<< SHIFTS COUNT FORWARDS FROM HERE
11111 17/03/2008 Th 1 --<<BASE DATE MUST HAVE SHIFT = 1 FOR THIS EMPLOYEE
11111 16/03/2008 We 8 --<<SHIFTS COUNT BACKWARDS FROM HERE
11111 15/03/2008 Tu ?
11111 14/03/2008 Mo ?
11111 13/03/2008 Su ?
11111 12/03/2008 Sa ?
11111 11/03/2008 Fr ?
11111 10/03/2008 Th ?
11111 09/03/2008 We ?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2008 at 9:38 am
hi and tnx for the help
i am apprais your help
if you see in my tow stord prosege
i have the conditions there !
and it work 100%(but slow !!)
explain for the list
1 morning
2 morning
3 evening
4 evening
5 night
6 night
7 rest after night
8 home
usually the employee work 1,2,3,4,5,6,7,8 1,2,3,4,5,6,7,8
1=morning,2=morning,3=evening,4=evening,5=night,6=night,7=rest_after_night,8=home
THE conditions - for the week-end and
rule
the employee must start the week-end and finish it with the same shift
but only if it start a series 1 OR 3 OR 5 ( morning=1 2=evening 3=night) ON Friday
i change the date so you can see THE conditions - for the week-end and !
EmpID Date DAY Shift
11111 22/03/2008 Tu 4
11111 21/03/2008 Mo 3
11111 20/03/2008 Su 2
11111 19/03/2008 Sa 1 --<< SHIFTS COUNT FORWARDS FROM HERE
11111 18/03/2008 Fr 1 --<<BASE DATE MUST HAVE SHIFT = 1 FOR THIS EMPLOYEE
11111 17/03/2008 Th 8 --<<SHIFTS COUNT BACKWARDS FROM HERE
11111 16/03/2008 We 7
11111 15/03/2008 Tu 6
11111 14/03/2008 Mo 5
11111 13/03/2008 Su 4
11111 12/03/2008 Sa 4
11111 11/03/2008 Fr 3
11111 10/03/2008 Th 2
11111 09/03/2008 We 1
the data the user see
EmpID Date DAY Shift
11111 22/03/2008 Tu evening
11111 21/03/2008 Mo evening
11111 20/03/2008 Su morning
11111 19/03/2008 Sa morning --------<< SHIFTS COUNT FORWARDS FROM HERE
11111 18/03/2008 Fr morning-------- --<<BASE DATE MUST HAVE SHIFT = 1 FOR THIS EMPLOYEE
11111 17/03/2008 Th home --<<SHIFTS COUNT BACKWARDS FROM HERE
11111 16/03/2008 We rest after night
11111 15/03/2008 Tu night
11111 14/03/2008 Mo night
11111 13/03/2008 Su evening
11111 12/03/2008 Sa evening
11111 11/03/2008 Fr evening
11111 10/03/2008 Th morning
11111 09/03/2008 We morning
January 31, 2008 at 8:45 am
hi
any new
tnx
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply