how to generate date backward from end to start
like this
begin
-- loop to insert date backward
while
@end_date>=@start_Date
begin
INSERT INTO @tb_temp
from middle of the month to end of the month
serial date
------------------------------
1 19/03/2008
2 18/03/2008
3 17/03/2007
..............
19 01/03/2007
TNX
2 1124 deepuv04 227
Recognized Expert New Member
how to generate date backward from end to start
like this
begin
-- loop to insert date backward
while
@end_date>=@start_Date
begin
INSERT INTO @tb_temp
from middle of the month to end of the month
serial date
------------------------------
1 19/03/2008
2 18/03/2008
3 17/03/2007
..............
19 01/03/2007
TNX
Try this query..
INSERT INTO Table_Name(col1,col2,.....)
SELECT Col1,col2,col3.... FROM Table_Name2
WHERE Required_Condtions
ORDER BY Date_Column desc
thanks
help me put it in my code - 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 1 UNION ALL
-
-
SELECT 2 UNION ALL
-
-
SELECT 3 UNION ALL
-
-
SELECT 4 UNION ALL
-
-
SELECT 5 UNION ALL
-
-
SELECT 6 UNION ALL
-
-
SELECT 7 UNION ALL
-
-
SELECT 8
-
-
set
-
-
@i=@i+1
-
-
end
-
-
declare
-
-
@empList
-
-
TABLE
-
-
( [empID] [numeric](18, 0) 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, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103)
-
-
-- create shifts table
-
-
declare
-
-
@empShifts
-
-
TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
-
-
DECLARE
-
-
@StartDate datetime
-
-
DECLARE
-
-
@EndDate datetime
-
-
Declare
-
-
@current datetime
-
-
DEclare
-
-
@last_shift_id int
-
-
Declare
-
-
@input_empID int
-
-
----------------- open list table for emp with curser
-
-
DECLARE
-
-
List_of_emp CURSOR FOR
-
-
SELECT
-
-
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
-
-
OPEN
-
-
List_of_emp
-
-
FETCH
-
-
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-
-
SET @current = @StartDate
-
-
-----------------
-
-
-- loop on all emp in the list
-
-
while
-
-
@@Fetch_Status = 0
-
-
begin
-
-
-- loop to insert info of emp shifts
-
-
while
-
-
@current<=@EndDate
-
-
begin
-
-
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
-
-
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
-
-
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-
-
-- 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,2,3))
-
-
-- do nothing
-
-
--set @last_shift_id=@last_shift_id
-
-
print ('friday first shift')
-
-
ELSE
-
-
set @last_shift_id=@last_shift_id+ 1
-
-
set @current=DATEADD( d,1, @current)
-
-
end
-
-
FETCH
-
-
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-
-
-- init of start date for the next emp
-
-
set
-
-
@current = @StartDate
-
-
end
-
-
CLOSE
-
-
List_of_emp
-
-
DEALLOCATE
-
-
List_of_emp
-
-
select
-
-
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
-
-
RETURN
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Gerrit Holl |
last post by:
Posted with permission from the author.
I have some comments on this PEP, see the (coming) followup to this message.
PEP: 321
Title: Date/Time Parsing and Formatting
Version: $Revision: 1.3 $...
|
by: Andrew Grandison |
last post by:
We are converting a legacy visual foxpro system to use a SQL back-end.
A number of (existing DBF) tables currently have a zero-filled primary
key eg. '000255' which is just an auto-incrementing...
|
by: Dr John Stockton |
last post by:
It has appeared that ancient sources give a method for Numeric Date
Validation that involves numerous tests to determine month length;
versions are often posted by incomers here. That sort of code...
|
by: tsmith81 |
last post by:
Ok. I have an ASP page that send data to a database. One of the fields
is "Date" (no quotes). The form name is FrontPage_Form1. I would like
the Date field to auto populate. So, I did some research...
|
by: Robert Davis |
last post by:
I would like to be able to create a recordset in MS Access 2000 that
allows a macro to run and create a report for each ID that appears in the recordset. So I thought that I would use a
scheduling...
| |
by: AA Arens |
last post by:
Hi,
I have a database with 2 main forms. Contacts and companies. I share
the base with two others via LAN. On the companies form I have buttons
to navigate throught the records
(>400). We are...
|
by: robert bristow-johnson |
last post by:
here is a post i put out (using Google Groups) that got dropped by
google:
i am using gcc as so:
$ gcc -v
Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure...
|
by: gpspocket |
last post by:
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,...
|
by: vp.softverm |
last post by:
hi all .
Am facing problem with the date picker. when i click on date picker in
a popup window. the date table is opened in the middle of the pop up
window.
and it is unable to scroll with...
|
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...
|
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,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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 ...
| |
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...
| |