Question 313157 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Data not populating last four weeks from previous year i.e. W50,51,52 – SQL

I have a code that I have been using to get data for the last few months but when the new year rolled around, I realized that it was not populating any data cause it was not counting the weeks properly.

I wrote the code as week – 4 but it’s not giving any data for the last few weeks of December 2023. Can someone help me figure out how to edit the code so it gives me the last few weeks.
I’m assuming it’s cause the current week is 1 and 1 – 4 = -3 which doesn’t exist.

Select id,    
EXTRACT(WEEK FROM transaction_date) as week
    from table1
    where week BETWEEN DATEPART(WEEK, GETDATE())-4 AND DATEPART(WEEK, GETDATE())
Tags:

2

Answers


  1. 0

    Challenges like this are much easier to resolve with the use of a Calendar table.

    This becomes a much more straightforward task when you simply use the actual dates, rather than a part of them. Consider:

    assuming TSQL/SQL Server by OPs use of DATEPART

    SELECT 
  CAST(DATEADD(WEEK,-4,DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE())) AS DATE) AS RangeStart,
  DATEADD(MILLISECOND,-3,CAST(DATEADD(DAY,7,CAST(DATEADD(WEEK,-4,DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE())) AS DATE)) AS DATETIME)) AS RangeEnd;
    RangeStart RangeEnd
    2023-12-03 2023-12-09 23:59:59.997 
    DECLARE @RangeStart DATETIME = CAST(DATEADD(WEEK,-4,DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE())) AS DATE) AS RangeStart,
 @RangeEnd DATETIME = DATEADD(MILLISECOND,-3,CAST(DATEADD(DAY,7,CAST(DATEADD(WEEK,-4,DATEADD(DAY,1-DATEPART(WEEKDAY,GETDATE()),GETDATE())) AS DATE)) AS DATETIME)) AS RangeEnd

SELECT t.id, DATEPART(WEEK,t.transaction_date) AS week
  FROM table1 t
 WHERE t.transaction_date BETWEEN @RangeStart AND @RangeEnd;
    Login or Signup to reply.
  2. 0

    current week is 1 and 1 – 4 = -3 which doesn’t exist.

    As this is a data lookup and you have not documented the list of lookup values, the simple answer is to add the missing values to your list. Sure it’s a hack, but you have a list of weeks and if -3, -2, -1 and 0 are significant to your logic, then they probably deserve to have an entry in your lookup list.

    If the lookup list has actual calendar dates, and isn’t a lookup table at all, then resolve the actual dates from 4 weeks ago, don’t use the week number at all!

    If you don’t feel like adding these entries, this problem is commonly solved by wrapping the list, so if your lookup index is less than zero, you can add the index value to the maximum index in the list.

    let currentWeek = DATEPART(WEEK, GETDATE())
    let startWeek = currentWeek – 4
    if startWeek < 0 then startWeek = 52 – startWeek

    In SQL that roughly translates to 2 separate criteria:

     WHERE
 (   
     week BETWEEN DATEPART(WEEK, GETDATE())-4 AND DATEPART(WEEK, GETDATE())
  OR week BETWEEN 52 + DATEPART(WEEK, GETDATE())-4 AND 52
 )

    this works due to the OR reference. When current week is 2, the first criteria will match week 1 and 2 (0 and -1 will not exist) but the second criteria will match 52-0 (52) and 52-1 (51). Weeks 53 and 54 are excluded by the hardcoded 52 as the upper boundary in the second BEWTEEN clause.

    I have enclosed these criteria within brackets so that you do not accidentally compose additional criteria over the top of this incorrectly.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search