how to generate date backward from end to start

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to generate date backward from end to start

Author

Topic

protest
Starting Member

7 Posts

Posted - 2008-01-16 : 08:10:51

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 08:17:50

[code]
declare @tb_temp table(serail int, dates datetime)
declare @end_date datetime
set @end_date='2008-03-19'

INSERT INTO @tb_temp
select sno,dateadd(day,-1*sno,@end_Date) from
(
select row_number() over (order by s1.name) as sno from
sysobjects s1 cross join sysobjects s2
) as t
where dateadd(day,-1*sno,@end_Date)>=dateadd(month,datediff(month,0,@end_Date),0)

select * from @tb_temp

[/code]

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-16 : 08:18:09

put a loop like

while
@end_date>=@start_Date
getdate()-@I
@I++

..so on !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 08:22:16

quote:
Originally posted by sakets_2000

put a loop like

while
@end_date>=@start_Date
getdate()-@I
@I++

..so on !!

Simulating front-end code?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 08:23:44

You may also want to refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Madhivanan

Failing to plan is Planning to fail

protest
Starting Member

7 Posts

Posted - 2008-01-16 : 08:35:25

ok ok tnx
need help

how can i put this in this part code
-----------------------------------------------



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 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])

protest
Starting Member

7 Posts

Posted - 2008-01-16 : 08:48:40

pardon this is the all my "stored procedure"
i need to generate date backward from end to start
Merci



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 
print ('-')
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources