How do I edit my SQL query such that I get the last 1 year's data when my datecode is like &#03-CodePudding

WITH Cte AS 
(
    SELECT 
        faw.AccountId,
        fam.AccountKey,
        fam.YearMonthCode,
        ROW_NUMBER() OVER (PARTITION BY faw.accountid ORDER BY faw.accountid DESC) AS rn,
        MonthsSinceInception
    FROM
        fact.AccountMonthlyAggregate fam
    INNER JOIN 
        fact.AccountAtSalevw faw ON fam.AccountKey = faw.AccountKey
    WHERE  
        faw.AccountId = 19695
)
SELECT *
FROM cte
WHERE yearmonthcode >= 202109

As you can see it is getting all records based on year month code since September 2021. However, I don't want to manually change this every month to 202110, 202111, and so on.

How can I just keep this dynamic something like minus 1 year?

I know something like the below works for only dates that are in normal form such as 2021-09-10

SELECT * 
FROM MyTable
WHERE MyDate < DATEADD(year, -1, GETDATE())

What would I need to do since my year is a yearmonth code combination?

CodePudding user response：

One option is to generate the value up front as another (first) part of the CTE.

Working code to demonstrate calculation:

DECLARE 
  @dated  date = Sysdatetime()
, @yyyymm integer
;

SELECT
  @yyyymm = ( DATEPART( yyyy, @dated ) -1 ) * 100   DATEPART( mm, @dated )
;
SELECT
  @yyyymm
;

Main reason to generate the value up front is that using a calculation in a WHERE clause can be detrimental to performance.

Wrapped up in CTE it looks maybe like:

WITH 
  cteCriteria
(
  YYYYMM
)
AS
(
  SELECT
    ( DATEPART( yyyy, Sysdatetime() ) -1 ) * 100   DATEPART( mm, Sysdatetime() )
)
, cteData 
AS 
(
  SELECT 
    faw.AccountId,
    fam.AccountKey,
    fam.YearMonthCode,
    ROW_NUMBER() OVER (PARTITION BY faw.accountid ORDER BY faw.accountid DESC) AS rn,
    MonthsSinceInception
  FROM
    fact.AccountMonthlyAggregate  AS fam
    INNER JOIN 
      fact.AccountAtSalevw AS faw 
      ON  fam.AccountKey = faw.AccountKey
    CROSS JOIN
      cteCriteria AS crit
  WHERE  
        faw.AccountId = 19695
    AND --yyyymm criteria applied
        fam.YearMonthCode >= crit.YYYYMM
)
SELECT 
  *
FROM 
  cteData
;

Using a cross join on a single row result will work as it will not multiply results. However this is an edge case to be used with caution.

CodePudding user response：

Actually it could be as simple as:

 Select CurrYear = convert(varchar(6),getdate(),112)
       ,PrevYear = convert(varchar(6),dateadd(year,-1,getdate()),112)

Results

CurrYear    PrevYear
202211      202111

Just to be clear... DATES SHOULD BE STORED AS DATES