CODEWITHSUNDEEP
mysqlsqlsubquery
bbowler86
bbowler86

Reputation: 139

MySQL joining the previous occurrence of a record

I have the query that lists that dates that a store was open and closed. What I want is the previous open date so for example for 2014-12-27 it would be 2014-12-26. The store wasn't open on New Years so for 2015-01-02 it would be 2014-12-31.

Every time I sit down to write the query I get nowhere, I have tried subselects, case statements, joining subqueries, subqueries in the where clause and I still can't figure this one out. Any help on this would be much appreciated?

EDIT Clarifying that the column date_nk is an integer, and add the column "previous_date" to show the desired output. 

    date_nk         weekday     opened     previous_open_date
1.  20141226        Friday      1          20141225
2.  20141227        Saturday    0          20141226
3.  20141228        Sunday      0          20141226
4.  20141229        Monday      1          20141226 
5.  20141230        Tuesday     1          20141229
6.  20141231        Wednesday   1          20141230
7.  20150101        Thursday    0          20141231 
8.  20150102        Friday      1          20141231
9.  20150103        Saturday    0          20150102 
10. 20150104        Sunday      0          20150102

Upvotes: 1

Views: 53

Answers (2)

dcieslak
dcieslak

Reputation: 2715

Please check the below.

Example in SQL Fiddle

SELECT 
 date_nk, 
 weekday,
 opened,
 (select max(date_nk) from OpenTable PO
  where PO.date_nk < CU.date_nk and
       PO.Opened = 1) PrevDate
  from OpenTable CU
  order by date_nk;

Upvotes: 2

Gonzalo
Gonzalo

Reputation: 1876

It doesn't look difficult if I understod you.

SELECT * FROM table WHERE opened = '1' AND date_nk < 'yourdate' LIMIT 1;

I don't knwo the type of your date_nk field, you should think about use date types but I think that should work.

Upvotes: 1

Related Questions