Displaying current and previous pay period in SQL (based on an imported table)

Question

I need to display the current AND last pay periods based on an imported table for biweekly pay dates. The table looks something like this:

2017-03-23 00:00:00 2017-04-06 00:00:00
2017-04-07 00:00:00 2017-04-20 00:00:00
2017-04-21 00:00:00 2017-05-06 00:00:00
2017-05-07 00:00:00 2017-05-20 00:00:00
2017-05-21 00:00:00 2017-06-05 00:00:00
2017-06-06 00:00:00 2017-06-22 00:00:00
2017-06-23 00:00:00 2017-07-06 00:00:00
2017-07-07 00:00:00 2017-07-21 00:00:00

This code:

     SELECT * 
     FROM 
       table1
     WHERE  
       (start_date <= now()  AND end_date >= now())

Displays:

2017-06-06 00:00:00 2017-06-22 00:00:00

How can I display the previous pay period? Such as:

2017-05-21 00:00:00 2017-06-05 00:00:00

This code just doesn't even look right, but I tried it anyways:

SELECT start_date,
CASE 
    WHEN start_date = now() THEN    start_date = now() - 1
    WHEN end_date = now() THEN start_date = now() - 1
END
FROM table1;

I know there is a way to use CASE WHEN but for some reason, I cannot get the syntax right using now() since I want it to be dynamic and not a static date.

if no one will give you an answer i can probably write but later — RoMEoMusTDiE, Jun 08 '17 at 05:51
@maSTAShuFu Shouldn't there be an easier solution using CASE and the current timestamp using Date (now())? — codemonkey, Jun 08 '17 at 05:56
@maSTAShuFu Throw your code at me anyways. I'll compare to what others post afterwards. And please try to keep it simple since I'm learning. — codemonkey, Jun 08 '17 at 06:16

Elias · Answer 1 · 2017-06-08T09:04:47.523

0

 SELECT concat(start_date,'  ',
CASE
    WHEN start_date = now() THEN start_date = now() -  INTERVAL 1 DAY
WHEN end_date = now() THEN start_date =  now() - INTERVAL 1 DAY
END) AS PeriodOfPay
FROM table1;

edited Jun 08 '17 at 09:04

answered Jun 08 '17 at 05:58

Elias

664
2
11
23

Your code shows everything in the table (same as mine, above). I need the last pay period to be shown. 2017-05-21 00:00:00 2017-06-05 00:00:00 – codemonkey Jun 08 '17 at 06:05
I have edited the answer according to my understanding of your question. – Elias Jun 08 '17 at 08:53

score 0 · Answer 2 · answered Jun 08 '17 at 07:22

0

try this

To get the current and previous pay period

SELECT start_date,
end_date
FROM table1
where start_date < now()
ORDER BY start_date desc
LIMIT 2;

answered Jun 08 '17 at 07:22

RoMEoMusTDiE

4,739
1
17
26

Displaying current and previous pay period in SQL (based on an imported table)

2 Answers2