SQL supports most aggregate functions except one fundamental aggregation to calculate product of values in a column. If you are wondering what could be a scenario where you may have to calculate product of values in a column, here is a business scenario that demands it. Business Scenario: For the sake of brevity, let's say... Continue Reading →
Why is SQL one of the most powerful tools in the arsenal of an Analyst?
Out of information analysis, comes wisdom. It’s this information analysis that is one of the important reasons that created the need for storage of data. The world of databases was sprouted to address this need of data storage and many languages were born to interact and deal with the data stored in these databases. But... Continue Reading →
Share this:
Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models
Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the... Continue Reading →
Share this:
A White Paper – Solving Relational Division problem in SQL – The Analyst Way
Hi readers..!! Here I go posting a white paper on one of the classical and intellectually challenging problems that an analyst might come across. The paper is titled - "Understanding and Solving Relational Division problems in SQL - The Analyst way". Hope you enjoy reading this paper. Please leave your comments and suggestions, if any, here.... Continue Reading →
Share this:
Truncate time part from Datetime column or literal in Oracle SQL
I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here - http://pivotronics.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/ Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL: TRUNC(datetime_column) or TRUNC(datetime_expression) very straight forward...!! isn't it.. ? 🙂
Share this:
Single T-SQL query to swapping the values of two columns without using temporary column
How to swap the values of two columns of a table ? Let me explain the scenario with an example - Let's create a table named "Employees" with columns "First_Name" and "Last_Name". Then populate the table with some fictitious data. Then view the results once and then interchange the values in the "First_Name" and "Last_Name" columns... Continue Reading →
Share this:
Comparing datetime column with a date literal in T-SQL
Hi Friends.. I have seen that comparing a datetime column in a table in SQL Server database, that has both date and time parts (e.g. 2012-12-05 10:37:36.897) with a date literal that has only date part is a not straight forward. Let me explain with an example: Have created a temporary table with two date... Continue Reading →
