Split the dates bi-weekly

Database
KVB
Enthusiast

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
6 REPLIES
KVB
Enthusiast

Re: Split the dates bi-weekly

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_DATEELSE
CAST((CALENDAR_DATE-DAYOFWEEK(CALENDAR_DATE))+2 AS DATE)
END
END DD
FROM DATE_DIM_T --(My date table same as calendar table)
WHERE CALENDAR_DATE BETWEEN CAST('2013-01-01' AS DATE) AND CAST ('9999-12-31' AS DATE)
) A
QUALIFY SUM(1) OVER (ROWS UNBOUNDED PRECEDING) MOD 2 = 1
)B

KVB
Enthusiast

Re: Split the dates bi-weekly

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_DATEELSE
CAST((CALENDAR_DATE-DAYOFWEEK(CALENDAR_DATE))+2 AS DATE)
END
END DD
FROM DATE_DIM_T --(My date table same as calendar table)
WHERE CALENDAR_DATE BETWEEN CAST('2013-01-01' AS DATE) AND CAST ('9999-12-31' AS DATE)
) A
QUALIFY SUM(1) OVER (ROWS UNBOUNDED PRECEDING) MOD 2 = 1
)B
Senior Apprentice

Re: Split the dates bi-weekly

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.

KVB
Enthusiast

Re: Split the dates bi-weekly

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
Enthusiast

Re: Split the dates bi-weekly

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.

Enthusiast

Re: Split the dates bi-weekly

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