Grouping (For different level of Data Cross Verification)

The Grouping Sets SQL Sample:

Instead of Roll up or Cube, we can use Grouping to verify the data in various level. Please have a look below on SQL query that help you to get data monthly sum, Product Sum, prod-month data sum etc.

Choose in SQL 2012

Here I am going to show you a very good example of CHOOSE function, newly introduced in SQL Server 11.0.

Let's take an example, I have a Table having daily temperature records. Take a fake example of January, 2012. I have created a table variable with 31 records and put some rand() values for temperature column.

Now, I have to display temperature status in below manner.

Here I am going to write an query using CHOOSE Function.

Hope,

This example will help you to understand choose function.

IIF (if and only if)

Get rid of Case Queries: 
Now we have new way to get rid of lengthy case SQL. In SQL 11.0 (Formally Name: SQL 2012) we can use IIF (if and only if) instead of Case. IIF is newly introduce in SQL 11, however it was already there with VB 4.0. 

So persons who have worked with any version of VB/ VB.Net, they are already aware about this function.



  

Multiplication of Rows

Multiplication of Rows:

In this example, we will demonstrate how to multiply rows in SQL Server using the log/exp operator.

---

Alternatively, a while loop can achieve the same result.

Logic to get All Dates

To get All Dates of Year:

Here I'm showing a simple query to generate all dates of one year. You may increase or decrease the range as per requirement. 

This query usually work to load date dimension table.

Get Active and InActive Counts

Get Active and Inactive Counts:

In most scenarios, we need to retrieve the count of records with Active and Inactive statuses. Let's take an example: We have a table with 2 columns (name and status), where status can have values of either 0 or 1.

0 means Inactive, and 1 means Active.

To count all Active and Inactive statuses, we have two approaches: either use two different subqueries and make a final join or simplify with the query methods shown below:

---

Using the SUM function with a CASE statement:

SELECT SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS Active, SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS InActive FROM table_name;

---

Using SUM and COUNT together:

SELECT Name, SUM(status) AS [Active Status Count], COUNT(status) - SUM(status) AS [Inactive Status Count] FROM table_name GROUP BY Name;

---