help-how to generate date backward from end to start

  • 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

  • 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

  • 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

  • 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

  • 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?

  • Thanks, Matt... you just saved me from having to breakout the Tally table "porkchops"! 😛

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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

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

    SELECT111111,

    1,

    CONVERT(DATETIME, '01/01/2008', 103),

    CONVERT(DATETIME, '27/02/2009', 103)

    UNION ALL

    SELECT222222,

    2,

    CONVERT(DATETIME, '01/01/2008', 103),

    CONVERT(DATETIME, '27/02/2009', 103)

    UNION ALL

    SELECT333333,

    3,

    CONVERT(DATETIME, '01/01/2008', 103),

    CONVERT(DATETIME, '27/02/2009', 103)

    UNION ALL

    SELECT444444,

    4,

    CONVERT(DATETIME, '01/01/2008', 103),

    CONVERT(DATETIME, '27/02/2009', 103)

    UNION ALL

    SELECT555555,

    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?

  • 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 😀

  • can you see this i do it is ok like this ???

    DECLARE @tbDate table (id int identity, dt datetime)

    --While (@effectivedate > '01/03/2007')

    --begin

    --Insert into @tbDate (dt)

    --values ( @effectivedate )

    --set @effectivedate = dateadd(d,-1,@effectivedate)

    --end

    --Select dt from @tbDate

    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/03/2008', 103), CONVERT(DATETIME, '17/03/2008', 103)union all

    SELECT 222222,1,CONVERT(DATETIME, '01/02/2008', 103), CONVERT(DATETIME, '10/03/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

    declare @effectiveDate datetime

    set @effectivedate = @EndDate

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

    -----------------

    --DECLARE @tbDate table (id int identity, dt datetime)

    --

    --declare @effectiveDate datetime

    --set @effectivedate = '17/03/2007'

    --

    --While (@effectivedate > '01/03/2007')

    --begin

    --Insert into @tbDate (dt)

    --values ( @effectivedate )

    --set @effectivedate = dateadd(d,-1,@effectivedate)

    --end

    -- loop on all emp in the list

    while

    @@Fetch_Status = 0

    begin

    --DECLARE @tbDate table (id int identity, dt datetime)

    --

    --declare @effectiveDate datetime

    set @effectivedate = @EndDate

    -- loop to insert info of emp shifts

    --while

    While (@effectivedate > @StartDate)

    begin

    INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])

    select @input_empID ,@effectivedate,shift .patternShiftValue ,@StartDate,@EndDate

    from @shifts_pattern as shift where PatternId=@last_shift_id+1

    set @effectivedate = dateadd(d,-1,@effectivedate)

    set @current=DATEADD( d,1, @current)

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

  • I'm not sure what you mean by okay. You're the one who determines what "okay" is.

    If it returns what you want it to return, then that's something.

    That being said - it's going to have limited growth potential, because of the looping and the cursors. It's going to become very inefficient as it grows bigger. Even on the small set you showed, mine returns the same records, 4 times faster. If you do this against a 1000 employees even if just for a month, the cursor solution will run for at least 5-10 minutes, peg your server at 100% for a while - but it will return in the end. Mine would probably take oh - say about 5.57 seconds and won't even break a sweat.

    That being said - you know what data you need - if your solutions works for you, then that's great.

    ----------------------------------------------------------------------------------
    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?

  • skip the d*** loops. Use a numbers table to do this:

    After looking at your solution, its is so very clear. Before I was only think loop :hehe:. This is precisely why I hang around these forums. I do not claim to be an expert by any means, I just like to help out and learn where I can. Every person has a distinct method of thinking and the longer you been the game the more evident the process becomes.

    Anyway an alteration to code I created using a tally table (so much simplier and scalable :))

    declare @start datetime, @end datetime

    set @start = '1/1/2007 00:00:00.000'

    set @end = getdate()

    declare @tbl table(

    Dt SMALLDATETIME

    )

    insert into @tbl

    select dateadd(dd,-1 * id, @end)

    from tally

    where dateadd(dd,-1 * id, @end) between @start and @end

    select *

    from @tbl

  • midan1 (1/16/2008)


    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

    Heh... ok... just to get back to the original question before everyone broke out in a bad case of "loopus" :P...

    The following code will produce dates in a reverse order from the EndDate to the StartDate... no cursor... no explicit loop... runs nasty fast... on repeated use, generates 10 years of days in about 31 milliseconds.

    --===== Declare some local variables that could be parameters in a proc

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    --===== Set those "parameters" for demonstration purposes

    SET @StartDate = '20000101' --Inclusive

    SET @EndDate = '20100101' --Non-inclusive

    ; WITH Tally AS

    (-----------------------------------------------------------------------------

    --==== CTE equivalent of a Tally table

    SELECT TOP 36525 -- Force limit to about 100 years

    ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

    FROM Master.sys.Columns t1

    CROSS JOIN Master.sys.Columns t2

    )-----------------------------------------------------------------------------

    --===== Create the Serial and Date info required in the original post

    -- using the Tally CTE as a common source of numbers.

    SELECT Serial = N,

    Date = @EndDate - N

    FROM Tally

    WHERE @EndDate - N >= @StartDate

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ...

    The following code will produce dates in a reverse order from the EndDate to the StartDate... no cursor... no explicit loop... runs nasty fast... on repeated use, generates 10 years of days in about 31 milliseconds.

    ...

    /

    Now that's a very nice CTE solution

    btw the common table expression I like most is a round of beers :w00t:(expressing friendship). :smooooth:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 15 posts - 1 through 15 (of 53 total)

You must be logged in to reply to this topic. Login to reply