Looking for help creating a SQL update statement 2

[MdotButler]

I am looking for a bit of help creating a SQL update statement. The task is to update a first and last activity date in a company table based on bills that are going through a billing cycle. The following code would update the entire company table and is what I used when I created the new activity date fields. It process all companies and all encounters.

UPDATE Company Set
C_FirstActivity = (SELECT MIN(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID),
C_LastActivity = (SELECT MAX(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID)

Now the twist is to only update the companies in the current batch of bills and only if the activity dates are less than or greater than the activity values already in the company table. There is a field in the encounter (EN_BilledBATID) containing a batch id and the procedure has a variable (@BATID) containing the batch id we are currently processing.

I thought about doing this with a cursor and looping through for each company being processed in the current batch but I would like to do it without looping through a cursor.

Any help would be appreciated. thanx
Mark

 

[markros]

It looks like your need to use EXISTS operator, but I can not exactly grasp the requirements.

Something like

update Company Set ...
where not exists (select 1 from Company C where C.DateField > Company.DateField) and not exists (select 1 from Company C where C.AnotherDateField > Company.AnotherDateField)

That's the idea - you hopefully can adjust for your case.

PluralSight Learning Library

 

[MdotButler]

I don't get what you are trying to do with the EXISTS clause. Following is a bit of pseudo code to hopefully illustrate the requirement better.

DECLARE @BATID AS CHAR(36)
SET @BATID = NEWID()

UPDATE Encounter 
   SET EN_BilledBATID = @BATID, EN_BilledDate = GETDATE()
Where EN_BilledDate IS NULL

UPDATE Company SET
   C_FirstActivity = (Lessor of: existing C_FirstActivity, or EN_AddDate from Encounter with EN_BilledBATID = @BATID),
   C_LastActivity = (Greater of: existing C_LastActivity, or EN_AddDate from Encounter with EN_BilledBATID = @BATID)

Note that there most likely would be multiple encounters for the same company being billed at the same time. That was the reason for the MIN(EN_AddDate) and MAX(EN_AddDate) in the original post.

 

[markros]

I see - it's very easy then.

declare @En_AddDateMin datetime, @En_AddDateMax datetime

select @EN_AddDateMin = min(EN_AddDate), 
@EN_AddDateMax = Max(En_AddDate) from Encounter
where EN_BilledBatID = @BatID

update Company 
  SET C_FirstActivity = 
  case when C_FirstActivity > @EN_AddDateMin then @EN_AddDateMin else c_FirstActivity end,
C_LastActivity = case when C_LastActivity > @EN_AddDateMax
then C_LastActivity else @EN_AddDateMax end

PluralSight Learning Library

 

[MdotButler]

The twist is that within one @BATID there may be many companies. What you propose would work if contained in a loop, once for each company. I was hoping to perform the update in one statement without having to loop through a cursor.

Building on what your solution was I think the following is the solution.

UPDATE Company SET
C_FirstActivity = 
CASE WHEN C_FirstActivity > ISNULL((SELECT MIN(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID AND EN_BilledBATID = @BATID),C_FirstActivity) THEN
  (SELECT MIN(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID AND EN_BilledBATID = @BATID) 
ELSE 
  C_FirstActivity 
END,
C_LastActivity = 
CASE WHEN C_LastActivity < ISNULL((SELECT MAX(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID AND EN_BilledBATID = @BATID),LastActivity) THEN
  (SELECT MAX(EN_AddDate) FROM Encounter WHERE EN_CID = C_CID AND EN_BilledBATID = @BATID) 
ELSE
  C_LastActivity 
END

I will test the above and see if it works. With that many subqueries I don't know what the performance will be.

 

[markros]

I see - easy enough as well.

;with cte as (EN_CID, select Min(EN_AddDate) as MinDate, Max(EN_AddDate) from Encounter where EN_BilledBATID = @BatID group by EN_CID)

update CO set C_FirstActivity = case when C.MinDate > CO.C_FirstActivity 
then C_FirstActivity else C.MinDate end,
C_LastActivity = case when C.MaxDate > C_LastActivity 
then C.MaxDate else C_LastActivity end
from Company CO inner join cte C on CO.C_CID = C.EN_CID

PluralSight Learning Library

 

[markros]

I missed select in cte definition, e.g.

;with cte as (select EN_CID, etc.)

PluralSight Learning Library

 

[PWise]

how about

DECLARE @BATID AS CHAR(36)
SET @BATID = NEWID()

UPDATE Encounter 
   SET EN_BilledBATID = @BATID, EN_BilledDate = GETDATE()
Where EN_BilledDate IS NULL

UPDATE Company 
Set 
C_FirstActivity = Case when MinDate<C_FirstActivity then MinDate else C_FirstActivity end ,
C_LastActivity = Case when MaxDate>C_LastActivity then MaxDate else C_LastActivity end
From Company
inner join (Select EN_CID, min(EN_AddDate)MinDate,max (EN_AddDate)MaxDate
            From Encounter
            inner join(Select EN_CID 
                       from Encounter
                       where EN_BilledBATID = @BATID
                       )Batid
            on batid.EN_CID=Encounter.EN_CID              
            Group by EN_CID
            )Encounter
on Encounter.EN_CID=Company.C_CID

 

[markros]

As Nastya Murzin said - don't copy my solutions )))))

Just kidding - you don't have to use two derived tables, IMHO, one derived is enough.

PluralSight Learning Library