Help refining my TSQL

[NuJoizey]

I've thought it out logically, but I need some syntax help since I am still a little feeble with t-sql, and debugging in mssql still mystifies me a bit. Once I get this working, I figured I'd schedule it as a once a month job or something.

--What needs to be done in plain English:
------------------------------------------
--SUMMARY: a branch that hasn't had placement associated with it for 1 year needs to be set to inactive.

--PROBLEM STATEMENT:
--for each branch whose most recent placement associated with it has an 'ExpirationDate' of longer than one year ago,
--set the 'Active' attribute of that particular branch to 'false'

--NOTE: One branch can have many placements.

--Refine #1 at Pseudo-TSQL:
---------------------------------------------------

--FOR EACH CompBranchID in this recordset:

	--SELECT		COMPANY.CompanyTypeID, COMPANY_BRANCH.CompBranchID
	--FROM			COMPANY_BRANCH 
	--INNER JOIN	COMPANY ON COMPANY_BRANCH.CompanyID = COMPANY.CompanyID
	--WHERE			(COMPANY.CompanyTypeID = 2)

		--IF IsItStagnant(@CompBranchID)=True THEN
			--UPDATE    COMPANY_BRANCH
			--SET       Active = FALSE
			--WHERE     (CompBranchID = @ReturnedProducerBranchID 
--NEXT

--UDF IsItStagnant(@CompBranchID) RETURNS True/False
	--IF GetExpirationDate(@CompBranchID) < GETDATE()-1Year  THEN
		--RETURN True
	--ELSE
		--RETURN False	


--UDF GetExpirationDate(@CompBranchID)  RETURNS ExpirationDate
	--SELECT     TOP (1) ProducerBranchID, ExpirationDate
	--FROM         PLACEMENT
	--WHERE     (ProducerBranchID = @CompBranchID  
	--ORDER BY ExpirationDate DESC 	

--RETURN ExpirationDate

Gurus, I request your insight, mostly with a good loop construction in tsql - i think i can handle the UDF parts - I know there's no FOR...EACH..NEXT in tsql, right? Any input you can give would be appreciated

 

[LNBruno]

Why not simply:

DECLARE @ItWasAYearAgoToday
    SET @ItWasAYearAgoToday = SELECT GETDATE()-365
UPDATE COMPANY_BRANCH
         SET Active = 0 --hoping this is a bit field
       WHERE ExpirationDate < @ItWasAYearAgoToday

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[ESquared]

Forget you ever knew the words loop or CURSOR. These are not the way you should be thinking about queries.

Note: "FALSE" is not a valid value in T-SQL. Choose an appropriate value for the data type of the column "Active."

UPDATE B1
SET B1.Active = 0
FROM
   Company_Branch B1
   INNER JOIN (
      SELECT B2.CompBranchID
      FROM
         Company_Branch B2
         INNER JOIN Company C ON B2.CompanyID = C.CompanyID
         INNER JOIN Placement P ON B2.CompBranchID = P.ProducerBranchID
      WHERE C.CompanyTypeID = 2
      GROUP BY B2.CompBranchID
      HAVING Max(P.ExpirationDate) < DateAdd(yy, -1, GetDate())
   ) X ON B1.CompBranchID = X.CompBranchID

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[nice95gle]

>> NOTE: One branch can have many placements.

Does this mean if a branch has at lease 1 placement that is greater than 1 year ago everything for that bracnh should get set to false. Or as long as a branch has 1 valid date (less than a year they should not get updated)?

Well Done is better than well said
- Ben Franklin

 

[NuJoizey]

LNBruno, thanks - i don't think that'll work, and here is why -

The ExpirationDate is an attribute of PLACEMENT, not of COMPANY_BRANCH. Each COMPANY_BRANCH has a ton of placements, and some of them might be old, but they're most recent is still within a year and therfore, I would not want to deactivate that COMPANY_BRANCH. I need to find only those that have the most recent placement more than a year ago and set only those COMPANY_BRANCH(es) active to 0.

 

[NuJoizey]

Does this mean if a branch has at lease 1 placement that is greater than 1 year ago everything for that bracnh should get set to false.

No. only that if its most recent is greater than a year ago, active should be set to false

Or as long as a branch has 1 valid date (less than a year they should not get updated)?

yes

 

[NuJoizey]

Esquared

Forget you ever knew the words loop or CURSOR. These are not the way you should be thinking about queries.

ok thanks, I think this makes some sense, as I think i am getting confused because of my days as a vb/vba programmer fiddling with recordsets....

 

[nice95gle]

So I think what E[sup]2[/sup] posted should help you or at lease get you started.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

>>...I think i am getting confused because of my days as a vb/vba programmer fiddling with recordsets....

read.

http://www.codeproject.com/database/Sql__set-oriented.asp

Well Done is better than well said
- Ben Franklin

 

[NuJoizey]

thanks, you've all given me a lot to chew on. I'm now in the process of trying to deconstruct esquareds code example, which i think is right, but I'm trying to figure out why so i have it straight in my own head.

 

[ESquared]

That's a reasonable article that nice95gle posted, but it does have some problems.

Not so good:

Delete From tbl_rangeList
Where Id In
    (Select B.Id From tbl_toRemove As B)

Better:

Delete L
FROM tbl_rangeList L
INNER JOIN tbl_toRemove R ON L.ID = R.ID

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[NuJoizey]

ok, working through this by trying to recreate what esquared posted, i understand this much:

This piece returns those COMPANY_BRANCH(es) whose most recent PLACEMENT date is before 1 year ago today (there happen to be three of them)

SELECT     COMPANY_BRANCH.CompBranchID, MAX(PLACEMENT.ExpirationDate) AS ExpDate
FROM         COMPANY_BRANCH INNER JOIN
                      PLACEMENT ON COMPANY_BRANCH.CompBranchID = PLACEMENT.ProducerBranchID
GROUP BY COMPANY_BRANCH.CompBranchID
HAVING      (MAX(PLACEMENT.ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))

However what I am not quite understanding is then, once these are returned, then how the UPDATE works on a result set that is in a GROUP BY.

It looks like esquared is joining COMPANY_BRANCH to iteself? and somehow only those three records are updated - but I'm not seeing the logic behind it.

 

[NuJoizey]

specifically, i do not understand what the "X" does

 

[NuJoizey]

ok, here's is a variation that seems to work that is a bit easier for me to understand. Any reason why this isn't the thing to do?

UPDATE COMPANY_BRANCH

SET ACTIVE = 0 WHERE CompBranchID IN 

(

SELECT COMPANY_BRANCH.CompBranchID
FROM COMPANY_BRANCH INNER JOIN
PLACEMENT ON COMPANY_BRANCH.CompBranchID = PLACEMENT.ProducerBranchID
GROUP BY COMPANY_BRANCH.CompBranchID
HAVING (MAX(PLACEMENT.ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))

)

 

[nice95gle]

Almost there but not just yet

You don't need the WHERE clause and you don't need COMPANY_BRANCH in your sub-query. The sub-query should return only the id's that need to be updated.

Notice the WHERE clause was replaced with a inner join and in the sub-query I am only focusing on the PLACEMENT table. I've also added cb and p as aliases for easier reading.

UPDATE cb
SET cb.ACTIVE = 0 
	from COMPANY_BRANCH cb
inner join
	(select ProducerBranchID from PLACEMENT
		group by ProducerBranchID
		HAVING MAX(ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))p
on cb.CompBranchID=p.ProducerBranchID

If you have any question let me know.

Well Done is better than well said
- Ben Franklin

 

[bjb123]

specifically, i do not understand what the "X" does

E2s code does an inner join with a derived table, the X is the alias for that derived table. Same with nice95gle code above, but he's using p.