how to change hard coded dates to ytd please

Question

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

Answer 1

A general solution would be use a calendar table, and you can read about this here: https://www.sqlshack.com/designing-a-calendar-table/.

Here is a solution that is targeted for this specific problem. It may not be the most efficient, but it may be good enough:

   WITH dates AS (  
      SELECT date = cast(d.date as date)   
      FROM  (VALUES('2022-01-31'),  
                   ('2022-02-28'),  
                   ('2022-03-31'),  
                   ('2022-04-30'),  
                   ('2022-05-31'),  
                   ('2022-06-30'),  
                   ('2022-07-31'),  
                   ('2022-08-31')  
             ) AS d(date)  
   )  
   Select d.date, COUNT(DISTINCT P.ACCOUNTNO)   
   FROM  CustFunds P  
   JOIN  Customers A ON (P.ACCOUNTNO= A.ACCOUNTNO)  
   CROSS JOIN dates  
   where P.Activity = 'Active'   
     AND P.FundVALUE <> 0   
     AND P.TranDate >= '2022-01-01'   
     AND P.TranDate <= d.date  
     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)  
   GROUP BY d.date  

Answer 2

Hi @milan i
If there are records in every month, then you could add Group By clause to achieve Count() of different months. What you need to do is to give the date range.
Check this:

DECLARE @StartDate DATE ='2022-01-01'  
DECLARE @EndDate DATE ='2022-09-31'  
  
Select FORMAT(P.TranDate,'yyyy-MM')AS Year_Month,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 <= @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 <= @EndDate  
          AND A.AIBID = '8ca2-0a854437aedsdsdXX5'  
          AND A.ACCOUNTTYPE = 4)  
Group by FORMAT (P.TranDate,'yyyy-MM')   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.