January 17, 2008 at 2:10 am
Matt Miller (1/16/2008)
I'm not sure what you mean by okay. You're the one who determines what "okay" is.If it returns what you want it to return, then that's something.
That being said - it's going to have limited growth potential, because of the looping and the cursors. It's going to become very inefficient as it grows bigger. Even on the small set you showed, mine returns the same records, 4 times faster. If you do this against a 1000 employees even if just for a month, the cursor solution will run for at least 5-10 minutes, peg your server at 100% for a while - but it will return in the end. Mine would probably take oh - say about 5.57 seconds and won't even break a sweat.
That being said - you know what data you need - if your solutions works for you, then that's great.
Matt Miller first i want to thank you
and you right it is faster and work excellent
and i must to use yours code !!!
but i need the date backward from end to start 17/3 .. 16/3.. 15/3 ......01/03
please how to do it
like in my example the dates is from end date to start day
and olso this rule not work in my code it work!
-- 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')
again i am appraiser your help
and tnx for all the help i get here
ilan
January 17, 2008 at 7:02 am
You just pull a fast one there - the Saturday rule wasn't there...
Reverse the date assignments as well as well the clustered index, and add the saurday rule
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])
SELECT111111,
1,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT222222,
2,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT333333,
3,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT444444,
4,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT555555,
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 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,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id 8 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 from #empshifts
select datediff(ms,@g,getdate())
----------------------------------------------------------------------------------
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 17, 2008 at 9:37 am
hi and TNX
but i get an error
-----------------
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near '8'.
Msg 319, Level 15, State 1, Line 78
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
January 17, 2008 at 10:15 am
oops - that's what I get for editing on the fly:
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])
SELECT111111,
1,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT222222,
2,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT333333,
3,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT444444,
4,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT555555,
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 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,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id= 8 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 from #empshifts
select datediff(ms,@g,getdate())
----------------------------------------------------------------------------------
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 17, 2008 at 10:41 am
men this fast like rocket !!!
you are cannon !!
TNX
1) question - what to do if now i need to use it forward and also the shift list go forward
show me how to handl it please
2)i need olso to replace the "#empList " with real table is it ok to do it I MUST
3) i am enjoy to see it work like this FAST
TNX 🙂
January 17, 2008 at 10:48 am
The only thing making the sequence go in the same direction or opposite from the date is this line:
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
By making the index DESCending, then the shifttype goes up as the date goes down
if you want it the other way - then remove DESC. In other words:
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate)
If you need the #emplist to be permanent, then replace all references to it.
Except for the DROP TABLE one - remove that altogether: you don't want to drop your permanent table.
Enjoy!
----------------------------------------------------------------------------------
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 17, 2008 at 10:55 am
please one more question
now i use tow stored procedurelike
1 ) from 17/03/2008 to 31/03/2008(end month)
2) from 16/03/2008 to 01/03/2008(start month)
because i must to start from middle of the month
evry employee different date (in the same month)
evry time for the next month
any recommendation ?
January 17, 2008 at 11:21 am
Current process assumes that #emplist table provides the start and end dates specific to each person. Your permanent table would need to continue to provide that, or you can continue to use the #emplist temp table,and just fill #emplist from the permanent table.
Might be easiest to continue to use that as is - just populate #emplist with your employee ID's, the shifttype and whatever dates (which can then be parameters for your stored Procedure you wish to generate. That way- nothing else in there changes.
The beginning of the procedure would then look like:
Create Procedure MyProcedure @startdate datetime, @enddate datetime
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
declare @g datetimeselect @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])
SELECTEmpID, shifttype,@startdate,@enddate
from MyPermanentEmpTable --put the name of your permanent table here.
-- create shifts table
--use the rest of the script as is
----------------------------------------------------------------------------------
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 17, 2008 at 1:53 pm
Matt Miller hi
i want to do something smart
for evry employee want to use only one date!!!
because of i do it evry month for the next month
in the forward_stored procedure (for example the employee 11111 in the date 17/02/2008)
i do this
17/02/2008 to the END OF THE NEXT MONTH
IN THE reverse_stored procedure
16/02/2008 to the START OF THE NEXT MONTH
and like this i can delete the start_day "field "
TNX
January 17, 2008 at 7:03 pm
ALZDBA (1/17/2008)
btw the common table expression I like most is a round of beers :w00t:(expressing friendship). :smooooth:
Definitely a kindred spirit... round of beers... motorcycle for an avatar...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2008 at 7:34 pm
Midan -
No disrespect intended - but I think you ought to take a crack at it. All of the pieces are right there. The reason being - you need to UNDERSTAND the code. I'm actually concerned I've done "too much" because I'm not convinced you understand it enough to fix it when it stops doing what you want from it.
It's a good idea you have - I just think you actually need to wade in and try some things with what you have right now so that you can own that code and understand why and how it operates. It's important.
----------------------------------------------------------------------------------
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 17, 2008 at 8:08 pm
I wondered when you were going to get to that point...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2008 at 8:24 pm
Jeff Moden (1/17/2008)
I wondered when you were going to get to that point...
Ahem - yup. It's that fine line where you want to help someone learn how to swim, but at the same time - not throw them out so far they can't swim back....
----------------------------------------------------------------------------------
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 18, 2008 at 2:55 am
i UNDERSTAND the code
and vry appraise what you did for me and for the help
i only shar me idea with you !
and only ask your opinion
i can do it men
i love you men
and respect you
TNX
January 18, 2008 at 6:59 am
midan1 (1/18/2008)
i UNDERSTAND the code
That's the key... can't really speak for Matt but it was starting to look like you were having him debug every little thing without actually trying yourself. Learning only occurs if you "DO".
Thank you for the feedback Midan.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply