last 2 months Different

Navigation
0

I have a registration list for each SKU; then for each SKU I would like to be able to obtain the prices of the last 2 months Different (Months registered) (and that I can recognize between annual changes like December 2016 and January 2017) the problem that there is a jump between months.

the table has the following scheme 

id  sku     precio  fecha Actualizacion
1   10101   7.40    2016-12-23 11:58:05
2   10102   7.35    2016-12-23 11:58:06
3   10103   7.70    2016-12-23 11:58:07
4   10101   7.40    2017-02-15 11:58:05
5   10102   7.35    2017-02-15 11:58:06
6   10103   7.70    2017-02-15 11:58:07
7   10101   7.50    2017-05-06 11:58:11
8   10102   7.55    2017-05-06 11:58:12
9   10103   7.90    2017-05-06 11:58:05

I was trying to adapt this example: 

SELECT id, thread_id, user_id, subject, body, date_sent
  FROM messages WHERE date_sent IN (
    SELECT MAX( date_sent )
      FROM messages WHERE user_id =6 GROUP BY thread_id
  )
ORDER BY thread_id ASC , date_sent DESC;

Update:

Manipulating something like this, but I have to prevent you from taking 2 records of the same month: 

SELECT * 
FROM tabla
WHERE 
    (YEAR('Date_Rel') <= NOW() + INTERVAL 1 YEAR)
    AND 
    (MONTH('Date_Rel') <= NOW() + INTERVAL 1 MONTH)
    ORDER 
    BY 'Date_Rel' DESC;
    
asked by Francisco Núñez 23.10.2017 в 19:33
source

2 answers

1

If I did not understand you badly, what you are looking for is to obtain a price per month for the last two months of each sku. That is, you should have a maximum of two records per sku. I give you an example of how you could solve it:

First of all we create a table with your example 

create table Ejemplo (
    id      int,
    sku     varchar(15),
    precio  numeric(15,2),
    fecha   datetime
);  

insert into Ejemplo(id, sku, precio, fecha)
  values (1,'10101',7.40,'2016-12-23 11:58:05'),
         (2,'10102',7.35,'2016-12-23 11:58:06'),
         (3,'10103',7.70,'2016-12-23 11:58:07'),
         (4,'10101',7.40,'2017-02-15 11:58:05'),
         (5,'10102',7.35,'2017-02-15 11:58:06'),
         (6,'10103',7.70,'2017-02-15 11:58:07'),
         (7,'10101',7.50,'2017-05-06 11:58:11'),
         (8,'10102',7.55,'2017-05-06 11:58:12'),
         (9,'10103',7.90,'2017-05-06 11:58:05');

Then the query itself: 

Select   e.*
         from Ejemplo e
         INNER JOIN (select e1.sku,
                            @rownum := IF(e1.sku=@sku, @rownum+1, 1) AS RowNumber,
                            @sku := e1.sku,
                            e1.fecha
                            FROM ( select t.sku, 
                                           year(t.fecha) * 100 + month(t.fecha) as Periodo,
                                           max(t.fecha) as fecha
                                           from Ejemplo t
                                           group by t.sku, year(t.fecha) * 100 + month(t.fecha)
                                           order by t.sku, year(t.fecha) * 100 + month(t.fecha) desc
                                  ) e1
                                  cross join (SELECT @sku:=(SELECT MIN(sku) FROM Ejemplo), @rownum:=0) AS init 
                     ) rn
                     on rn.sku = e.sku
                     and rn.fecha = e.fecha
                     and rn.RowNumber <= 2
          order by e.sku,
                   e.fecha desc;

Explanation

  • First, we make a query grouped by sku and period (year / month) and with the last date ( MAX ) of each of these groups
  • Then we number each of these records by group
  • And finally we recover all the sku whose date coincides with at least the last two records of each period.

If, on the other hand, you want a single row for sku 

Select   e.sku,
         max(case when rn.RowNumber = 1 then e.fecha else null end)  as 'fecha1',
         max(case when rn.RowNumber = 1 then e.precio else null end) as 'precio1',
         max(case when rn.RowNumber = 2 then e.fecha else null end)  as 'fecha2',
         max(case when rn.RowNumber = 2 then e.precio else null end) as 'precio2'
         from Ejemplo e
         INNER JOIN (select e1.sku,
                            @rownum := IF(e1.sku=@sku, @rownum+1, 1) AS RowNumber,
                            @sku := e1.sku,
                            e1.fecha
                            FROM ( select t.sku, 
                                           year(t.fecha) * 1000 + month(t.fecha) as Periodo,
                                           max(t.fecha) as fecha
                                           from Ejemplo t
                                           group by t.sku, year(t.fecha) * 1000 + month(t.fecha)
                                           order by t.sku, year(t.fecha) * 1000 + month(t.fecha) desc
                                  ) e1
                                  cross join (SELECT @sku:=(SELECT MIN(sku) FROM Ejemplo), @rownum:=0) AS init 
                     ) rn
                     on rn.sku = e.sku
                     and rn.fecha = e.fecha
                     and rn.RowNumber <= 2
          group by e.sku           
          order by e.sku;
    
answered by 23.10.2017 / 22:28
source
0

I'm not clear about the name of the table or the fields but this query should return what you request.

SELECT sku, price FROM table WHERE date_date BETWEEN DATE_SUB (now (), INTERVAL 2 MONTH) AND now ()

This will return all the prices registered two months from now.

    
answered by 23.10.2017 в 20:08
array using explode [closed] Alternative to kbhit