How can I efficiently and elegantly add current week flag column using SQL query as shown in the picture attached. It has to be True or 1 for the week TODAY falls under. I was thinking of FIRST_VALUE
and CASE
. Am I in the right track?
How can I efficiently and elegantly add current week flag column using SQL query as shown in the picture attached. It has to be True or 1 for the week TODAY falls under. I was thinking of FIRST_VALUE
and CASE
. Am I in the right track?
3条答案
按热度按时间n9vozmp41#
If your weeks start on Sundays you can choose an early Saturday that doesn't appear in your data and count the whole weeks (7days) since that date:
wd2eg0qa2#
You can use DATEPART to get the week number:
2wnc66cl3#
This is a recursive example of using
DATEPART()
. The difference here is it runs DATEPART() only for that week, not your entire dataset. There is an inferred problem of identifying the same week (number) for the same year, and your sample data contains different years. The easiest way I can think of is to identify the MAX and MIN date for that week/year. I use Common Table Expressions (CTEs) and recursion below: