how to change hard coded dates to ytd please

  • Dear Helpers,

    Below query is working fine giving me what i needed but if i need it for 9 months(ytd) i need to run this query for 6 times by changing the data values for each month.

    Can you please help with automate this for any continuous months in other words same query for YTD with out changing each time the dates (hard coded ones).

     

    Select COUNT(DISTINCT P.ACCOUNTNO) from CustFunds P

    JOIN Customers A ON (P.ACCOUNTNO= A.ACCOUNTNO)

    where P.Activity = 'Active' AND P.FundVALUE <> 0 AND

    P.TranDate) >= '2022-01-01' AND P.TranDate <= '2022-01-31'

    AND A.AIBID = '8ca2-0a854437aedsdsdXX5'

    AND A.ACCOUNTTYPE = 4

    AND P.ACCOUNTNO NOT IN (Select T.ACCOUNTNO from DW_TECH_PROD.STG_DYNAMODB.TRADE_ORDERS T

    JOIN Customers A ON (T.ACCOUNTNO= A.ACCOUNTNO)

    WHERE EXECUTEDDate >= '2022-01-01' AND EXECUTEDDate <= '2022-01-31'

    AND A.AIBID = '8ca2-0a854437aedsdsdXX5'

    AND A.ACCOUNTTYPE = 4);

     

    Thank you

    ASiti

     

  • Your query does not parse (it has two WHERE clauses). But no matter, for (current) YTD, use something like

    WHERE SomeDate >= DATEFROMPARTS(YEAR(GETDATE()),1,1) AND SomeDate <= GETDATE()

    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.

  • Not that it matters now but the parsing error is due to a parentheses after P.TranDate in the WHERE of the outer query.  The two WHERE clauses are ok because one of them is in an inner query for a NOT IN.

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

  • This was removed by the editor as SPAM

  • DECLARE @EndDate date
    DECLARE @StartDate date

    SET @StartDate = '20220101' --<<-- change @Start and @End values to get any date range you want
    --get end of current month
    SET @EndDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

    Select COUNT(DISTINCT P.ACCOUNTNO) from CustFunds P

    JOIN Customers A ON (P.ACCOUNTNO= A.ACCOUNTNO)

    where P.Activity = 'Active' AND P.FundVALUE <> 0 AND

    P.TranDate) >= @StartDate AND P.TranDate < DATEADD(DAY, 1, @EndDate)

    AND A.AIBID = '8ca2-0a854437aedsdsdXX5'

    AND A.ACCOUNTTYPE = 4

    AND P.ACCOUNTNO NOT IN (Select T.ACCOUNTNO from DW_TECH_PROD.STG_DYNAMODB.TRADE_ORDERS T

    JOIN Customers A ON (T.ACCOUNTNO= A.ACCOUNTNO)

    WHERE EXECUTEDDate >= @StartDate AND EXECUTEDDate < DATEADD(DAY, 1, @EndDate)

    AND A.AIBID = '8ca2-0a854437aedsdsdXX5'

    AND A.ACCOUNTTYPE = 4);

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • This will get you the first day of the current year:

    DECLARE @StartDate DATE = DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0);

    Do you want only completed months, or everything through now (current moment? current date?)

     

     

  • Why do you need to run the query six times for nine months? Do you need a count by month, rather than a total ytd count? Does the ExecuteDate need to be in the same month as the Trandate?

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

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