How to calculate the sum of two columns in the table, for the last week from the current one?

Question

SQL

There is a table in MS SQL, it is necessary to calculate the sum of the values ​​in one and the other column, given the following condition: the values ​​must be calculated for the last week from the current week. The time in the table is indicated in the "period" column. At the same time, the "period" column is not a date format, but nvarchar and the date is written there as follows: '2022-01' - denoting year 22 and 1 week in a year, '2022-06' - denoting year 22 and week 6 in year, etc. The idea is the following, take the maximum value of the "period" column, find the second largest value in it, and use it to calculate the sums of the required two columns

SELECT SUM(some_value) AS 'Number of order_cost', SUM(just_value) AS 'Number of deliveries', MAX (period)
FROM dbo.

Approximately such a request I imagine in meaning

Answer 1

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY period DESC) drnk
    FROM source_table
)
SELECT period,
       SUM(some_value) AS 'Количество order_cost', 
       SUM(just_value) AS 'Количество доставок', 
FROM cte
WHERE drnk = 2
GROUP BY 1;

Answer 2

-- Get previous week
SELECT CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())));

SELECT 
    SUM(some_value) AS 'Количество order_cost', SUM(just_value) AS 'Количество доставок'
FROM dbo.Sheet1$
WHERE 
    period = CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())))

Test MS SQL DATEADD