473,554 Members | 3,071 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

how to insert dates from end to start

like this

SELECT 111111,1,CONVER T(DATETIME, '17/03/2008', 103), CONVERT(DATETIM E, '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
0 1658

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

Similar topics

21
6501
by: Dave | last post by:
After following Microsofts admonition to reformat my system before doing a final compilation of my app I got many warnings/errors upon compiling an rtf file created in word. I used the Help Workshop program: hcw.exe that's included with Visual Basic. This exact same file compiled perfectly with no notes, warnings or errors prior to...
9
4388
by: Tom | last post by:
A question for gui application programmers. . . I 've got some GUI programs, written in Python/wxPython, and I've got a help button and a help menu item. Also, I've got a compiled file made with the microsoft HTML workshop utility, lets call it c:\path\help.chm. My question is how do you launch it from the GUI? What logic do I put behind...
4
3333
by: Sarir Khamsi | last post by:
Is there a way to get help the way you get it from the Python interpreter (eg, 'help(dir)' gives help on the 'dir' command) in the module cmd.Cmd? I know how to add commands and help text to cmd.Cmd but I would also like to get the man-page-like help for classes and functions. Does anyone know how to do that? Thanks. Sarir
2
6413
by: Sudheer Kareem | last post by:
Dear All Please tell me how to assosiate help files with my Vb.net Project. Regards Sudheer
6
4312
by: wukexin | last post by:
Help me, good men. I find mang books that introduce bit "mang header files",they talk too bit,in fact it is my too fool, I don't learn it, I have do a test program, but I have no correct doing result in any way. Who can help me, I thank you very very much. list.cpp(main program)...
3
3332
by: Colin J. Williams | last post by:
Python advertises some basic service: C:\Python24>python Python 2.4.1 (#65, Mar 30 2005, 09:13:57) on win32 Type "help", "copyright", "credits" or "license" for more information. >>> With numarray, help gives unhelpful responses:
5
3253
by: Steve | last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp My expectation is that a developer using my DLL would be able to access this help file during his development time using "F1" help within the VB IDE. Is this expectation achievable In trying to test my help file in the IDE, I have a solution with 2 projects:...
8
3209
by: Mark | last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my home computer has an abbreviated help screen not 2% of the help on my laptop. All the settings look the same on both including search the internet for help, but the help is worthless. Any ideas?
10
3341
by: JonathanOrlev | last post by:
Hello everybody, I wrote this comment in another message of mine, but decided to post it again as a standalone message. I think that Microsoft's Office 2003 help system is horrible, probably the worst I ever seen. I almost cannot find anything I need, including things I
1
6115
by: trunxnirvana007 | last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"' 'UPGRADE_WARNING: Couldn't resolve default property of object Label. Click for more:...
0
7512
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7783
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7879
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6129
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5426
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2009
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1117
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
828
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.