[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

SQL 2008 Find Previous Record within same table for all rows

I am attempting to find a service record with part of the previous record on the same line of an SQL query.  This would be for ALL records not a specific record. All data is coming from a single table.


Original Data:
SVCRECORD      CARNMBR      Date      MILES
700375      103      12/31/2008      783
705907      103      2/17/2009      826
706420      103      2/18/2009      826
717787      121      5/29/2009      393
732032      121      10/16/2009      402
781194      121      12/30/2010      424

Desired Data:
SVCRECORD      CARNMBR      Date      MILES      PrevDT      PrevMiles
700375      103      12/31/2008      783      Null      Null
705907      103      2/17/2009      826      12/31/2008      783
706420      103      2/18/2009      826      2/17/2009      826
717787      121      5/29/2009      393      Null      Null
732032      121      10/16/2009      402      5/29/2009      393
781194      121      12/30/2010      424      10/16/2009      402
Please let me know if you need clarification. - Thank you in advance!
0
kgadams
Asked:
kgadams
  • 9
  • 5
  • 2
2 Solutions
 
jorgedeoliveiraborgesCommented:
Did you try to use a non-equal self join?
0
 
kgadamsAuthor Commented:
No. I am fairly new to SQL (about 4 months)
0
 
jorgedeoliveiraborgesCommented:
I am going to to the SQL.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
jorgedeoliveiraborgesCommented:
Try this, please.

select t1.SVCRECORD,
       t1.CARNMBR,
       t1.[Date],
       t1.MILES,
       t2.[data] as PrevDT,
       t2.MILES  as PrevMiles       
from Yourtable t1
     inner join  YourTable t2 on (t2.SVCRECORD <= t1.SVCRECORD)
where 1 = 1 
  and t2.SVCRECORD in (
                       select top 1 a3.SVCRECORD 
                         from YourTable t3
                        where t3.SVCRECORD <= a1.SVCRECORD
                        order by a3.SVCRECORD  desc
                      )
order by t1.SVCRECORD asc;

Open in new window

0
 
jorgedeoliveiraborgesCommented:
Please change: where t3.SVCRECORD <= a1.SVCRECORD
by where t3.SVCRECORD < a1.SVCRECORD



0
 
kgadamsAuthor Commented:
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "a1.SVCRECORD
" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "a3.SVCRECORD
" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "a3.CSVCRECORD
" could not be bound.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'data'.

Assuming it should be t1 and t3 vs. a1 and a3 and the column name should be date vs data, I get the following.

SVCRECORD      CARNMBR      Date      MILES      PrevDT      PrevMILES
700375      103      2008-12-31 00:00:00.000      783968      2008-12-31 00:00:00.000      783968
705907      103      2009-02-17 00:00:00.000      826078      2009-02-17 00:00:00.000      826078
706420      103      2009-02-18 00:00:00.000      826663      2009-02-18 00:00:00.000      826663
717928      103      2009-06-01 00:00:00.000      851557      2009-06-01 00:00:00.000      851557
725413      103      2009-08-14 00:00:00.000      864093      2009-08-14 00:00:00.000      864093
749998      103      2010-03-23 00:00:00.000      897000      2010-03-23 00:00:00.000      897000
758084      103      2010-06-02 00:00:00.000      908803      2010-06-02 00:00:00.000      908803

It is just showing the line with the same miles not the previous miles??  Did I miss something from your post?
0
 
kgadamsAuthor Commented:
"Please change: where t3.SVCRECORD <= a1.SVCRECORD
by where t3.SVCRECORD < a1.SVCRECORD"

When I attempt this revision (still assuming a1 & a3 should be t1 & t3)

Using your edit to remove the <= and replace with < I get the following results:
SVCRECORD      CARNMBR      Date      MILES      PrevDT      PrevMILES
700375      103      2008-12-31       783968      2008-12-31       52147
705907      103      2009-02-17       826078      2009-02-16       127489
706420      103      2009-02-18       826663      2009-02-18       738179

0
 
kgadamsAuthor Commented:
I have cracked it with your help!  Please see my edits below and make sure I have not committed an epic blunder.  It does not show an initial record with a NULL value for the previous but I can live with that...

select t1.SVCRECORD,
       t1.CARNMBR,
       t1.Date,
       t1.Miles,
       t2.[date] as PrevDT,
       t2.Miles  as PrevMiles      
from SCReports t1
     inner join  SCReports t2 on (t2.SVCRECORD <= t1.SVCRECORD)
where 1 = 1 and t1.CARNMBR = '103'
  and t2.SVCRECORD in (
                       select top 1 t3.SVCRECORD
                         from SCReports t3
                        where t3.SVCRECORD < t1.SVCRECORD and t3.CARNMBR = t1.CARNMBR
                        order by t3.SVCRECORD  desc
                      )

order by t1.SVCRECORD asc;
0
 
Scott PletcherSenior DBACommented:

;WITH CTE AS (
    SELECT SVCRECORD,
       CARNMBR,
       Date,
       Miles,
       ROW_NUMBER() OVER (PARTITION BY CARNMBR ORDER BY SVCRECORD) AS row_num
    FROM SCReports
    --WHERE CARNMBR = '103'
)
SELECT t1.SVCRECORD,
   t1.CARNMBR,
   t1.Date,
   t1.Miles,
   t2.PrevDT,
   t2.PrevMiles
FROM CTE t1
LEFT OUTER JOIN CTE t2 ON
    t2.CARNMBR = t1.CARNMBR AND
    t2.row_num = t1.row_num - 1
ORDER BY t1.SVCRECORD asc;
0
 
jorgedeoliveiraborgesCommented:
>> Assuming it should be t1 and t3 vs. a1 and a3 and the column name should be date vs data, I get the following.
That right. Thank you.
0
 
jorgedeoliveiraborgesCommented:
>> It does not show an initial record with a NULL value for the previous
I see the point.
0
 
jorgedeoliveiraborgesCommented:
Please erase «1 = 1 and», as in:

select t1.SVCRECORD,
       t1.CARNMBR,
       t1.Date,
       t1.Miles,
       t2.[date] as PrevDT,
       t2.Miles  as PrevMiles       
from SCReports t1
     inner join  SCReports t2 on (t2.SVCRECORD <= t1.SVCRECORD) 
where t1.CARNMBR = '103'
  and t2.SVCRECORD in (
                       select top 1 t3.SVCRECORD 
                         from SCReports t3
                        where t3.SVCRECORD < t1.SVCRECORD and t3.CARNMBR = t1.CARNMBR
                        order by t3.SVCRECORD  desc
                      )
order by t1.SVCRECORD asc;

Open in new window

0
 
jorgedeoliveiraborgesCommented:
>> It does not show an initial record with a NULL value for the previous but I can live with that...
The T-SQL must show the first record.
0
 
kgadamsAuthor Commented:
Thank you both!
I awarded points based on the performance of the query.  Both return the values needed, but the one from Scott was Much faster.

Thanks to you both.
0
 
jorgedeoliveiraborgesCommented:
I used an another database to do the job.
The name of the database is pubs. (See instpubs.sql in order to install)

It does show the first record with a NULL value for the previous field.

use pubs
go


select a1.au_id,
case
    when a1.au_id = (select top 1 au_id from authors order by au_id) then null
    else a2.au_id
end as Previous_au_id 
from authors a1
     inner join  authors a2 on (a2.au_id <= a1.au_id)
where a2.au_id in (
                    select top 1 a3.au_id 
                      from authors a3
                     where a3.au_id < a1.au_id
                     order by a3.au_id desc
                  )
   or a1.au_id = (select top 1 au_id from authors order by au_id)
order by a1.au_id asc, 
         a2.au_id asc ;

return;

Open in new window

instpubs.sql
0
 
Scott PletcherSenior DBACommented:
Yep, better performance is why I offered it :-) (plus to me it's easier to read and debug).

I'm amazed at how fast SQL processes ROW_NUMBER() OVER queries ... it's truly impressive.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now