SQLTeam.com | Weblogs | Forums

I need to see when, per USER_ID, when a change in the VALUE field takes place. I

Hi,
I have a database with the following fields:
ID
USER_ID
ENTRY_DATE
VALUE
The ID field is a unique key. The field VALUE has numeric values. A USER_ID may have any number of VALUE and ENTRY_DATE values entered. I need to see when, per USER_ID, when a change in the VALUE field takes place. Is thee a way to do so? Possibly with recursion? Any help would be greatly appreciated!

hi

i have created sample data ....

please click arrow to the left for DROP Create Sample Data
drop table #sampledata
go 

create table #sampledata
(
ID			int	identity(1,1)		not null ,
USER_ID		varchar(100)	              	 ,
ENTRY_DATE	date				             , 
VALUE		varchar(100)
)
go 

insert into #sampledata select 'harry','2019-09-10','gotcha'
insert into #sampledata select 'pam'  ,'2019-09-11','so ok'
insert into #sampledata select 'lisa' ,'2019-08-10','now what'

insert into #sampledata select 'harry','2019-09-07','soup'
insert into #sampledata select 'pam'  ,'2019-09-12','so ok'
insert into #sampledata select 'lisa' ,'2019-08-15','ok what'

insert into #sampledata select 'harry','2019-09-15','gotcha'
insert into #sampledata select 'pam'  ,'2019-09-06','12 now fine'
insert into #sampledata select 'lisa' ,'2019-08-01','ok what'

go 

select 'sample data ', * from #sampledata
go

hi

please find the SQL ... see if it meets what you are looking for

please click arrow to the left for SQL ...
select 
	'SQL Output'
	,case when 
				value <> lag(value) over(partition by USER_ID order by USER_ID, ENTRY_DATE ) 
		 then 
				entry_date 
		 else 
				null 
	end as 'Change in Value'
   , * 
from 
    #sampledata 
order by 
     USER_ID
    ,ENTRY_DATE

1 Like

I think that will work. i have further work to do but it looks good. Thank you very much!

If you need any more help

Please let me know

Would be happy to help

:+1::+1: