January 16, 2008 at 2:07 pm
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
January 16, 2008 at 2:36 pm
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
January 16, 2008 at 3:05 pm
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
January 16, 2008 at 3:30 pm
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
January 16, 2008 at 4:06 pm
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?
January 16, 2008 at 5:00 pm
Thanks, Matt... you just saved me from having to breakout the Tally table "porkchops"! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2008 at 5:27 pm
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?
January 16, 2008 at 5:37 pm
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
January 16, 2008 at 7:11 pm
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?
January 16, 2008 at 7:47 pm
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 😀
January 16, 2008 at 7:55 pm
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
January 16, 2008 at 9:58 pm
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?
January 16, 2008 at 10:25 pm
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
January 16, 2008 at 10:56 pm
midan1 (1/16/2008)
how to generate date backward from end to startlike 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
Change is inevitable... Change for the better is not.
January 17, 2008 at 12:57 am
...
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