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,

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,

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. Here is the SQL Server version.

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. Here is the equivalent SQL Server implementation.