Update Query

[jeffsturgeon2002]

I have a report that takes one MD (my Source Doctor) and his/her Fee Schedule and it should copy and update the fee schedule to my other Doctor (my destination Doctor).

The report currently (as listed below) only inserts doesn't update on the feeschedule that is modified. I need to somehow update the existing feeschedule...

I can do that by itself... Update dffeeschedule set expiration = 'xx-xx-xxxx' where dffeescheduleid = yy and where expiration is null

That should work except that I don't know how to separate out the two!

Report Query:

declare @dest varchar(8000)
declare @src varchar(255)
declare @fs varchar(255)

select @src=(select listname from  doctorfacility where doctorfacilityid=7)
set @dest=''
select @fs=(select feeschedulename from feeschedule where feescheduleid=8)

DECLARE @curse_id varchar(255)
DECLARE curse CURSOR
FOR 
select listname from  doctorfacility where doctorfacilityid in (2)

OPEN curse
FETCH NEXT FROM curse 
INTO @curse_id
WHILE @@FETCH_STATUS = 0
BEGIN

set @dest=@dest+'; '+@curse_id

   FETCH NEXT FROM curse 
   INTO @curse_id
END
CLOSE curse
DEALLOCATE curse
set @dest=substring(@dest,2,len(@dest))
select @dest,@src, @fs

Update Query (used with the Report Query above):

create table #tmp2 (
DFFeeScheduleId		int,
FinancialClassMId	int,
DoctorFacilityId	int,
FacilityId		int,
CompanyId		int,
InsuranceCarriersId	int,
InsuranceGroupId	int,
FeeScheduleId		int,
EffectiveDate		datetime,
ExpirationDate		datetime,
ContractCode		varchar(30),
ContractVersionId	varchar(30),
Created			datetime,
CreatedBy		varchar(30),
LastModified		datetime,
LastModifiedBy		varchar(30))


-- stuff this into a temp table
select * into #tmp from dffeeschedule  where doctorfacilityid=7
and feescheduleid=8

-- update the temp dataset
update #tmp set created=getdate(),createdby= dbo.GetLogonId(),lastmodified=getdate(),lastmodifiedby= dbo.GetLogonId()

-- do the update
DECLARE @curse_id int
DECLARE curse CURSOR
FOR 
select  distinct df.doctorfacilityid from doctorfacility df
left join dffeeschedule dff on df.doctorfacilityid=dff.doctorfacilityid
where isnull(dff.doctorfacilityid,0) <>7
and df.doctorfacilityid in (2)
--and df.doctorfacilityid not in 
--(select doctorfacilityid from dffeeschedule where feescheduleid = 8)

OPEN curse
FETCH NEXT FROM curse 
INTO @curse_id
WHILE @@FETCH_STATUS = 0
BEGIN

insert into dffeeschedule 
select FinancialClassMId,@curse_id,FacilityId,CompanyId,InsuranceCarriersId,InsuranceGroupId,FeeScheduleId,
EffectiveDate,ExpirationDate,ContractCode,ContractVersionId,Created,CreatedBy,LastModified,LastModifiedBy
from #tmp


   FETCH NEXT FROM curse 
   INTO @curse_id
END
CLOSE curse
DEALLOCATE curse

-- delete existing fee schedules for this doctor
--delete from dffeeschedule where doctorfacilityid=2

drop table #tmp
drop table #tmp2


--select count(*) from dffeeschedule where doctorfacilityid=2

Jeff

SELECT * FROM users WHERE clue > 0