Getting subsequent records from same table - help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting subsequent records from same table - help!

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-02-02 : 10:39:47

Hi

I'm very stuck with this, I nearly had it but my query was a mess and realised I'm probably doing it wrong. I was trying to use inline views, then sub-query etc. Still can't get quite what I want.

So for example take a personID where they are registered, processed and deregistered. They can have one or more registrations, one or more related processes (for each registration) and then deregistration, the processes are always tied to a registration and deregistration period.

for a given time-period I want to find the first registration and use that first registration to find subsequent registration records within 20 days - I don't want to return processes related to the next registration record, just the next registration record.

I already have the query built that returns all the raw data for those registrations, processes and deregistrations but need to set up something that finds subsequent registrations. So here is a sample of raw data:

Person Id    RowID      Reg_Date    Reg_Time   Process_First_Date   Process_First_Time   Process_last_Date   Process_Last_Time   De_Reg_Date   De_Reg_Time  
       =========    =======    ========    ========   ==================   ==================   =================   =================   ===========   ===========
       000000001    00001/8    01/01/2014  12:00      01/01/2014           12:00                02/01/2014          11:40               03/01/2014    16:44 
       000000001    00001/10   01/01/2014  12:00      02/01/2014           13:22                03/01/2014          09:56               03/01/2014    16:44
       000000001    00002/12   04/01/2014  10:36      04/01/2014           10:36                05/01/2014          15:12               08/01/2014    12:33
       000000001    00002/16   04/01/2014  10:36      06/01/2014           12:42                08/01/2014          11:03               08/01/2014    12:33
       000000002    00003/9    01/01/2014  12:00      01/01/2014           12:00                02/01/2014          11:40               03/01/2014    16:44 
       000000002    00003/11   01/01/2014  12:00      02/01/2014           13:22                03/01/2014          09:56               03/01/2014    16:44
       000000002    00004/13   04/01/2014  10:36      04/01/2014           10:36                05/01/2014          15:12               08/01/2014    12:33
       000000002    00004/22   04/01/2014  10:36      06/01/2014           12:42                08/01/2014          11:03               08/01/2014    12:33

firstly need to use all first registrations - that would be row where reg-date = process first date and also reg time and process first time are equal - and use those for finding the next reg_date of that personID. PersonID can have more than one registration in a period so need to do something like partition, which although it worked on it's own, seemed to become a tangled mess when I started using the subset in a inline view or sub-query. Not sure how to return earliest reg date in period for certain PersonID for multiple IDs.

So for above I would want to return these rows:

Person Id    RowID      Reg_Date    Reg_Time   Process_First_Date   Process_First_Time   Process_last_Date   Process_Last_Time   De_Reg_Date   De_Reg_Time  
       =========    =======    ========    ========   ==================   ==================   =================   =================   ===========   ===========
       000000001    00002/12   04/01/2014  10:36      04/01/2014           10:36                05/01/2014          15:12               08/01/2014    12:33
       000000002    00004/13   04/01/2014  10:36      04/01/2014           10:36                05/01/2014          15:12               08/01/2014    12:33

And this is based on these two rows:

Person Id    RowID      Reg_Date    Reg_Time   Process_First_Date   Process_First_Time   Process_last_Date   Process_Last_Time   De_Reg_Date   De_Reg_Time  
       =========    =======    ========    ========   ==================   ==================   =================   =================   ===========   ===========
       000000001    00001/8    01/01/2014  12:00      01/01/2014           12:00                02/01/2014          11:40               03/01/2014    16:44
       000000002    00003/9    01/01/2014  12:00      01/01/2014           12:00                02/01/2014          11:40               03/01/2014    16:44

I have made the dates the same in last 4 rows, this isn't intentional and doesn't mean anything it's because I copied and pasted the first 4 rows.

Any help how to tie this together using first reg of each personID and then comparing with other records to find subsequent first registrations would be really appreciated. I'm not too fussed about finding them within a certain time period from the first registration, more that I want to just get some sort of query set up then I can put in specifics.

Many thanks.

G

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-02 : 13:22:32

I built a solution (below), but the results do not match your desired output, probably due to lack of understanding of the requirements. e.g. Person 1 initially registered on 1/1/2014 at 12:00 twice, so two rows match for person 1, same with person 2. Anyway, this might help:


declare @t table(Id int,    RowID varchar(20),      Reg_Date    date,	Reg_Time  TIME,  Process_First_Date Date,  Process_First_Time Time,  Process_last_Date  Date,  Process_Last_Time Time,  De_Reg_Date Date,  De_Reg_Time  Time)

insert into @t(Id ,   RowID     , Reg_Date  ,  Reg_Time ,  Process_First_Date ,  Process_First_Time  , Process_last_Date ,  Process_Last_Time,   De_Reg_Date ,  De_Reg_Time)

--Person Id    RowID      Reg_Date    Reg_Time   Process_First_Date   Process_First_Time   Process_last_Date   Process_Last_Time   De_Reg_Date   De_Reg_Time  
--       =========    =======    ========    ========   ==================   ==================   =================   =================   ===========   ===========
values

      (000000001,'  00001/8 ',' 01/01/2014','12:00','  01/01/2014  ','  12:00 ','  02/01/2014 ',' 11:40 ','  03/01/2014','16:44 '),
      (000000001,'  00001/10',' 01/01/2014','12:00','  02/01/2014  ','  13:22 ','  03/01/2014 ',' 09:56 ','  03/01/2014','16:44	'),
      (000000001,'  00002/12',' 04/01/2014','10:36','  04/01/2014  ','  10:36 ','  05/01/2014 ',' 15:12 ','  08/01/2014','12:33	'),
      (000000001,'  00002/16',' 04/01/2014','10:36','  06/01/2014  ','  12:42 ','  08/01/2014 ',' 11:03 ','  08/01/2014','12:33	'),
      (000000002,'  00003/9 ',' 01/01/2014','12:00','  01/01/2014  ','  12:00 ','  02/01/2014 ',' 11:40 ','  03/01/2014','16:44 '),
      (000000002,'  00003/11',' 01/01/2014','12:00','  02/01/2014  ','  13:22 ','  03/01/2014 ',' 09:56 ','  03/01/2014','16:44	'),
      (000000002,'  00004/13',' 04/01/2014','10:36','  04/01/2014  ','  10:36 ','  05/01/2014 ',' 15:12 ','  08/01/2014','12:33	'),
      (000000002,'  00004/22',' 04/01/2014','10:36','  06/01/2014  ','  12:42 ','  08/01/2014 ',' 11:03 ','  08/01/2014','12:33	')
;


with cte as (
	select dateadd(minute, datepart(minute, reg_time), dateadd(hour, datepart(hour, reg_time), cast(reg_date as datetime))) as reg_datetime
	     , dateadd(minute, datepart(minute, Process_First_Time), dateadd(hour, datepart(hour, Process_First_Time), cast(Process_First_Date as datetime))) as Process_First_datetime
		 , dateadd(minute, datepart(minute, Process_Last_Time), dateadd(hour, datepart(hour, Process_Last_Time), cast(Process_last_Date as datetime))) as Process_last_Date_datetime
	     , *
	from @t
),
firstreg as (
	select id, min(reg_datetime) reg_datetime
	from cte
	where reg_datetime = process_first_datetime
	group by id
	
)

select * from cte c
join firstreg f
  on c.id = f.id
and c.reg_datetime = f.reg_datetime

union all

select * from cte c
join firstreg f
  on c.id = f.id
where c.reg_datetime <= f.reg_datetime + 20
  and c.reg_datetime > f.reg_datetime

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-02-03 : 03:38:10

quote:
I built a solution (below), but the results do not match your desired output, probably due to lack of understanding of the requirements. e.g. Person 1 initially registered on 1/1/2014 at 12:00 twice, so two rows match for person 1, same with person 2. Anyway, this might help:

That's right the reg date of the first process will show on each process row, so if the person had 5 processes then 5 rows will show same reg date and reg time but process date and time will be different on the 2nd to 5th row.

The 5th row process end date/tim will match the deregistration date/time.

Notice on the first row for the personID the reg date and time match the process date and time, this is how to know it is the first registration/process. It is required to match reg date/time with process start date/time to get first registrations of person, and this is one of the data sets I was using in my query.

Initially I was using this data set to try and find other first registrations for the same person within a certain time period but do not want to pick up 2nd/3rd processes for the person from the outer query, just the next first registration/process.

Hope that makes sense?

G

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 07:10:38

I think it makes sense, and I think you can use my solution as a starting point to do what you need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources