Generate rows and sequence data in Oracle and SQL Server

Oracle

There is a simple and efficient way in Oracle to generate arbitrary number of rows and a sequence of integer numbers. The trick is to (ab)use Oracle's "connect by" clause. From the integer sequence, it is trivial to derive other sequential data, such as datetime or varchar. Here is an example,

	define row_count = "100"
	select
	level as s,
	to_date((2000+level)*10000 + 0101, 'yyyymmdd') as d,
	chr(level) as c
	from dual
	connect by level <= &row_count;

view raw Generate_Row.oracle.sql hosted with ❤ by GitHub

SQL Server

SQL Server doesn't support "connect by" clause. However, you can use recursive CTE (common table expression) to get similar results, albeit more verbosely. Here is the same example in T-SQL,

	declare @row_count int = 100;
	with seq (s, d, c) as (
	select
	1 as s,
	cast('2000-01-01' as datetime) as d,
	char(1) as c
	union all
	select
	s + 1,
	dateadd(year, 1, d),
	char(s + 1)
	from seq
	where s < @row_count
	)
	select * from seq;

view raw Generate_Row.t.sql hosted with ❤ by GitHub

Calculate Fiscal Year in SQL

Fiscal years are usually not aligned with calendar years. Given a calendar date, it is a common task to calculate which fiscal year it is in or what date its fiscal year starts on.

Here is how to calculate them in Oracle.

	define shift = "6"
	with v as (
	select
	trunc(sysdate - floor(dbms_random.value()*1000)) as test_dt
	from dual
	connect by level <= 10
	)
	select
	test_dt,
	to_char(add_months(test_dt, -&shift), 'yyyy') || '-' || to_char(add_months(test_dt, 12-&shift), 'yyyy') as fisc_year,
	add_months(trunc(add_months(test_dt, -&shift), 'y'), &shift) as fisc_start
	from v
	order by test_dt

view raw Fiscal.oracle.sql hosted with ❤ by GitHub

Here is the SQL Server version.

	declare @shift int = 6
	select
	test_dt,
	concat(year(dateadd(month, -@shift, test_dt)), '-', year(dateadd(month, 12-@shift, test_dt))) as fisc_year,
	dateadd(month, @shift, datename(year, dateadd(month, -@shift, test_dt)) + '0101') as fisc_start
	from (values
	(getdate()),
	(cast('2014-03-31' as date)),
	(cast('2014-04-01' as date)),
	(cast('2014-07-01' as date)),
	(cast('2014-12-31' as date)),
	(cast('2015-01-01' as date)),
	(cast('2015-03-31' as date)),
	(cast('2015-04-01' as date)),
	(cast('2015-06-30' as date)),
	(cast('2015-07-01' as date)),
	(cast('2015-12-31' as date))
	) v(test_dt)
	order by test_dt

view raw Fiscal.t.sql hosted with ❤ by GitHub

Calculate Observed Holidays in Oracle and SQL Server

There are two types of holidays, weekday-based ones and date-based ones. Martin Luther King (MLK) Day, observed on the 3rd Monday of January, is an example of weekday-based holidays. Date-based holidays include Independence Day (4th of July) and Christmas Day (Dec. 25). When a date-base holiday falls on weekend, it is usually observed one day earlier on Friday, or later on Monday.

Here is an example Oracle implementation for calculating observed MLK Day and Independence Day.

	with yr_tbl as (
	select
	2000 + level as yr,
	to_date(101 + (2000+level)*10000, 'yyyymmdd') as jan1,
	to_date(704 + (2000+level)*10000, 'yyyymmdd') as jul4
	from dual
	connect by level <= 100
	)
	select
	yr,
	case
	when to_char(jan1, 'DY') in ('MON', 'SUN') then trunc(jan1, 'd') + 15
	else trunc(jan1, 'd') + 22
	end as MLK_DAY,
	case to_char(jul4, 'DY')
	when 'SAT' then jul4 - 1
	when 'SUN' then jul4 + 1
	else jul4
	end as IND_DAY
	from yr_tbl;

view raw Observed_Holiday.sql hosted with ❤ by GitHub

Here is the equivalent SQL Server implementation.

	with seq (yr, jan1, jul4) as (
	select
	2001 as yr,
	cast('2001-01-01' as datetime) as jan1,
	cast('2001-07-04' as datetime) as jul4
	union all
	select
	yr + 1,
	dateadd(year, 1, jan1),
	dateadd(year, 1, jul4)
	from seq
	where yr < 2100
	)
	select
	yr,
	case
	when datename(weekday, jan1) in ('Sunday', 'Monday') then dateadd(day, 16 - datepart(weekday, jan1), jan1)
	else dateadd(day, 23 - datepart(weekday, jan1), jan1)
	end as MLK_DAY,
	case datename(weekday, jul4)
	when 'Saturday' then dateadd(day, -1, jul4)
	when 'Sunday' then dateadd(day, 1, jul4)
	else jul4
	end as IND_DAY
	from seq

view raw Holiday.t.sql hosted with ❤ by GitHub