All Forums Database
KVB 124 posts Joined 09/12
28 Nov 2013
Split the dates bi-weekly

I want to run my job bi-weekly.No date should not miss.I can take the nearest Monday.
I am going to run alternate Mondays(bi-weekly).The process dates should be split like below.

Eg:Suppose I take for the Month of january

It should split the dates
Startdate Enddate
2012-12-31 2013-01-13
2013-01-14 2013-01-27
2013-01-28 2013-02-10

Thanks

KVB 124 posts Joined 09/12
28 Nov 2013

I have written like this.I feel this is complex.

SEL B.STARTDATE,COALESCE(MAX(STARTDATE) OVER( PARTITION BY 3 ORDER BY STARTDATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)-1,CAST('9999-12-31' AS DATE)) AS DW_EXPR_DT
FROM

(SEL A.DD as STARTDATE, RANK() OVER(ORDER BY DD) RN
FROM
(
SEL DISTINCT
CASE WHEN DAYOFWEEK(CALENDAR_DATE) =2 THEN CALENDAR_DATE
ELSE
CASE WHEN CALENDAR_DATE

KVB 124 posts Joined 09/12
28 Nov 2013

SEL B.STARTDATE,COALESCE(MAX(STARTDATE) OVER( PARTITION BY 3 ORDER BY STARTDATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)-1,STARTDATE+13) AS DW_EXPR_DT
FROM

(SEL A.DD as STARTDATE, RANK() OVER(ORDER BY DD) RN
FROM
(
SEL DISTINCT
CASE WHEN DAYOFWEEK(CALENDAR_DATE) =2 THEN CALENDAR_DATE
ELSE
CASE WHEN CALENDAR_DATE

dnoeth 4628 posts Joined 11/04
01 Dec 2013

I don't get what your exactly trying to achieve.
"run my job bi-weekly": scheduling a job on Unix or retriving rows in a SELECT?
"nearest monday": based on what, first of month?
"no missing date": return a row even if there's no data for it?
A simple query on sys_calendar.calendar based on day_of_calendar = 2 and week_of_calendar mod 2 = 0|1 should work. 
Similar in TD13.10+ an EXPAND ON MONDAY.

Dieter

KVB 124 posts Joined 09/12
03 Dec 2013

Hi Dieter
 
 I am maintaining a process table with start date and end date.I need to run a job on Monday's only that too bi weekly.So that if i give the dates as sample Jan-1-2013 to Dec-31-2013.Then the tables should be populated with the start and end dates i.e. splitting in bi-weekly dates.

KVB 124 posts Joined 09/12
03 Dec 2013

For eg:If I want to run my job on Jan14th 2013 then i should process the data from 31-Dec-2012 to jan-13-2013.

Kawish_Siddiqui 37 posts Joined 03/07
03 Dec 2013

Following might solve your issue or help you to solve this query.

SELECT Start_Date, End_Date
FROM
(
	SELECT calendar_date End_Date, ROW_NUMBER() OVER(ORDER BY calendar_date) RowNum
	FROM sys_calendar.CALENDAR
	WHERE calendar_date BETWEEN '2013-01-01' AND '2013-12-31'
	  AND day_of_week = 1  --Sunday
	 QUALIFY RowNum MOD 2 = 1
) ed
INNER JOIN 
(
	SELECT calendar_date Start_date, ROW_NUMBER() OVER(ORDER BY calendar_date) RowNum
	FROM sys_calendar.CALENDAR
	WHERE calendar_date BETWEEN '2013-01-01' AND '2013-12-31'
	  AND day_of_week = 2  -- Monday
	 QUALIFY RowNum MOD 2 = 1
) st
ON st.RowNum+2 = ed.rownum

 

Kawish Siddiqui -

You must sign in to leave a comment.