Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

help-how to generate date backward from end to start Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2008 2:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
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 for any help



Post #444003
Posted Wednesday, January 16, 2008 2:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
I dont know if this is what you have in mind but You could use dateadd function to do that.


Create table #tbDate (id int identity, dt datetime)

declare @effectiveDate datetime
set @effectivedate = '2007-12-15 00:00:00.000'

While (@effectivedate > '2007-12-01')
begin
Insert into #tbDate (dt)
values ( @effectivedate )
set @effectivedate = dateadd(d,-1,@effectivedate)
end

Select * from #tbDate


-Roy
Post #444022
Posted Wednesday, January 16, 2008 3:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:35 AM
Points: 2,278, Visits: 3,056
You could do something like this

declare @start datetime, @counter int, @end int
set @start = '1/1/2007'
set @end = datediff(dd,@start,getdate())
set @counter = 0

declare @tbl table(
Dt SMALLDATETIME
)

while @counter <= @end
begin
INSERT INTO @tbl (Dt)
Values(CONVERT(VARCHAR,dateadd(dd,-1 * @counter, GETDATE()),101))

set @counter = @counter + 1
end

select *
from @tbl





My blog: http://jahaines.blogspot.com
Post #444031
Posted Wednesday, January 16, 2008 3:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
tnx for the help
how can i use al, the help and put it in this part of code

while

@@Fetch_Status = 0

begin

-- loop to insert info of emp shifts

while

@current<=@EndDate ----------------generate date from END to START ?

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





Post #444039
Posted Wednesday, January 16, 2008 4:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,135, Visits: 15,154
skip the d*** loops. Use a numbers table to do this:

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,
@current,
shift.patternShiftValue ,
@StartDate,
dateadd(day,-1*spt.number,@EndDate)
from @shifts_pattern as shift,
master..spt_values spt
where
PatternId=@last_shift_id+1
and spt.type='P'
and dateadd(day,-1*spt.number,@EndDate) between @current and @enddate




----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #444050
Posted Wednesday, January 16, 2008 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
Thanks, Matt... you just saved me from having to breakout the Tally table "porkchops"! :P

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #444065
Posted Wednesday, January 16, 2008 5:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,135, Visits: 15,154
Not that it's much of a consolation, since the "outer code" is that of a cursor...

I just had to draw the line at second-order loops (loops of loops)..:)

Baby steps.....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #444072
Posted Wednesday, January 16, 2008 5:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
Matt Miller (1/16/2008)
skip the d*** loops. Use a numbers table to do this:

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,
@current,
shift.patternShiftValue ,
@StartDate,
dateadd(day,-1*spt.number,@EndDate)
from @shifts_pattern as shift,
master..spt_values spt
where
PatternId=@last_shift_id+1
and spt.type='P'
and dateadd(day,-1*spt.number,@EndDate) between @current and @enddate




can you help me this is all 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





Post #444075
Posted Wednesday, January 16, 2008 7:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,135, Visits: 15,154
Try this
if object_ID('tempdb..#emplist','U')<>0 
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int 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, '27/02/2009', 103)
UNION ALL
SELECT 222222,
2,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT 333333,
3,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT 444444,
4,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT 555555,
5,
CONVERT(DATETIME, '01/01/2008', 103),
CONVERT(DATETIME, '27/02/2009', 103)

-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int

--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,spt.number,startdate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)

--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0

update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0 else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)

--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts

select datediff(ms,@g,getdate())



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #444087
Posted Wednesday, January 16, 2008 7:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2008 8:42 AM
Points: 302, Visits: 271
Matt Miller
i love you men :D:D:D:D
it working
you are genius !!
tnx
any recommendation for this code ?
i am look for solution over a month !!!
you make my day :D



Post #444096
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse