By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,505 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,505 IT Pros & Developers. It's quick & easy.

help me -CURSOR backward insert from End Date > to Start Date

P: 3
help me -CURSOR backward insert from End Date > to Start Date

how to insert dates from end to start

like this

SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008'

i explain i have stord prosege that create mod cycle shift pattern
and it working ok

now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1

so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008


tnx

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  
  3. @shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL)
  4.  
  5. declare
  6.  
  7. @I int
  8.  
  9. set
  10.  
  11. @i=0
  12.  
  13. while
  14.  
  15. @i < 5
  16.  
  17. BEGIN
  18.  
  19. INSERT INTO @shifts_pattern ([patternShiftValue] )
  20.  
  21. SELECT 1 UNION ALL
  22.  
  23. SELECT 2 UNION ALL
  24.  
  25. SELECT 3 UNION ALL 
  26.  
  27. SELECT 4 UNION ALL
  28.  
  29. SELECT 5 UNION ALL
  30.  
  31. SELECT 6 UNION ALL
  32.  
  33. SELECT 7 UNION ALL
  34.  
  35. SELECT 8
  36.  
  37. set
  38.  
  39. @i=@i+1
  40.  
  41. end
  42.  
  43. declare
  44.  
  45. @empList
  46.  
  47. TABLE
  48.  
  49. ( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) 
  50.  
  51. INSERT INTO
  52.  
  53. @empList ([empID], [ShiftType],[StartDate],[EndDate])
  54.  
  55. SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103)
  56.  
  57. -- create shifts table
  58.  
  59. declare
  60.  
  61. @empShifts
  62.  
  63. TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) 
  64.  
  65. DECLARE
  66.  
  67. @StartDate datetime
  68.  
  69. DECLARE
  70.  
  71. @EndDate datetime
  72.  
  73. Declare
  74.  
  75. @current datetime
  76.  
  77. DEclare
  78.  
  79. @last_shift_id int
  80.  
  81. Declare
  82.  
  83. @input_empID int
  84.  
  85. ----------------- open list table for emp with curser
  86.  
  87. DECLARE
  88.  
  89. List_of_emp CURSOR FOR 
  90.  
  91. SELECT
  92.  
  93. emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
  94.  
  95. OPEN
  96.  
  97. List_of_emp
  98.  
  99. FETCH
  100.  
  101. List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
  102.  
  103. SET @current = @StartDate
  104.  
  105. -----------------
  106.  
  107. -- loop on all emp in the list
  108.  
  109. while
  110.  
  111. @@Fetch_Status = 0
  112.  
  113. begin
  114.  
  115. -- loop to insert info of emp shifts
  116.  
  117. while
  118.  
  119. @current<=@EndDate 
  120.  
  121. begin
  122.  
  123. INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
  124.  
  125. select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
  126.  
  127. from @shifts_pattern as shift where PatternId=@last_shift_id+1 
  128.  
  129. -- if it is Friday and we are on one of the first shift we don't move to next shift type .
  130.  
  131. if (DATENAME(dw ,@current) = 'Friday' ) and
  132.  
  133. EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) 
  134.  
  135. -- do nothing
  136.  
  137. --set @last_shift_id=@last_shift_id
  138.  
  139. print ('friday first shift')
  140.  
  141. ELSE
  142.  
  143. set @last_shift_id=@last_shift_id+ 1
  144.  
  145. set @current=DATEADD( d,1, @current)
  146.  
  147. end
  148.  
  149. FETCH
  150.  
  151. List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
  152.  
  153. -- init of start date for the next emp
  154.  
  155. set
  156.  
  157. @current = @StartDate
  158.  
  159. end
  160.  
  161. CLOSE
  162.  
  163. List_of_emp
  164.  
  165. DEALLOCATE 
  166.  
  167. List_of_emp
  168.  
  169. select
  170.  
  171. empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
  172.  
  173. RETURN
Jan 15 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.