A Use Case of CROSS JOINS in SQL

Use case of SQL CROSS JOIN: Although CROSS JOIN is available in SQL, you rarely find any instance where you can use it. Here is one use case for CROSS JOIN in SQL:

Example with fictitious data: Assuming a tournament of Soccer to happen among four teams, what are the all possible matches between each of these teams, such that each team gets a chance to play with the remaining other three teams..?

You can use the CREATE and INSERT statements in the below screenshot 1 to create this table. Then run the SQL SELECT statement as in the screenshot 2 to get the desired results:

Screenshots:

image

Here is the SQL script containing all the statements that can be copied: SQL Cross Joins Use Case

Caveat: The CROSS JOIN here actually returns all permutation of the matches which includes the repetition of the combinations giving significance to the order of the teams in a match. i.e., a match between Brazil Vs Italy and Italy Vs Brazil will be treated as separate.

Solution: The predicate in the WHERE clause takes care of this and avoids any such repeated combinations and lets you disregard any significance attached to the order of the teams in a match.

Mathematical formula for Combinations

Happy learning..!! Don’t forget you leave your comments friends..!! Smile

Advertisements

Complement Negation: SQL Design Pattern to solve a frequently occurring interesting business data analysis problem

Here is an interesting business data analysis problem that I have come across multiple times in the recent times. Since I have seen this problem surfacing multiple times and the solution boils down to the same technique, it becomes an SQL Design Pattern and I named this baby as “Complement Negation”. Have shared the justification for the name at the end of this write-up.

Now, let me explain the scenario with an example using some fictitious data.

We have a table named “Projects” with the status details of various projects. Each project is given a unique project id. A Project may have multiple milestones where each milestone is identified by milestone id. The table also has the details of status of project and the status of each of the milestones. Status project and milestone can be either “closed” or “in-progress” (i.e., they are complementary).

Here we create the “Projects” table using below SQL:

—Step:1. Creating the table
CREATE TABLE Projects
(ProjectID VARCHAR(5)
, MilestoneID VARCHAR(5)
, ProjectStatus VARCHAR(15)
, MilestoneStatus VARCHAR(15)
PRIMARY KEY (ProjectID, MilestoneID)
);

Now let’s populate the table with some fictitious data using the below SQL:

INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS02’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS01’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS03’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS02’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P203’, ‘MS01’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS02’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS03’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS06’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS07’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS06’, ‘closed’, ‘in-progress’);

So, below is how the table looks:

SELECT * FROM Projects;

05June2013

Business rule is that a project is considered to be closed only when all the milestones under the project are closed.

However, the table has some records that does not satisfy this rule. e.g.: Project – P204 has two milestones (viz., MS02, MS07) whose status is “in-progress” but the project status itself is “closed”. Similarly, Project – P207 has one milestone (viz., MS06) whose status is “in-progress” but the project status itself is “closed”. These cases that doesn’t satisfy the business rule are considered abnormal cases/Projects.

Challenge at hand: Now the challenge at hand is to query the details of all abnormal projects (i.e., details or projects where project status is “closed” but the status of one or more milestones under the project is “in-progress”) and report the complete data.

Solution: Below is the SQL used to query/retrieve all the abnormal packages.

WITH temp AS (
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
, SUM(CASE WHEN MilestoneStatus = ‘in-progress’
                  THEN 1
                  ELSE 0
          END) OVER(PARTITION BY ProjectID) AS Count_Indicator
FROM Projects
)
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
FROM temp
WHERE Count_Indicator > 0
AND ProjectStatus = ‘closed’;

Results: The results appear as below:

ComplementNegation_SQLDesignPattern

Explanation: First, in the CTE, we retrieve all the details of projects that need to be reported. Then we derive a column that counts only the number of “in-progress” milestones under each project and name this derived column as, say Count_Indicator.

Now we retrieve the records where Project Status is “closed” but with the count of “in-progress” milestones (indicated by Count_Indicator derived column) being greater than 0 (i.e., there exists at least one milestone that is “in-progress”). This is nothing but retrieving the records where project status appears “closed” but with one or more underlying milestones still being “in-progress”.

Name Justification: The word Complement – since the status’ “closed” and “in-progress” are complementary (i.e., # of closed projects or milestones within each project plus # of in-progress projects or milestones within each project equals the total # of projects or milestones within each project respectively). The word Negation – since we test for status of one field for “closed” and the status of the other field for “in-progress” (i.e., “closed” test is negation of “in-progress” or vice versa).

Hence I feel the name for this SQL Design Pattern is justified to be “Complement – Negation”.

Hope you enjoyed reading this post. Do share your comments, suggestions, questions or clarifications, if any. Have great time until my next post..!! Smile

WordPress Tags: CTE,common table expression,Microsoft,sql server,t-sql,SQL,Complement,Negation,Design,Pattern,Here,data,analysis,times,solution,technique,justification,scenario,example,status,Project,Step,CREATE,TABLE,ProjectID,VARCHAR,MilestoneID,ProjectStatus,MilestoneStatus,PRIMARY,INSERT,INTO,VALUES,SELECT,FROM,records,cases,Challenge,packages,CASE,ELSE,OVER,PARTITION,Count_Indicator,WHERE,Results,Explanation,column,Name,word,Complementary,equals,itself

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 of the table. Then see the results in the table again to verify. The below T-SQL does all this:

Interchanging column values in a table
Interchanging column values in a table

Usually the UPDATE statement as in the above picture gives the impression of the both columns returning the same set of values (here Last_Name column values in both columns). But SQL Server behaves intelligent and interchanges the values in the two columns. It internally stores the values in a temporary column and then does the interchange.

Hope you enjoyed reading this post and found somewhat interesting learning. Keep coming back or follow this “Data Language” blog for more such interesting posts.

Happy time until my next blog post.. 🙂

 

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:

T-SQL DateTime Comparison

Have created a temporary table with two date columns – From_Date and To_Date and inserted a couple of random date values. First output shows the table contents. Observe that the timestamps are also included with the date values. 

Now declared a date variable and assigned a date value without timestamp. Then retrieved results from the table where From_Date is less than or equal to the date variable in two ways: 

– once by converting the From_Date column into string taking the format “mm/dd/yyyy” (note the format style 101 in the convert function syntax) and then casting this date string back to datetime data type. This removes the timestamp from the datetime values.

– the other time by comparing the direct comparison between From_Date datetime column and datetime variable. This comparison compares the both date and time pieces of the From_Date column and the datetime variable, hence resulting in incorrect(less) number if results. 

Hope that helps. Do leave your comments or questions, if any.. 🙂