Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hissam78
Starting Member
14 Posts |
 Posted - 2011-08-24 : 03:49:23
|
| SOMEBODY CAN HELP ME TO MAKE THE 3 WEEKS SCHEDULE QUERY FOR PAYROLL SYSTEM. AFTER EVERY 3 WEEKS SHIFT WILL ROTATE ACCORDING TO DATE1 MEANS IN FIRST 3 WEEKS IT WILL BE SHIFTID 3,1,2 THEN IN SECOND 3 WEEKS IT WILL BE SHIFTID 1,2,3 THEN IN THIRD 3 WEEKS IT WILL BE SHIFT 2,3,1 MEANS AFTER EVERY 63 DAYS AGAIN THE SAME SCENERIO OF INSERTING THE SHIFTS NEEDED FOLLOWING DATA AS EXAMPLE WE WANT TO INSERT IS AS FOLLOWS: SHIFTID SHIFTRID SHIFTLNID DATE1 DATE23.00 1.00 1.00 12/04/2010 06:00:00 12/04/2010 14:00:001.00 2.00 2.00 12/04/2010 14:00:00 12/04/2010 22:00:002.00 3.00 3.00 12/04/2010 22:00:00 12/05/2010 06:00:003.00 4.00 4.00 12/05/2010 06:00:00 12/05/2010 14:00:001.00 5.00 5.00 12/05/2010 14:00:00 12/05/2010 22:00:002.00 6.00 6.00 12/05/2010 22:00:00 12/06/2010 06:00:003.00 7.00 7.00 12/06/2010 06:00:00 12/06/2010 14:00:001.00 8.00 8.00 12/06/2010 14:00:00 12/06/2010 22:00:002.00 9.00 9.00 12/06/2010 22:00:00 12/07/2010 06:00:003.00 10.00 10.00 12/07/2010 06:00:00 12/07/2010 14:00:001.00 11.00 11.00 12/07/2010 14:00:00 12/07/2010 22:00:002.00 12.00 12.00 12/07/2010 22:00:00 12/08/2010 06:00:003.00 13.00 13.00 12/08/2010 06:00:00 12/08/2010 14:00:001.00 14.00 14.00 12/08/2010 14:00:00 12/08/2010 22:00:002.00 15.00 15.00 12/08/2010 22:00:00 12/09/2010 06:00:003.00 16.00 16.00 12/09/2010 06:00:00 12/09/2010 14:00:001.00 17.00 17.00 12/09/2010 14:00:00 12/09/2010 22:00:002.00 18.00 18.00 12/09/2010 22:00:00 12/10/2010 06:00:003.00 19.00 19.00 12/10/2010 06:00:00 12/10/2010 14:00:001.00 20.00 20.00 12/10/2010 14:00:00 12/10/2010 22:00:002.00 21.00 21.00 12/10/2010 22:00:00 12/11/2010 06:00:00 |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2011-08-25 : 13:09:43
|
I'm not sure if anyone still reads this old SQL 2000 forum, but here's a thought:Write 3 different queries, and only call the one you need.~Joe
Avoid Sears Home Improvement (read why) |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-25 : 16:46:49
|
| >>help me to make the 3 weeks schedule queryNot sure if you need to populate future weeks or select based on current week. In other words are you looking for a SELECT statement or an INSERT statement?Be One with the OptimizerTG |
|
|
|
hissam78
Starting Member
14 Posts |
Posted - 2011-08-27 : 04:22:15
|
quote:
Originally posted by TG
>>help me to make the 3 weeks schedule queryNot sure if you need to populate future weeks or select based on current week. In other words are you looking for a SELECT statement or an INSERT statement?Be One with the OptimizerTG
i have made my query more simpl. now again i want to tell u the scenarioi have two tables shift_schedule_final, shift_schedule_Tempfrom shift_schedule_Temp i am getting three fields time_in, time_out and shift_id that i wants to update in Shift_schedule_final Table. both the tables have same fields but data is only in shift_schedule_Temp the other one is empty.i have inserted the date using loop in Shift_schedule_final now i want to update the time_in, time_out, shift_id fields in Shift_schedule_final but there is no link between these two tables. so i cannot use where clause. i am using the following subquery update shift_schedule_final set (shift_id,time_in, time_out) = (SELECT shift_id,time_in, time_ouT FROM shift_schedule_Temp WHERE ROWNUM <= 64)it showing the message "Single row subquery return more the one rows.following are the table fieldsSHIFT_ID TIME_IN TIME_OUT2.00 06:00:00 AM 02:00:00 PM3.00 02:00:00 PM 10:00:00 PM1.00 10:00:00 PM 06:00:00 AM3.00 02:00:00 PM 10:00:00 PM1.00 10:00:00 PM 06:00:00 AM2.00 06:00:00 AM 02:00:00 PM3.00 02:00:00 PM 10:00:00 PM3.00 06:00:00 AM 02:00:00 PM1.00 02:00:00 PM 10:00:00 PM2.00 10:00:00 PM 06:00:00 AMthese values i want to update in shift_schedule_final |
|
|
|
|
|
|
|
|
SHIFT ROTATION SCHEDULE