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

Transact-SQL
1

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!

3

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

image
image840×472 134 KB

4

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

image
image1018×458 171 KB

1 Like
5

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

6

If you need any more help

Please let me know

Would be happy to help

:+1::+1: