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

  • 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!

  • 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

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

  • 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!!

  • 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;

     

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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)
    Intro to Tally Tables and Functions

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

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