-2

I have this type of data in my source tables.

site              date_time        data
1016771     2016-06-04 00:00:00    LBV
1016771     2016-06-04 00:00:00     SOB
1016771     2016-06-04 01:29:00     LBV
1016771     2016-06-04 01:29:00     SOB
1016771     2016-06-04 05:51:00     SOB
1016771     2016-06-04 05:51:00     LBV
1016771     2016-06-04 08:35:00     SOB
1016771     2016-06-04 08:35:00     LBV
1016771     2016-06-04 11:18:00     LBV
1016771     2016-06-04 11:18:00     SOB
1016771     2016-06-04 12:20:00     LBV
1016771     2016-06-04 12:20:00     SOB
1016771     2016-06-04 13:24:00     LBV
1016771     2016-06-04 13:24:00     SOB
1016771     2016-06-04 14:04:00     LBV
1016771     2016-06-04 14:04:00     SOB
1016771     2016-06-04 15:27:00     LBV
1016773     2016-06-04 15:27:00     SOB
1016773     2016-06-04 16:55:00     LBV
1016773     2016-06-04 16:55:00     SOB
1016773     2016-06-04 18:15:00     LBV
1016773     2016-06-04 18:15:00     SOB

Now i want to perform some operation on this data model.I want next row data in same row. Output is like this:

site            SOB_date_time             LBV_date_time 
1016771     2016-06-04 00:00:00         2016-06-04 01:29:00    
1016771     2016-06-04 05:51:00         2016-06-04 05:51:00 
1016771     2016-06-04 08:35:00         2016-06-04 08:35:00     
1016771     2016-06-04 11:18:00         2016-06-04 12:20:00    
1016771     2016-06-04 12:20:00         2016-06-04 13:24:00     
1016771     2016-06-04 13:24:00         2016-06-04 14:04:00    
1016771     2016-06-04 14:04:00         2016-06-04 15:27:00 
1016773     2016-06-04 15:27:00         2016-06-04 16:55:00 
1016773     2016-06-04 16:55:00         2016-06-04 18:15:00

Can u please help me to solve this problem.

Bishan Singh
  • 1
  • 2

1 Answers1

2

If you got only 2 different datas you can use OUTER APPLY:

SELECT  y.[site],   
        y.date_time as SOB_date_time,
        t.date_time as LBV_date_time
FROM YourTable y
OUTER APPLY (
    SELECT TOP 1 *
    FROM YourTable
    WHERE y.[site] = [site]
        AND y.date_time < date_time
        AND [data] = 'LBV'
    ORDER BY date_time
) as t
WHERE y.[data] = 'SOB'

Output:

site    SOB_date_time       LBV_date_time
1016771 2016-06-04 00:00:00 2016-06-04 01:29:00
1016771 2016-06-04 01:29:00 2016-06-04 05:51:00
1016771 2016-06-04 05:51:00 2016-06-04 08:35:00
1016771 2016-06-04 08:35:00 2016-06-04 11:18:00
1016771 2016-06-04 11:18:00 2016-06-04 12:20:00
1016771 2016-06-04 12:20:00 2016-06-04 13:24:00
1016771 2016-06-04 13:24:00 2016-06-04 14:04:00
1016771 2016-06-04 14:04:00 2016-06-04 15:27:00
1016773 2016-06-04 15:27:00 2016-06-04 16:55:00
1016773 2016-06-04 16:55:00 2016-06-04 18:15:00
1016773 2016-06-04 18:15:00 NULL

One more way with TOP 1 WITH TIES and ROW_NUMBER():

SELECT  TOP 1 WITH TIES 
        y.[site],   
        y.date_time as SOB_date_time,
        y1.date_time as LBV_date_time
FROM YourTable y
LEFT JOIN YourTable y1
    ON y.[site] = y1.[site] and y1.[data] = 'LBV' AND y.date_time < y1.date_time
WHERE y.[data] = 'SOB'
ORDER BY ROW_NUMBER() OVER (PARTITION BY y.[site],y.date_time ORDER BY   y1.date_time)
Bishan Singh
  • 1
  • 2
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • The main problem to get EXACTLY same output that you want - you need some id field that represent the order of rows. – gofr1 Oct 03 '16 at 07:39
  • I m performing the query in vertica software. OUTER APPLY is not working in the vertica .Is there any alternate solution.Can we solve this using lead and lag analytical function? – Bishan Singh Oct 03 '16 at 09:45
  • I add solution with TOP 1 WITH TIES and ordering, should give same result – gofr1 Oct 03 '16 at 09:57
  • Don't know why you removed LEAD, but that is ok. Hope, your problem is solved. – gofr1 Oct 03 '16 at 10:43
  • solution with Lead function. getting wrong output.You also getting wrong output. – Bishan Singh Oct 03 '16 at 10:54
  • i want sob date_time after that LBV date_time just after sob date_time. – Bishan Singh Oct 03 '16 at 10:57
  • As I already said, to get exactly the same output as in your question you need some field that will identify the order of rows, as for now - there is no such field. – gofr1 Oct 03 '16 at 10:59
  • i want output which satisfy the following condition. 1:pick only those sob details which came just before LBV. 2:pick only those LBV details which came just after SOB. 3:SOB date_time can be equal to LBV or less than LBV but should not be greater than LBV. – Bishan Singh Oct 03 '16 at 11:08
  • date_time is one column, which can identify the order of rows. – Bishan Singh Oct 03 '16 at 11:10
  • No it can't, if there is two same `date_time`s and `site`s with different `data` how SQL Server will define which is first and which is second? – gofr1 Oct 03 '16 at 11:17
  • we will not consider those records where date_time is equal for both sob and LBV. its fine for me if i get all records where sob >lbv and lbv should be just after the sob. – Bishan Singh Oct 03 '16 at 11:22
  • Then any of two solutions I gave in my answer should help you. – gofr1 Oct 03 '16 at 11:24
  • but it will miss some records. – Bishan Singh Oct 03 '16 at 11:34
  • Look, there is 2 variants to add unique id for each row, the first is using temp table with `IDENTITY(1,1)` column (just create one and insert data from your table, then work with this table), the second using `ROW_NUMBER() OVER (ORDER BY (SELECT NULL))`. Both of them DON'T GAVE 100% GRANTEE that the row order will be as it is in your sample. – gofr1 Oct 03 '16 at 11:46