January 18, 2008 at 7:09 am
Cool - that's great Midan. I just didn't want to get YOU in trouble by giving you more than you can "handle". I've been handed those scenarios (having to support something I don't fully understand), and it's not pretty.
Anyway - I'm happy I helped you. Good luck with the rest!!!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2008 at 10:23 am
hi an thnks for all
but i have strange behavior on the backward code the shift don'T GO backward (the shift)
please RUN THIS CODE
the order for backward IS "8" AFTER 8 IS 7 > 6 > 5 > 4 > 3..2..1
now i get AFTER 8 > 1 .2.3.4.5.6.7.8
TNX for the help
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int 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, '10/02/2008', 103),CONVERT(DATETIME, '24/02/2008', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,-1*spt.number,Enddate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)
--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,3,5,8) and DATENAME (dw,ShiftDate )='Saturday' then 0
WHEN @shift_id in ( 1,3,5) and DATENAME (dw,ShiftDate )='Sunday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts
January 20, 2008 at 2:42 am
hi
i think the problem is in this line
for the shift order
............................else 1 end)%8+1
i am only think i know
and you know what to do 😀
TNX
January 21, 2008 at 2:08 pm
can you help please
on the shift order
tnx
January 22, 2008 at 12:43 am
can I have a go ?
Since I try to avoid hints ...
how about ....
-- just a copy from your initial #empshifts
insert #empshifts2
select * from #empshifts ;
--now set up the shifts as the cursor solution did
update e2
set ShiftType = case when isnull(e1.ShiftType,e1.StartingShiftType) - ((datediff(d, e1.ShiftDate, eMax.max_ShiftDate) %8) + 1) = 0 then eMax.max_StartingShiftType
else isnull(e1.ShiftType,e1.StartingShiftType) - ((datediff(d, e1.ShiftDate, eMax.max_ShiftDate) %8) + 1)
end
from #empshifts2 e1
inner join #empshifts2 e2
on e1.empid = e2.empid
and e1.ShiftDate = dateadd(d,+1, e2.ShiftDate)
inner join (select empid
, max(ShiftDate) as max_ShiftDate
, max(StartingShiftType) as max_StartingShiftType
from #empshifts2
group by empid ) eMax
on e1.empid = eMax.empid
update e1
set ShiftType = eMax.max_StartingShiftType
from #empshifts2 e1
inner join (select empid
, max(ShiftDate) as max_ShiftDate
, max(StartingShiftType) as max_StartingShiftType
from #empshifts2
group by empid ) eMax
on e1.empid = eMax.empid
where e1.ShiftType is null
select *
from #empshifts2
order by empid,
shiftdate
I know it needs the extra run, you might consider just to
insert the initial StartingShiftType also into the ShiftType column if
you want to avoid this.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 1:52 pm
ALZDBA (1/22/2008)
can I have a go ?Since I try to avoid hints ...
how about ....
.....
I know it needs the extra run, you might consider just to
insert the initial StartingShiftType also into the ShiftType column if
you want to avoid this.
please can you help me finish
and use this code
i try to fix with your extra code !
and i can't make it work ok
can someone help me
make the list of the shift backward !
TNX
January 23, 2008 at 2:05 pm
Midan -
You shouldn't need to change the assignment process if you simply change the clustered index order. Really. It should assign the numbers correctly one way or the other.
If you reverse both the order of the dates and the order of the shifts, you end up with the same data. Fun as all heck, but not really effective.
Really - try drawing it out on paper, and you'll see what I mean.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 2:23 pm
hi and welcome! my best teacher
everything work wonderful
and the day go backward
BUT the list not
in my old code i fix it and i do like this example and it work
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
how can i do it on this line ?
WHEN @shift_id in ( 1,3,5) and DATENAME (dw,ShiftDate )='Sunday' then 0 else 1 end)%8+1,
can you show me please
TNX for the help
January 23, 2008 at 2:32 pm
see
i do this and still
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
the list don't go backward
January 23, 2008 at 2:42 pm
please see this
after i change the "on #empshifts(empid,shiftdate DESC)"
i get this
1111112008-02-24Sunday 1
1111112008-02-23 Saturday2
1111112008-02-22 Friday 3
1111112008-02-21 Thursday4
1111112008-02-20 Wednesday5
1111112008-02-19 Tuesday 6
1111112008-02-18 Monday 7
1111112008-02-17 Sunday 8
1111112008-02-16 Saturday1
1111112008-02-15 Friday 2
1111112008-02-14 Thursday3
1111112008-02-13 Wednesday4
1111112008-02-12 Tuesday 5
1111112008-02-11 Monday 6
1111112008-02-10 Sunday 7
i need go get it like this
1111112008-02-24Sunday 8
1111112008-02-23 Saturday 7
1111112008-02-22 Friday 6
1111112008-02-21 Thursday 5
1111112008-02-20 Wednesday4
1111112008-02-19 Tuesday 3
1111112008-02-18 Monday 2
1111112008-02-17 Sunday 1
1111112008-02-16 Saturday 8
1111112008-02-15 Friday 7
1111112008-02-14 Thursday 6
1111112008-02-13 Wednesday5
1111112008-02-12 Tuesday 4
1111112008-02-11 Monday 3
1111112008-02-10 Sunday 2
TNX
January 23, 2008 at 2:47 pm
What I'm trying to get at is:
A process where the date increments along with the shift number would yield the same result as a process where the date decrements along with the shift number.
Also - I need to make sure that you understand what this is doing. So - a pop quiz (peeking is fine, but no helping):
1. what does the % do in all of this? For example, what would 7 % 8 return? how about 15 % 8? how about 2 % 2? Why do we do it?
2. With an abstract function - how would you make a number decrement in value from 7 to 0 and back to 7? What would the value for n+1 in relationship to the value for N.
In other words, fill in the blank:
f(n+1) = f(n) ___________________
3. What is the WITH(index()) syntax doing for you? Why is that so important?
4. what is the @shift_ID doing for you?
Again - not trying to be harsh. You NEED to understand this. I may not be here some day when your server or this process goes to hell, and you will be on the hook for knowing why.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 2:08 am
hi i work on it
but thre is BUG
if you run this code i you see it not work ok
after shift 1 must be 8 (8,7,6,5,4,3,2,1)
SELECT 111111,1,CONVERT(DATETIME, '02/03/2008', 103), CONVERT(DATETIME, '31/03/2008', 103)
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
can you help
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int NOT NULL,
[ShiftType] int NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL
)
INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '15/03/2008', 103), CONVERT(DATETIME, '31/03/2008', 103) UNION ALL
SELECT 222222,1,CONVERT(DATETIME, '12/03/2008', 103),CONVERT(DATETIME, '27/03/2008', 103) UNION ALL
SELECT 333333,1,CONVERT(DATETIME, '11/03/2008', 103), CONVERT(DATETIME, '26/03/2008', 103)--UNION ALL
--
--SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
--
--SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate asc)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select empid, dateadd(day,-1*spt.number,Enddate), shifttype
from #empList
cross join master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)
--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set @shift_ID=shiftType=(case when @curr_employee=empid
then @shift_ID
else startingShiftType end -1 +
CASE WHEN @shift_id in (1,3,5,8) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id in (1,3,5) and DATENAME (dw,ShiftDate )='Saturday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype, startingshifttype from #empshifts
--select datediff(ms,@g,getdate())
order by empid, shiftdate desc
January 24, 2008 at 2:56 am
Yes, we can help. But we'd rather you attempted it yourself. We can't do all your debugging for you. You need to understand what the code does, and how it does it. That's why Matt put those questions to you. Can you answer them?
John
January 24, 2008 at 3:12 am
i am appraiser all the help for all the wonderful, people
but i think this is sum bug
i don't know what is the problem
the list work backward the date work backward OK
but thre is bug if i go backward
test it yourself you see
if you can help and see what is the problem
TNX
January 24, 2008 at 5:02 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply