How to get the desired data with SQL

Transact-SQL
1

BEGIN TRANSACTION;

/* Create a table called NAMES */
CREATE TABLE EVENTS(Id integer, DT date);

/* Create few records in this table */
INSERT INTO EVENTS VALUES(1,'01-01-2017');
INSERT INTO EVENTS VALUES(1,'02-01-2017');
INSERT INTO EVENTS VALUES(1,'03-01-2017');
INSERT INTO EVENTS VALUES(1,'04-01-2017');
INSERT INTO EVENTS VALUES(1,'05-01-2017');
INSERT INTO EVENTS VALUES(2,'01-01-2017');
INSERT INTO EVENTS VALUES(2,'02-01-2017');
INSERT INTO EVENTS VALUES(2,'03-01-2017');

CREATE TABLE LEVEL(Player_id integer,Level integer, DT date);

/* Create few records in this table */
INSERT INTO LEVEL VALUES(1,1,'01-01-2017');
INSERT INTO LEVEL VALUES(1,2,'03-01-2017');
INSERT INTO LEVEL VALUES(1,3,'05-01-2017');
INSERT INTO LEVEL VALUES(2,1,'01-01-2017');
INSERT INTO LEVEL VALUES(2,2,'03-01-2017');
COMMIT;

select * from EVENTS a left join LEVEL b on a.Id=b.Player_id and a.DT=b.DT

Basically whenever level of game will change a record will be pushed into LEVEL table with Date and Current level of game

OUTPUT of above query -

1|01-01-2017|1|1|01-01-2017
1|02-01-2017|||
1|03-01-2017|1|2|03-01-2017
1|04-01-2017|||
1|05-01-2017|1|3|05-01-2017
2|01-01-2017|2|1|01-01-2017
2|02-01-2017|||
2|03-01-2017|2|2|03-01-2017

Output i WANT :

1|01-01-2017|1|1|01-01-2017
1|02-01-2017|1|1|02-01-2017
1|03-01-2017|1|2|03-01-2017
1|04-01-2017|1|2|04-01-2017
1|05-01-2017|1|3|05-01-2017
2|01-01-2017|2|1|01-01-2017
2|02-01-2017|||1|02-01-2017
2|03-01-2017|2|2|03-01-2017

Can somebody help what logic should be used to get the desired output above with SQL

Let me know incase if you need more info

2

This:

select top(1) with ties
       e.id
      ,e.dt
      ,l.player_id
      ,l.level
      ,l.dt
  from [events] as e
       left outer join [level] as l
                    on l.player_id=e.id
                   and l.dt<=e.dt
 order by row_number() over(partition by e.id,e.dt
                                order by l.dt desc
                           )
;

will give you this:

id  dt          player_id  level  dt
1   2017-01-01  1          1      2017-01-01
1   2017-02-01  1          1      2017-01-01
1   2017-03-01  1          2      2017-03-01
1   2017-04-01  1          2      2017-03-01
1   2017-05-01  1          3      2017-05-01
2   2017-01-01  2          1      2017-01-01
2   2017-02-01  2          1      2017-01-01
2   2017-03-01  2          2      2017-03-01
1 Like
3

thank you #bitsmed

4

hi

i know this topic was 7 months ago

I have come up with a different solution
please provide feedback
:slight_smile:
:slight_smile:

drop create data 
use tempdb
go 

drop table events 
go 

CREATE TABLE EVENTS(Id integer, DT date);

INSERT INTO EVENTS VALUES(1,'01-01-2017');
INSERT INTO EVENTS VALUES(1,'02-01-2017');
INSERT INTO EVENTS VALUES(1,'03-01-2017');
INSERT INTO EVENTS VALUES(1,'04-01-2017');
INSERT INTO EVENTS VALUES(1,'05-01-2017');
INSERT INTO EVENTS VALUES(2,'01-01-2017');
INSERT INTO EVENTS VALUES(2,'02-01-2017');
INSERT INTO EVENTS VALUES(2,'03-01-2017');
go 

select * from events
go  

drop table level 
go 



CREATE TABLE LEVEL(Player_id integer,Level integer, DT date);

/* Create few records in this table */
INSERT INTO LEVEL VALUES(1,1,'01-01-2017');
INSERT INTO LEVEL VALUES(1,2,'03-01-2017');
INSERT INTO LEVEL VALUES(1,3,'05-01-2017');
INSERT INTO LEVEL VALUES(2,1,'01-01-2017');
INSERT INTO LEVEL VALUES(2,2,'03-01-2017');
go 

select * from level
go
SQL 
SELECT a.id, 
       a.dt, 
       b.player_id, 
       b.level, 
       CASE 
         WHEN b.dt IS NULL THEN a.dt 
         ELSE b.dt 
       END AS dt 
FROM   events a 
       LEFT JOIN level b 
              ON a.id = b.player_id 
                 AND a.dt = b.dt 

go
Result

image