CODEWITHSUNDEEP
sqlsql-server
Lifewithsun
Lifewithsun

Reputation: 998

Combine result for current year and previous year

I have two queries which calculate the % for current year and previous year in a table. I need to create a single query instead of two different queries (* to save execution time).

My query look like this:

DECLARE @temptable TABLE (Id INT,FaceId INT,TimeStamp DATETIME)

INSERT INTO @temptable (Id,FaceId,TimeStamp) VALUES 
(1,1,'2021-08-31 18:29:27.103'),(2,1,'2021-05-28 18:29:27.103'),(3,2,'2020-08-31 18:29:27.103'),(4,1,'2020-03-15 18:29:27.103')


--Query for current year
SELECT CAST((100 *(2*(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END))) /
(2 *(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2))
FROM @temptable h 
WHERE YEAR(h.TimeStamp) = YEAR(GETDATE())

--Query for previous year
SELECT CAST((100 *(2*(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END))) /
(2 *(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2))
FROM @temptable h 
WHERE YEAR(h.TimeStamp) = YEAR(GETDATE())-1

Is there any way to combine both in a single query?

Upvotes: 0

Views: 72

Answers (1)

Thom A
Thom A

Reputation: 95554

Use a GROUP BY clause:

--Query for current year
SELECT YEAR(h.TimeStamp) AS [Year],
       CAST((100 * (2 * (AVG(CASE WHEN h.FaceId = 1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN h.FaceId = 2 THEN 1.0 ELSE 0 END))) / (2 * (AVG(CASE WHEN h.FaceId = 1 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId = 2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId = 3 THEN 1.0 ELSE 0 END))) AS decimal(5, 2))
FROM @temptable h
WHERE h.TimeStamp >= DATEFROMPARTS(YEAR(GETDATE())-1,1,1)
  AND h.TimeStamp < DATEFROMPARTS(YEAR(GETDATE())+1,1,1)
GROUP BY YEAR(h.TimeStamp);

I also make your WHERE SARGable, by converting it to date boundaries.

Upvotes: 2

Related Questions