There are two columns, XCHG_DATE and USD_KRW, and the table contains daily data.

What I am trying to do is to select weekly data from the daily data.

E.g) (2022-03-01, value), (2022-03-08, value), (2022-03-15, value), (2022-03-22, value) and so one...

The current SQL I have is:

SELECT CE.XCHG_DATE xchageDate
    ,  CE.USD_KRW usdKrw
    FROM(
        SELECT DATEADD(WEEK, DATEDIFF(WEEK, 1, XCHG_DATE), 4) xchageDate
        FROM CWL_EXCHANGE 
        WHERE XCHG_DATE BETWEEN '20220301' AND '20220523' 
        GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 1, XCHG_DATE),4)
    ) AS RESULT
LEFT JOIN CWL_EXCHANGE CE
ON CE.XCHG_DATE = RESULT.xchageDate
WHERE RESULT.xchageDate = CE.XCHG_DATE
ORDER BY CE.XCHG_DATE;

This query gives me weekly data from 20220304 to 20220520, but I need the data from 2022-03 to 2022-05-23(today's date).

Can anyone please help me of how to solve this problem? Thanks in advance!

Sample Data:

COLUMNS = XCHG_DATE    USD_KRW
          2022-05-23   1
          2022-05-22   2
          2022-05-21   3
          2022-05-20   4
          2022-05-19   5
          2022-05-18   6
          2022-05-17   7
          2022-05-16   8
          2022-05-15   9
          2022-05-14   10
          2022-05-13   11          
          2022-05-12   12
          2022-05-11   13
          2022-05-10   14
          2022-05-09   15
          2022-05-08   16
          2022-05-07   17
          2022-05-06   18

Current Output :

20220506  18
20220513  11
20220520  4

Expected Output :

20220509  15  
20220516  8  
20220523  1

CodePudding user response：

You will need a calendar table with Weekdaynumber to arrive at the earlier weekdays corresponding to Today's date(23 May 2022). This will make the calculation easier.

DECLARE @StartDate DATE = '2022-05-01'
DECLARE @EndDate DATE = '2022-05-31'
declare @table table (XCHG_DATE    date, USD_KRW int);
insert into @table 
values ('2022-05-23', 1  )
          ,('2022-05-22', 2  )
          ,('2022-05-21', 3  )
          ,('2022-05-20', 4  )
          ,('2022-05-19', 5  )
          ,('2022-05-18', 6  )
          ,('2022-05-17', 7  )
          ,('2022-05-16', 8  )
          ,('2022-05-15', 9  )
          ,('2022-05-14', 10 )
          ,('2022-05-13', 11 )         
          ,('2022-05-12', 12 )
          ,('2022-05-11', 13 )
          ,('2022-05-10', 14 )
          ,('2022-05-09', 15 )
          ,('2022-05-08', 16 )
          ,('2022-05-07', 17 )
          ,('2022-05-06', 18 );
;WITH Cal(n) AS
(
SELECT 0 UNION ALL SELECT n   1 FROM Cal
WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
),
FnlDt(d,weeknum) AS
(
SELECT DATEADD(DAY, n, @StartDate),datepart(dw, DATEADD(DAY, n, @StartDate)) as weeknum FROM Cal
)
SELECT t.XCHG_DATE,t.USD_KRW 
from FnlDt as c
INNER JOIN @table as t
on t.XCHG_DATE = c.d
where c.weeknum = datepart(dw, getdate()) -- Weekdaynumber today 

CodePudding user response：

Sub in GETDATE() for the hardcoded value if you always want todays date

SELECT * 
FROM CWL_EXCHANGE  
WHERE DATEPART(dw, XCHG_DATE) = DATEPART(dw, '20220523')