SQLTeam.com | Weblogs | Forums

Current,previous and next customer information


#1

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


#2

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
:slight_smile:
:slight_smile:


#3

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


#4

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
:slight_smile:
:slight_smile:


#5

Thanks.

Based on your suggested solution please find my query below.

  1. It is working but, not exactly what I want.
  2. The previous customer is right
  3. The current customer is blank.
  4. The current customer should be rentref with ‘100020003/005’
  5. The Initial void date and current void date does not seem to work.
  6. 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


#6

Hi

I will work on it

Right now not able to access
My computer

Please give me
1 day
,


#7

Thanks. I appreciate


#8

Whats the difference between Initial_unrentvoiddate and current_unrentvoiddate.


#9

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


#10


#11

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


#12

Hi harishgg1

Thanks you so much. Much appreciated.

Cheers


#13

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

:slight_smile:
:slight_smile:


#14

Cheers. You are star!