CODEWITHSUNDEEP
sql-server
Ravi
Ravi

Reputation: 1350

Monthly Data Query With Group by

I have table order with fields id, department, date and amount. I want query which returns last 3 months or 6 months records of department total transactions and total amount. if there is no transactions for any month i want 0 as total transactions and amount.

I tried several queries but i fail to generate output as i want two group by.

Here is Something what i have tried.

Select SUM(ISNULL(od.Amount,0)) as Points, Count(ISNULL(od.OrderId,0)) as Transactions,
DATEPART(MONTH, od.OrderDate) as Month, max(DeptId)
from POR_OrderDetails od
where 
cast(od.OrderDate as date) BETWEEN cast(dateadd(month, -3, GETDATE()) as date) and cast(getdate() as date) 
group by DATEPART(YEAR, od.OrderDate),DATEPART(MONTH, od.OrderDate), od.DeptId

Test Data

+---------+--------+--------+-----------+
| OrderId | Amount | DeptId | OrderDate |
+---------+--------+--------+-----------+
| 1       | 10     | 1      | 15/3/2020 |
+---------+--------+--------+-----------+
| 2       | 10     | 3      | 1/3/2020  |
|         |        |        |           |
+---------+--------+--------+-----------+
| 3       | 10     | 2      | 25/2/2020 |
+---------+--------+--------+-----------+
| 4       | 20     | 1      | 1/2/2020  |
+---------+--------+--------+-----------+

Expected Output

+-------+-------------+-------------------+--------+
| Month | TotalAmount | TotalTransactions | DeptId |
+-------+-------------+-------------------+--------+
| 1     | 0           | 0                 | 1      |
+-------+-------------+-------------------+--------+
| 1     | 0           | 0                 | 2      |
+-------+-------------+-------------------+--------+
| 1     | 0           | 0                 | 3      |
+-------+-------------+-------------------+--------+
| 2     | 20          | 1                 | 1      |
+-------+-------------+-------------------+--------+
| 2     | 10          | 1                 | 2      |
+-------+-------------+-------------------+--------+
| 2     | 0           | 0                 | 3      |
+-------+-------------+-------------------+--------+
| 3     | 10          | 1                 | 1      |
+-------+-------------+-------------------+--------+
| 3     | 0           | 0                 | 2      |
+-------+-------------+-------------------+--------+
| 3     | 10          | 1                 | 3      |
+-------+-------------+-------------------+--------+

Upvotes: 0

Views: 33

Answers (1)

Thom A
Thom A

Reputation: 96016

Firstly, I suggest creating a Calendar Table, as it will help here, and they are an invaluable additional to any instance.

Once you have that set up, it's as simple as cross joining your department table (which I assume you have) to your Calendar Table, and then making a LEFT JOIN to your Department Orders table. So something like this:

SELECT C.MonthNo,
       SUM(DOAmount) AS TotalAmount,
       COUNT(DOOrderID) AS TotalTransactions,
       D.DeptID
FROM dbo.Department D
     CROSS JOIN dbo.Calendar C
     LEFT JOIN dbo.DepartmentOrder DO ON D.DeptID = DO.DeptID
                                     AND C.CalendarDate = DO.OrderDate
WHERE C.CalendarDate >= '20200101'
  AND C.CalendarDate < '20200401'
GROUP BY C.MonthNo,
         D.DeptID
ORDER BY C.MonthNo,
         D.DeptID;

Upvotes: 1

Related Questions