April 28, 2016 at 10:03 am
im trying to work around a primary key based one a date time
i need to vary it but I've had some issues
i tried using the rownum but it doesn't seem to work entirely
instead of varying every rows, it varies every 3
What can I do differently to make it vary every row instead?
Here is the script:
IF OBJECT_ID('dbo.#t0', 'U') IS NOT NULL
drop table #t0
select distinct (select enc_nbr from patient_encounter where enc_id=ng.enc_id)as enc_nbr,
en.subject,
CONVERT(VARCHAR(MAX), textdata) as textdata,
ng.enc_id,
en.enc_id as 'related_enc_id',
en.person_id,
ep.enterprise_id,
en.create_timestamp,
en.practice_id into #t0
from encounter_notes en,NG_EDI_ELIGIBILITY ng,encounter_payer ep,payer_mstr pm--,#t1 t
where submit_ind='D'
and en.person_id=ng.person_id
and ep.enc_id=en.enc_id
and ep.payer_id = pm.payer_id
and pm.payer_name = ng.payer_name--(select payer_name from payer_mstr where payer_id=ep.payer_id)
and ((subject like '%deduct%' and en.textdata like '%$0%') or subject like '%Demo%' or subject like '%covera%' or subject like '%skill%' )
and en.create_timestamp >='20160410'--@date
--delete from #t0
--where not((subject like '%deduct%' and textdata like '%$0%') or subject like '%Demo%' or subject like '%covera%')
--select * from #t0
insert into encounter_notes
(practice_id, enc_id, create_timestamp, subject, textdata,description, person_id, delete_ind,created_by, modified_by,
modify_timestamp, lockout_user_id, lockout_ind, create_timestamp_tz, modify_timestamp_tz)
select distinct practice_id,enc_id,
--ROW_NUMBER() OVER(ORDER BY enc_id DESC),
--DATEADD (ms, ROW_NUMBER() OVER(ORDER BY enc_id DESC)+1000, GETDATE()),
DATEADD (ms, ABS(CHECKSUM(NewId())) % 100, GETDATE()),
subject,textdata,'',person_id,'N',0,0,GETDATE(),NULL,'N',NULL,NULL--select DATEADD (ms, ABS(CHECKSUM(NewId())) % 100, GETDATE()),*
from(
select distinct practice_id,enc_id,--ROW_NUMBER() OVER(ORDER BY enc_id DESC) AS Row,
subject,textdata,''as 'description',person_id,'N' as 'delete_ind',0 as 'created_by',0 as 'modified_by',GETDATE() as 'modify_timestamp',NULL as 'lockout_user_id','N' as 'lockout_ind',NULL as 'create_timestamp_tz',NULL as 'modify_timestamp_tz'--,enc_nbr
from #t0 t
where not exists(select enc_id from encounter_notes where enc_id=t.enc_id)
) te
April 28, 2016 at 11:05 am
First, please format your code (spacing and use the code=sql shortcut to the left of the editor). Second, we don't have your environment, so if there are tables needed to duplicate this, show the DDL and some sample data.
What is the PK you are trying to work around? What is the issue? You haven't described anything. What does vary every row mean?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply