Current,previous and next customer information

ravin80 · January 3, 2019, 10:27am

Hi Guys,

Happy New Year. I am a beginner to SQL. I am having some challenging on how to indentify current, previous and next customer for a customer renters table. Please the table below. Any help is appreciated.

CREATE TABLE #temp([rentid] char NOT NULL
,fullName varchar(255)
,rentref varchar(30)
,memberid varchar(30)
,rentstartdate DATETIME
,rentenddate DATETIME
,unrentvoiddate DATETIME

)

INSERT INTO #temp(rentid, rentref, fullName,memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Ms XXXXXXXXX', '100020003/003', '1016819767', '2014-09-15 00:00:00','2015-11-15 00:00:00','2015-11-16 00:00:00.000'
INSERT INTO #temp(rentid, rentref,fullName, memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Mr YYYYYYYYY', '100020003/004', '1021242816', '2015-12-21 00:00:00','2017-05-28 00:00:00','2017-05-29 00:00:00.000'
INSERT INTO #temp(rentid, rentref,fullName, memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Dr TTTTTTTTT', '100020003/005', '1021475396', '2017-06-19 00:00:00','2017-07-23 00:00:00','2017-07-24 00:00:00.000'

Select * from #temp
--Problem:
--The table contains list of car renters history - current and previous owners.
-- In addition to the lists, I want to add extra 5 columns to identify the previous, current and next ---member customers and Initial_unrentvoiddate and Current_unrentvoiddate

CREATE TABLE #temp([rentid] char NOT NULL
,Customer varchar(255)
,rentref varchar(30)
,memberid varchar(30)
,rentstartdate DATETIME
,rentenddate DATETIME
,unrentvoiddate DATETIME
,CurrentCustomer varchar(255)
,NextCustomer varchar(255)
,PreviousCustomer varchar(255)
,Initial_unrentvoiddate DATETIME
,Current_unrentvoiddate DATETIME
)

Thanking you in advance.

Ravin

harishgg1 · January 3, 2019, 12:08pm

please see LEAD LAG functions
from SQL SERVER 2012
lead is next value
lag is previous value

https://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/

if you are really NEW
somebody here will do the SQL
i have sql server 2008

ravin80 · January 3, 2019, 12:22pm

Thanks. But I am using sql server 2008 unfortunately, no luck. The dates are not coming out right. Any help will be useful

harishgg1 · January 3, 2019, 12:28pm

you can create row number ( rn ) on the table

then join

sametable a join sametable b on a.rn-1 = b.rn
join sametable c on a.rn+1 = c.rn

if you use a.columnname it is previous value
if you use b.columnname it is current value
if you use c.columname it is next value

try it
hope it works
i am on 2008
i can do the SQL if needed

ravin80 · January 3, 2019, 3:19pm

Thanks.

Based on your suggested solution please find my query below.

It is working but, not exactly what I want.
The previous customer is right
The current customer is blank.
The current customer should be rentref with ‘100020003/005’
The Initial void date and current void date does not seem to work.
In a nutshell, I want to have the current and the Initial void date per group; and then follow by previous, current and next rents which is defined by membered.
Thanks in advance.

if object_id('tempdb..#temp') > 0 drop table #temp
go
CREATE TABLE #temp([rentid] char NOT NULL
,fullName varchar(255)
,rentref varchar(30)
,memberid varchar(30)
,rentstartdate DATETIME
,rentenddate DATETIME
,unrentvoiddate DATETIME

)

INSERT INTO #temp(rentid, fullName,rentref,memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Ms XXXXXXXXX', '100020003/003', '1016819767', '2014-09-15 00:00:00','2015-11-15 00:00:00','2015-11-16 00:00:00.000'
INSERT INTO #temp(rentid,fullName,rentref, memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Mr YYYYYYYYY', '100020003/004', '1021242816', '2015-12-21 00:00:00','2017-05-28 00:00:00','2017-05-29 00:00:00.000'
INSERT INTO #temp(rentid, fullName, rentref,memberid, rentstartdate,rentenddate,unrentvoiddate ) SELECT '100020003','Dr TTTTTTTTT', '100020003/005', '1021475396', '2017-06-19 00:00:00','2017-07-23 00:00:00','2017-07-24 00:00:00.000'

;WITH CTE_T AS(
Select t.*
,rn = ROW_NUMBER() OVER (ORDER BY rentid)

from #temp as t
)

Select a.rentid as rentid
,a.rentref as prev_rentref
,a.FullName as prev_Customer
,a.rentstartdate as prev_rentstart
,a.rentenddate as prev_rententdate
,a.unrentvoiddate as InitialVoidDate

,b.rentid as cur_rentid
,b.FullName as cur_Customer
,b.rentref as cur_rentref
,b.rentstartdate as cur_rententdate
,b.rentenddate as cur_rententdate
,b.unrentvoiddate as curVoidDate

,c.rentid as nex_rentid
,c.FullName as nex_Customer
,c.rentref as nex_rentref
,c.rentstartdate as nex_rententdate
,c.rentenddate as nex_rententdate
,c.unrentvoiddate as curVoidDate2
from CTE_T as a
left join CTE_T as b on a.rn-1 = b.rn
left join CTE_T as c on a.rn+1 = c.rn

--where a.rn = 1

harishgg1 · January 3, 2019, 4:21pm

Hi

I will work on it

Right now not able to access
My computer

Please give me
1 day
,

ravin80 · January 3, 2019, 4:34pm

Thanks. I appreciate

mannesravya · January 3, 2019, 6:45pm

Whats the difference between Initial_unrentvoiddate and current_unrentvoiddate.

mannesravya · January 3, 2019, 6:54pm

when i running the above script in my system i am getting error as string or binary data would be truncated.
Could you please let me know how to slove this error

mannesravya · January 3, 2019, 6:57pm

harishgg1 · January 4, 2019, 5:25am

hi

I tried it

Here is the sample data YOU gave

drop create sample data

USE tempdb 

go 

DROP TABLE #temp 

go 

CREATE TABLE #temp 
  ( 
     [rentid]       CHAR(30) NOT NULL, 
     fullname       VARCHAR(255), 
     rentref        VARCHAR(30), 
     memberid       VARCHAR(30), 
     rentstartdate  DATETIME, 
     rentenddate    DATETIME, 
     unrentvoiddate DATETIME 
  ) 

go 

INSERT INTO #temp 
            (rentid, 
             fullname, 
             rentref, 
             memberid, 
             rentstartdate, 
             rentenddate, 
             unrentvoiddate) 
SELECT '100020003', 
       'Ms XXXXXXXXX', 
       '100020003/003', 
       '1016819767', 
       '2014-09-15 00:00:00', 
       '2015-11-15 00:00:00', 
       '2015-11-16 00:00:00.000' 

INSERT INTO #temp 
            (rentid, 
             fullname, 
             rentref, 
             memberid, 
             rentstartdate, 
             rentenddate, 
             unrentvoiddate) 
SELECT '100020003', 
       'Mr YYYYYYYYY', 
       '100020003/004', 
       '1021242816', 
       '2015-12-21 00:00:00', 
       '2017-05-28 00:00:00', 
       '2017-05-29 00:00:00.000' 

INSERT INTO #temp 
            (rentid, 
             fullname, 
             rentref, 
             memberid, 
             rentstartdate, 
             rentenddate, 
             unrentvoiddate) 
SELECT '100020003', 
       'Dr TTTTTTTTT', 
       '100020003/005', 
       '1021475396', 
       '2017-06-19 00:00:00', 
       '2017-07-23 00:00:00', 
       '2017-07-24 00:00:00.000' 

go 

SELECT * 
FROM   #temp 

go

Here is my SQL .. i am just using fullname column

SQL

 ;WITH cte 
     AS (SELECT rownum = Row_number() 
                           OVER ( 
                             ORDER BY rentid), 
                t.* 
         FROM   #temp AS t) 
SELECT prev.fullname PreviousValue, 
       cte.fullname, 
       nex.fullname  NextValue 
FROM   cte 
       LEFT JOIN cte prev 
              ON prev.rownum = cte.rownum - 1 
       LEFT JOIN cte nex 
              ON nex.rownum = cte.rownum + 1

Result

ravin80 · January 4, 2019, 2:46pm

Hi harishgg1

Thanks you so much. Much appreciated.

Cheers

harishgg1 · January 4, 2019, 5:24pm

Njoy

Been in your situation MANY times

for me .. nobody there to help
somehow find solution .. all night no sleep .. no food
ETC ETC ETC ETC

ravin80 · January 4, 2019, 5:25pm

Cheers. You are star!