OneSql PowerShell Module Fits All Relational Databases

OneSql is a PowerShell module providing a universal query interface to all relational databases, as long as there is a .NET data provider or an ODBC driver for the database. Supported databases include, but not limited to,

• SQL Server
• Oracle (provider)
• SQLite (provider)
• MySQL (provider)
• Postgresql (provider)
• DB2 (provider)

Here is the code on GitHub.

Date Calculation in Oracle and SQL Server

Oracle

In Oracle, the trick of date calculation is to align a date to its appropriate baseline with TRUNC() function, and then make adjustment by days and/or months. Here are some examples.

Date		Oracle
Week	First day (Sunday)	trunc(sysdate, 'd')
	First workkday (Monday)	trunc(sysdate, 'd') + 1
	Last workday (Friday)	trunc(sysdate, 'd') + 5
	Last day (Saturday)	trunc(sysdate, 'd') + 6
	Previous Sunday	trunc(sysdate, 'd') - 7
	Previous Monday	trunc(sysdate, 'd') - 6
	Previous Friday	trunc(sysdate, 'd') - 2
	Previous Saturday	trunc(sysdate, 'd') - 1
Month	First day	trunc(sysdate, 'mm')
	Last day	add_months(trunc(sysdate, 'mm'), 1) - 1
	Previous 1st	add_months(trunc(sysdate, 'mm'), -1)
	Previous last	trunc(sysdate, 'mm') - 1
Quarter	First day	trunc(sysdate, 'q')
	Last day	add_months(trunc(sysdate, 'q'), 3) - 1
	Previous 1st	add_months(trunc(sysdate, 'q'), -3)
	Previous last	trunc(sysdate, 'q') - 1
Year	First day	trunc(sysdate, 'y')
	Last day	add_months(trunc(sysdate, 'y'), 12) - 1
	Previous 1st	add_months(trunc(sysdate, 'y'), -12)
	Previous last	trunc(sysdate, 'y') - 1
Fiscal Year 7/1-6/30	First day	add_months(trunc(add_months(sysdate, -6), 'y'), 6)
	Last day	add_months(trunc(add_months(sysdate, 6), 'y'), 6) - 1
	Previous 1st	add_months(trunc(add_months(sysdate, -18), 'y'), 6)
	Previous last	add_months(trunc(add_months(sysdate, -6), 'y'), 6) - 1

SQL Server

In SQL Server, the basic strategy of date calculation is to compare a date against day 0 (01/01/1900, Monday). First, calculate the difference between day 0 and today at a particular unit (year, month, week, etc). Then add the difference back to day 0. You can adjust the difference and/or shift day 0 to reach the final result. Examples follow.

Date		T-SQL
Week	First day (Sunday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1)
	First workkday (Monday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 0)
	Last workday (Friday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 4)
	Last day (Saturday)	DATEADD(wk, DATEDIFF(wk, 0, getdate()), 5)
	Previous Sunday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -8)
	Previous Monday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -7)
	Previous Friday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -3)
	Previous Saturday	DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2)
Month	First day	DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
	Last day	DATEADD(mm, DATEDIFF(mm, 0, getdate()) + 1, -1)
	Previous 1st	DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)
	Previous last	DATEADD(mm, DATEDIFF(mm, 0, getdate()), -1)
Quarter	First day	DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0)
	Last day	DATEADD(qq, DATEDIFF(qq, 0, getdate()) + 1, -1)
	Previous 1st	DATEADD(qq, DATEDIFF(qq, 0, getdate()) - 1, 0)
	Previous last	DATEADD(qq, DATEDIFF(qq, 0, getdate()), -1)
Year	First day	DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
	Last day	DATEADD(yy, DATEDIFF(yy, 0, getdate()), 364)
	Previous 1st	DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
	Previous last	DATEADD(yy, DATEDIFF(yy, 0, getdate()), -1)
Fiscal Year 7/1-6/30	First day	cast(datename(year, dateadd(month, -6, getdate())) + '0701' as datetime)
	Last day	cast(datename(year, dateadd(month, 6, getdate())) + '0630' as datetime)
	Previous 1st	cast(datename(year, dateadd(month, -18, getdate())) + '0701' as datetime)
	Previous last	cast(datename(year, dateadd(month, -6, getdate())) + '0630' as datetime)

OBIEE

In OBIEE, the calculation strategy is the same as SQL Server.

Date		OBIEE
Day	Today	CURRENT_DATE
	Yesterday	TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)
Week	First day (Sunday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
	First workkday (Monday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last workday (Friday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-05')
	Last day (Saturday)	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-06')
	Previous Sunday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-24')
	Previous Monday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-25')
	Previous Friday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-29')
	Previous Saturday	TimestampAdd(SQL_TSI_WEEK, TimestampDiff(SQL_TSI_WEEK, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-30')
Month	First day	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-31')
	Previous 1st	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-01')
	Previous last	TimestampAdd(SQL_TSI_MONTH, TimestampDiff(SQL_TSI_MONTH, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Quarter	First day	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1900-03-31')
	Previous 1st	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1899-10-01')
	Previous last	TimestampAdd(SQL_TSI_QUARTER, TimestampDiff(SQL_TSI_QUARTER, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Year	First day	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1900-01-01')
	Last day	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1900-12-31')
	Previous 1st	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1899-01-01')
	Previous last	TimestampAdd(SQL_TSI_YEAR, TimestampDiff(SQL_TSI_YEAR, DATE'1900-01-01', CURRENT_DATE), DATE'1899-12-31')
Fiscal Year 7/1-6/30	First day	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6, CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
	Last day	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, 6, CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)
	Previous 1st	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -18, CURRENT_DATE)) as VARCHAR(4)), '/07/01') as DATE)
	Previous last	CAST(CONCAT(CAST(YEAR(TimestampAdd(SQL_TSI_MONTH, -6, CURRENT_DATE)) as VARCHAR(4)), '/06/30') as DATE)

Understand Oracle MODEL clause in one sentence

CodeProject

Short Version

Oracle MODEL clause allows 3-D access to a data set, compared to regular SQL functions' 1-D access.

Long Version

Oracle MODEL clause let you access any columns in the same row (1st dimension), and in any other rows (2nd dimension). In addition, you can use the calculated results immediately in the same query, enabling sequential calculation (3rd dimension).

Implication

The 3-D access extends SQL's data calculation capability in a revolutionary leap. It is similar to compare a 3-D printer to a hand-held label printer. It enables implementation of complex business logic within SQL, which previously has to be implemented in custom functions or stored procedures, or even outside of database using other programming languages.

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.