How to run a query multiple times with different values in date

Question

Post reply

How to run a query multiple times with different values in date

sailormonkey

SSC Enthusiast

Points: 173
More actions
October 4, 2022 at 8:30 pm

#4095519

I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date?
Values in date are 31Jan2022, 28Feb2022, 31Mar2022, 30Apr2022,...
Assume my query is simple:
select ID, Count
from XYZ
where Date between VariableA and VariableB

Thank you for recommendations!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 1

You could use a T-SQL cursor to loop through the months and return a dataset per month

You could retrieve the data all at once

select id,count(*),mymonth.id

from dataset

join mymonths on dataset.date between mymonth.start and mymonth.end

group by mymonth.id

MarkP Old Hand Points: 305 More actions · Answer 2

Is there a problem with doing it in one query?

For example,

SELECT ID, TRANSACTION_DATE, COUNT(*)
FROM
XYZ
WHERE TRANSACTION_DATE IN (your dates here*)
GROUP BY ID, TRANSACTION_DATE

* The your dates here section in brackets can then be either a list of dates or a query that gets what those dates are.

sailormonkey SSC Enthusiast Points: 173 More actions · Answer 3

I tried this method, but since the transaction_date need to between two variables in XYZ data (Variable A and Variable B), it seems not working correctly.

My code is similar like this:

SELECT ID, COUNT(*)

FROM

XYZ

WHERE TRANSACTION_DATE BETWEEN VARIABLEA AND VARIABLEB

Any advice? Thank you!!

MarkP Old Hand Points: 305 More actions · Answer 4

Sure. Instead of IN (your dates here)

Use BETWEEN VARIABLEA AND VARIABLEB

Declare your variables at the start.

DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2022-10-05';

SELECT ID, TRANSACTION_DATE,  COUNT(*)
FROM
XYZ
WHERE TRANSACTION_DATE BETWEEN @StartDate AND @EndDate
GROUP BY ID, TRANSACTION_DATE;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

What does 'not working correctly' mean? No results? Error message? Wrong results? Something else?

Note that if TRANSACTION_DATE is a DATETIME (rather than a DATE), BETWEEN is not the recommended way to limit your results. Instead, use

WHERE TRANSACTION_DATE >= @VARIABLEA AND TRANSACTION_DATE < @VARIABLEB

where @VARIABLEB is the beginning of the day following the end date you have in mind (eg, '20220104', if you want results up to and including '20220331')

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101096 More actions · Answer 6

Use a table to store the date ranges, then join to that.

DROP TABLE IF EXISTS #DATES;
CREATE TABLE #DATES (
    VARIABLEA date NOT NULL,
    VARIABLEB date NOT NULL
)
INSERT INTO #DATES VALUES
    ('01Jan2022', '31Jan2022'),
    ('01Feb2022', '28Feb2022'),
    ('01Mar2022', '31Mar2022'), 
    ('01Apr2022', '30Apr2022') /*, ...*/


SELECT ID, DATES.VARIABLEA, COUNT(*) AS ROW_COUNT

FROM

XYZ

INNER JOIN

#DATES DATES ON XYZ.DATE BETWEEN DATES.VARIABLEA AND DATES.VARIABLEB

GROUP BY 

ID, DATES.VARIABLEA

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".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

sailormonkey wrote:

I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date?
Values in date are 31Jan2022, 28Feb2022, 31Mar2022, 30Apr2022,...
Assume my query is simple:
select ID, Count
from XYZ
where Date between VariableA and VariableB
Thank you for recommendations!

Let's NOT assume a "simple query". You're trying to do something by month... what is it that you're trying to do by month?

The reason I'm asking is because you're automatically thinking "loop" and someone else suggested "cursor", which is just another form of RBAR looping. Present the whole problem and let's see if we can teach you a better way of thinking, especially since you're "new".

It could also be VERY helpful, not only to us but to you as well, if you were to read and understand the article at the first link in my signature line below. People will happily jump on your posts when even small sample of data are presented in a "Readily Consumable" format and the article shows you one of many ways to easily pull off such and thing and WHY it helps so much.

--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)