Query record last week

Databases
1

@r937 @DaveMaxwell
Hi can I ask some help how can I query records from previous week. ?
example if I have customer that will pay every Monday, how can I make the query to check the transaction from tuesday to sunday of the previous week ?

Thank you in advance.

2

WHERE somedatecol BETWEEN CURRENT_DATE - INTERVAL 6 DAY AND CURRENT_DATE - INTERVAL 1 DAY

3

Thank you so much.

4

Hi r937,

How can I filter on this to execute only if the CURRENT_DATE is equal to dateofpayment of customer,
example if today is “Monday” then I will get all transaction records of customers whose dateofpayment is monday.

customer_table

id   custname     dateofpayment  
24  John            Monday
25  Lisa            Tuesday
26  Marissa         Friday 
27  Eric             Monday

transaction_table

trans_id   cust_id     trans_date
1             24            2015-09-04 03:30:00

2             24            2015-09-05 03:30:00

3             24            2015-09-03 05:30:00

4             25            2015-09-04 03:30:00

5             25            2015-09-02 08:30:00

6             26            2015-09-04 03:30:00

7             27            2015-09-03 10:30:00

Thank you in advance

5

SELECT c.id , c.custname , t.transid , t.trans_date FROM customers AS c INNER JOIN transactions AS t ON t.cust_id = c.id AND t.trans_date BETWEEN CURRENT_DATE - INTERVAL 6 DAY AND CURRENT_DATE WHERE c.dateofpayment = DAYNAME(CURRENT_DATE)

6

Thank you it works :smile:

7

I do not know how to use “INNER JOIN” in any case.

hope you can explain to me. Tks a lot !!!

8

google it… there are literally hundreds of good tutorials out there

1 Like
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.