Last 13 weeks of data with user choosing the week

Help
1

I am trying to get my data for the last 13 weeks and i have my sql as below

WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < 91) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) > -1)) THEN DATE(${TABLE}.created_at)

This works like a charm when i use the current week , but when I change the date to last week I only get 12 weeks of data and not 13 weeks of data .

If I change the number to 98 I get 13 weeks but when i change back to current week it gives me 14 weeks of data .

The Current week and last week is for a user input , so how can i make sure that i get 13 weeks irrespective of the week chosen by the user

2

Hello sri75

Sorry for the late response. Not sure if you had already figured out a solution. Here is one option for a more elegant and predictable results using MemSQL built-in date function.

memsql> SELECT (CASE WHEN TIMESTAMPDIFF(WEEK,‘2019/03/15’, ‘2019/11/15’) = 13 THEN ‘TRUE’ ELSE ‘FALSE’ END) AS GOOD_DATE;
±----------+
| GOOD_DATE |
±----------+
| FALSE |
±----------+
1 row in set (0.09 sec)

memsql> SELECT (CASE WHEN TIMESTAMPDIFF(WEEK,‘2019/08/15’, ‘2019/11/15’) = 13 THEN ‘TRUE’ ELSE ‘FALSE’ END) AS GOOD_DATE;
±----------+
| GOOD_DATE |
±----------+
| TRUE |
±----------+
1 row in set (0.00 sec)

Hope the above works for your use case. Do let us know about it.

Thanks for your patience
Ramesh

3

What does your query look like when you try to do last week?

4

Hello gmcclintock,
Since the TIMESTAMPDIFF function takes in 2 date parameters, you can pass any valid dates. So, does not really matter which week that date falls in. Am I understanding you correctly?

Thanks,