Is it possible to SUM on two different ranges in one SELECT query?

Question

Post reply

Is it possible to SUM on two different ranges in one SELECT query?

BOR15K

SSCertifiable

Points: 5894

March 16, 2023 at 2:42 pm

#4161509

Hello All and apologies for a silly question.

I wonder if there is a better way to SUM values for two ranges in one DB Query statement, please?

We have our DB server in one location and the app one is far away. Obviously I can simply run two queries on the server side

as a function and return two SUMs but I thought if there is any better way. I tried to use CTE and it works, but the execution plan is terrible. Below is an example based on a simple sys.tables table

DECLARE @v_from_date DATETIME = '2020-01-01',
        @v_to_date DATETIME = '2024-01-01'

SELECT SUM(t.max_column_id_used) AS tables_created
FROM sys.tables t
WHERE t.type ='U'
AND t.create_date BETWEEN @v_from_date AND @v_to_date

SELECT SUM(t.max_column_id_used) AS tables_modified
FROM sys.tables t
WHERE t.type ='U'
AND t.modify_date BETWEEN @v_from_date AND @v_to_date;

WITH cte_dates AS
(SELECT t.max_column_id_used,
        t.create_date,
t.modify_date
FROM sys.tables t
WHERE t.type ='U'),
created_date AS(
SELECT SUM(t.max_column_id_used) AS tables_created
FROM cte_dates t
WHERE t.create_date BETWEEN @v_from_date AND @v_to_date),
modified_date AS (
SELECT SUM(t.max_column_id_used) AS tables_modified
FROM cte_dates t
WHERE t.modify_date BETWEEN @v_from_date AND @v_to_date)
SELECT *
FROM modified_date
UNION ALL
SELECT *
FROM created_date

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

SELECT 
    SUM(CASE WHEN t.create_date BETWEEN @v_from_date AND @v_to_date THEN t.max_column_id_used ELSE 0 END) AS tables_created,
    SUM(CASE WHEN t.modify_date BETWEEN @v_from_date AND @v_to_date THEN t.max_column_id_used ELSE 0 END) AS tables_modified
FROM sys.tables t
WHERE t.type ='U'
AND (t.create_date BETWEEN @v_from_date AND @v_to_date OR
     t.modify_date BETWEEN @v_from_date AND @v_to_date)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

BOR15K

SSCertifiable

Points: 5894

March 16, 2023 at 2:53 pm

#4161512

Wasn't it obvious.... THANK YOU!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Then pray that your data has no times in it because of the use of BETWEEN. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Jeff Moden wrote:

Then pray that your data has no times in it because of the use of BETWEEN. 😉

I have got your point.

I have removed the dates related part from WHERE clause and it works as expected, and because our real tables are relatively small, the execution plan is still much better than running two separate SELECT queries.

Thank you for pointing it out.