how to change hard coded dates to ytd please

Question

how to change hard coded dates to ytd please

asita

SSCarpal Tunnel

Points: 4144
More actions
September 25, 2022 at 3:12 pm

#4091747

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
September 26, 2022 at 6:57 am

This was removed by the editor as SPAM

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

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

ratbak Ten Centuries Points: 1381 More actions · Answer 4

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

Ed B Hall of Fame Points: 3669 More actions · Answer 5

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?