- SQL Server
- Oracle (provider)
- SQLite (provider)
- MySQL (provider)
- Postgresql (provider)
- DB2 (provider)
Here is the code on GitHub.
Good Programmers Copy. Great Programmers Steal.
Wednesday, May 27, 2015
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,
Saturday, May 23, 2015
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)
|
|
Wednesday, May 13, 2015
Understand Oracle MODEL clause in one sentence
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.Monday, February 9, 2015
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,
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
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,
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Sunday, February 8, 2015
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.
Here is how to calculate them in Oracle.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Thursday, February 5, 2015
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.
Here is an example Oracle implementation for calculating observed MLK Day and Independence Day.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Subscribe to:
Posts (Atom)