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;

---