Resample SQL Data with Count condition

Other SQL Server Topics
1

Hello ALL,

I have a time series data (date on one column and some integer value on second column).

I want to resample my data if the query returns more than 1 million rows over a selected period of time.

I mean if i select 6 month time period, and the select query returns me more than 1 million rows, it performs the resampling sql statement and if it is less than 1 million rows, it returns the same as it is.

I am able to resample my data but I am struggling with the condition application of count rows .

Can someone help me?

This is my resampling code:

SELECT 
  floor(extract(epoch from "ValueTime")/1800)*1800 AS "time",
  min("NumValue") AS """NumValue"""
FROM testing_table_3781
WHERE
  "ValueTime" BETWEEN '2021-10-04T20:40:28.086Z' AND '2021-11-11T08:23:16.238Z'
GROUP BY 1
ORDER BY 1

So here if the return of rows above is more than 1 milllion, it should select and show all rows in the query above.

If it is less than 1 million, it should run this sql query:

SELECT
  "ValueTime" AS "time",
  "NumValue"
FROM testing_table_3781
WHERE
  "ValueTime" BETWEEN '2021-10-04T20:40:28.086Z' AND '2021-11-11T08:23:16.238Z'
ORDER BY 1

Can someone help me form the correct query?

2

/* We want to resample the data when it has more then 1000000 rows */

DECLARE @Count INT

SELECT @COUNT=COUNT(*)
FROM testing_table_3781
WHERE
"ValueTime" BETWEEN '2021-10-04T20:40:28.086Z' AND '2021-11-11T08:23:16.238Z'

IF @COUNT > 1000000
BEGIN

SELECT
floor(extract(epoch from "ValueTime")/1800)*1800 AS "time",
min("NumValue") AS """NumValue"""
FROM testing_table_3781
WHERE
"ValueTime" BETWEEN '2021-10-04T20:40:28.086Z' AND '2021-11-11T08:23:16.238Z'
GROUP BY 1
ORDER BY 1

END
ELSE
BEGIN
SELECT
"ValueTime" AS "time",
"NumValue"
FROM testing_table_3781
WHERE
"ValueTime" BETWEEN '2021-10-04T20:40:28.086Z' AND '2021-11-11T08:23:16.238Z'
ORDER BY 1
END

If you really want to use BETWEEN you should be aware of this: