7 Replies - 232 Views - Last Post: 13 September 2018 - 09:18 AM

#1 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

update current and previous value

Posted 13 September 2018 - 01:26 AM

I have a table of company stats,

CompanyName, CurrentRating, PreviousRating, etc..

In another table I have the companies listed, dates and ratings. For example:

Quote

CompanyName, Date, RecommendationCode
Bob's Biscuits,10/09/18,Buy these
Bob's Biscuits,11/09/18,They have gone off
Bob's Biscuits,13/09/18,Back in business

I want to update the current and previous rating in the original table, based on the company and the date order. Currently I have two statements to do this in turn:

-- update the CurrentRating
UPDATE @GatherData SET CurrentRating = GR.RecommendationCode
	FROM @GatherData GD INNER JOIN ( 
		SELECT CompanyName, RecommendationCode, [Date], ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY [Date] DESC) as row_n
			FROM @GatherRatings) GR ON GD.CompanyName = GR.CompanyName 
			WHERE GR.row_n = 1

-- update the PreviousRating
UPDATE @GatherData SET PreviousRating = GR.RecommendationCode
	FROM @GatherData GD INNER JOIN ( 
		SELECT CompanyName, RecommendationCode, [Date], ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY [Date] DESC) as row_n
			FROM @GatherRatings) GR ON GD.CompanyName = GR.CompanyName 
			WHERE GR.row_n = 2

Is it possible to do this in one statement?

I did attempt this with two inner joins to the same table but I think the WHERE clause WHERE GR.row_n = 1 OR GR2.row_n = 2 threw things out.

Is This A Good Question/Topic? 0
  • +

Replies To: update current and previous value

#2 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7330
  • View blog
  • Posts: 15,244
  • Joined: 16-October 07

Re: update current and previous value

Posted 13 September 2018 - 04:41 AM

Challenge accepted.

First, I have to say, I never use OVER because it's non standard and, well, I've never had to. With that in mind...

This should give you the first two dates:
SELECT fd.CompanyName, fd.[Date] as DtFirst, 
  (SELECT min([Date]) FROM @GatherRatings b where a.CompanyName=b.CompanyName and b.[Date]<fd.[Date]) as DtSecond
  FROM (SELECT CompanyName, min([Date]) as [Date]  FROM @GatherRatings group by CompanyName) fd



Once you have that, you can dig down into the data:
select a.CompanyName, b.RecommendationCode as CurrentRating, c.RecommendationCode as PreviousRating
  from (
    SELECT fd.CompanyName, fd.[Date] as DtFirst, 
      (SELECT min([Date]) FROM @GatherRatings b where a.CompanyName=b.CompanyName and fd.[Date]<b.[Date]) as DtSecond
      FROM (SELECT CompanyName, min([Date]) as [Date]  FROM @GatherRatings group by CompanyName) fd
  ) a 
  join @GatherRatings b on a.CompanyName=b.CompanyName and a.DtFirst=b.[Date]
  left join @GatherRatings c on a.CompanyName=c.CompanyName and a.DtSecond=c.[Date]



With this, your update becomes a single statement:
UPDATE @GatherData 
    SET CurrentRating = GR.CurrentRating, PreviousRating = GR.PreviousRating
    FROM @GatherData GD
      JOIN (
        select a.CompanyName, b.RecommendationCode as CurrentRating, c.RecommendationCode as PreviousRating
          from (
            SELECT fd.CompanyName, fd.[Date] as DtFirst, 
              (SELECT min([Date]) FROM @GatherRatings b where a.CompanyName=b.CompanyName and fd.[Date]<b.[Date]) as DtSecond
              FROM (SELECT CompanyName, min([Date]) as [Date]  FROM @GatherRatings group by CompanyName) fd
          ) a 
          join @GatherRatings b on a.CompanyName=b.CompanyName and a.DtFirst=b.[Date]
          left join @GatherRatings c on a.CompanyName=c.CompanyName and a.DtSecond=c.[Date]
      ) GR ON GD.CompanyName = GR.CompanyName



As an aside, while dates are usually safe, you can still get burnt with a non unique collision, even if you don't expect it. In MSSQL tables should usually have an identity column which is sequentially incremented. If you have an identity that's applicable you should try to can key off of that.

So, generally, find single row with GROUP BY and MAX on the identity. Use the result of that to get the data from your targeted row. If you want a second row, you use the result of that first row to influence the second. It usually looks messy, but it's logically sound.

This post has been edited by baavgai: 13 September 2018 - 04:42 AM
Reason for edit:: code tag fail

Was This Post Helpful? 1
  • +
  • -

#3 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

Re: update current and previous value

Posted 13 September 2018 - 05:12 AM

Thank you very much.

The presence of join and left join did also inspire me to have another go with my earlier attempt:

UPDATE @GatherData SET CurrentRating = GR.RecommendationCode, PreviousRating = GR2.RecommendationCode
	FROM @GatherData GD INNER JOIN ( 
		SELECT CompanyName, RecommendationCode, [Date], ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY [Date] DESC) as row_n
			FROM @GatherRatings) GR ON GD.CompanyName = GR.CompanyName AND GR.row_n = 1
		LEFT JOIN ( 
		SELECT CompanyName, RecommendationCode, [Date], ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY [Date] DESC) as row_n
			FROM @GatherRatings) GR2 ON GD.CompanyName = GR2.CompanyName  AND GR2.row_n = 2

It is a little tricky, though, for me to visualise or explain what join..left join is achieving. I suppose, roughly, it is ensuring that the first value doesn't disappear when gathering the second(?).

(I know you are not a fan of OVER ;) )

I suppose a could avoid running the OVER/partitioning twice with the use of a CTE or temp table but it may not save a great deal.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

Re: update current and previous value

Posted 13 September 2018 - 05:25 AM

Actually, moving to a CTE is not too involved and looks much neater as well:

;WITH ratingsCTE AS (SELECT CompanyName, RecommendationCode, [Date], ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY [Date] DESC) as row_n
			FROM @GatherRatings)
UPDATE @GatherData SET CurrentRating = GR.RecommendationCode, PreviousRating = GR2.RecommendationCode
	FROM @GatherData GD INNER JOIN (SELECT * FROM ratingsCTE) GR 
			ON GD.CompanyName = GR.CompanyName AND GR.row_n = 1
		LEFT JOIN (SELECT * FROM ratingsCTE) GR2 
			ON GD.CompanyName = GR2.CompanyName  AND GR2.row_n = 2

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

Re: update current and previous value

Posted 13 September 2018 - 06:57 AM

Perhaps I could trouble for another related, but simpler, question.

I have a table Prices (CompanyID, Date, Price).

In my @GatherData table the company can appear more than once. For each company I want to update the row with the latest price (PriceAt). My attempt,

UPDATE @GatherData SET PrimaryAuthor = ANA.LastName + ', ' + ANA.FirstName, 
		Symbol = COM.Ticker, CompanyName = COM.CompanyName, PriceAt = PR.Price 
	FROM @GatherData GD 
		LEFT OUTER JOIN tblAnalysts ANA ON GD.PrimaryAnalystID = ANA.AnalystID 
		LEFT OUTER JOIN tblCompanies COM ON GD.CompanyID = COM.CompanyID
		LEFT OUTER JOIN (SELECT TOP 1 Price, CompanyID FROM Prices ORDER BY [Date] DESC) PR ON GD.CompanyID = PR.CompanyID

The prices are all blank/null. I can see why; selecting the TOP 1 Price and CompanyID happens first, before the join is applied, so the company that happens to be in the first row could be anything, so the join fails.

How could I adjust the subquery to be filtered for the current company? Perhaps I need to use GROUP BY?

My attempt with WHERE is invalid:

		LEFT OUTER JOIN (SELECT TOP 1 Price, CompanyID FROM Prices WHERE CompanyID = GD.CompanyID ORDER BY [Date] DESC) PR --ON GD.CompanyID = PR.CompanyID

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

Re: update current and previous value

Posted 13 September 2018 - 07:07 AM

Actually, I can make use of CTE's again:

;WITH priceCTE AS (
	SELECT Price, CompanyID, ROW_NUMBER() OVER(PARTITION BY CompanyID ORDER BY [Date] DESC) AS row_n
	FROM Prices
), priceCTE1 AS (
	SELECT Price, CompanyID FROM priceCTE WHERE row_n = 1
)
UPDATE @GatherData SET PrimaryAuthor = ANA.LastName + ', ' + ANA.FirstName, 
		Symbol = COM.Ticker, CompanyName = COM.CompanyName, PriceAt = CTE1.Price 
	FROM @GatherData GD 
		LEFT OUTER JOIN tblAnalysts ANA ON GD.PrimaryAnalystID = ANA.AnalystID 
		LEFT OUTER JOIN tblCompanies COM ON GD.CompanyID = COM.CompanyID
		LEFT OUTER JOIN priceCTE1 CTE1 ON GD.CompanyID = CTE1.CompanyID

Neato. Although I still welcome suggestions, corrections, to my above attempt.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7330
  • View blog
  • Posts: 15,244
  • Joined: 16-October 07

Re: update current and previous value

Posted 13 September 2018 - 08:46 AM

Ok, so, latest price is a nice, simple, case.

Given Prices (CompanyID, Date, Price), the latest price would be:
select CompanyID, max([Date]) from Prices group by CompanyID


Now, obviously, that's just the latest date, but that's step one of the question.

Step two is to find the entire row for that id,date pair, so:
select a.*
  from Prices a
    join (select CompanyID, max([Date]) as [Date] from Prices group by CompanyID) b
      on a.CompanyID=b.CompanyID and a.[Date]=b.[Date]



So, setting latest price:
UPDATE @GatherData 
  SET PriceAt = PR.Price
    FROM @GatherData GD
      LEFT OUTER JOIN (
        select a.CompanyID, a.Price
          from Prices a
            join (select CompanyID, max([Date]) as [Date] from Prices group by CompanyID) b
              on a.CompanyID=b.CompanyID and a.[Date]=b.[Date]
      ) PR on GD.CompanyID = PR.CompanyID




If I'm pulling multiple tables in for an update, I'd prefer to isolate the result set in a subquery. This also gives the ablity to inspect everything before you hit fire.

So, query:
select GD.CompanyID,
    PrimaryAuthor = ANA.LastName + ', ' + ANA.FirstName,
    Symbol = COM.Ticker,
    CompanyName = COM.CompanyName,
    PriceAt = PR.Price
  FROM @GatherData GD
    LEFT OUTER JOIN tblAnalysts ANA ON GD.PrimaryAnalystID = ANA.AnalystID
    LEFT OUTER JOIN tblCompanies COM ON GD.CompanyID = COM.CompanyID
    LEFT OUTER JOIN (
      select a.CompanyID, a.Price
        from Prices a
          join (select CompanyID, max([Date]) as [Date] from Prices group by CompanyID) b
            on a.CompanyID=b.CompanyID and a.[Date]=b.[Date]
    ) PR on GD.CompanyID = PR.CompanyID


Note the "alias =" syntax, as opposed to the standard "as alias" syntax, is MSSQL specific sugar.

If you like the results you see, just wire them in:
UPDATE @GatherData 
    SET PrimaryAuthor = u.PrimaryAuthor,
    Symbol = u.Symbol,
    CompanyName = u.CompanyName,
    PriceAt = u.PriceAt
  FROM @GatherData GD
    JOIN (
      select GD.CompanyID,
          PrimaryAuthor = ANA.LastName + ', ' + ANA.FirstName,
          Symbol = COM.Ticker,
          CompanyName = COM.CompanyName,
          PriceAt = PR.Price
        FROM @GatherData GD
          LEFT OUTER JOIN tblAnalysts ANA ON GD.PrimaryAnalystID = ANA.AnalystID
          LEFT OUTER JOIN tblCompanies COM ON GD.CompanyID = COM.CompanyID
          LEFT OUTER JOIN (
            select a.CompanyID, a.Price
              from Prices a
                join (select CompanyID, max([Date]) as [Date] from Prices group by CompanyID) b
                  on a.CompanyID=b.CompanyID and a.[Date]=b.[Date]
          ) PR on GD.CompanyID = PR.CompanyID
    ) u on GD.CompanyID = u.CompanyID



Also, be aware, you don't actually loose anything by doing these one at a time. Indeed, it's possible for any kind of complex join to loose it's key optimization somewhere along the line and that will slow things down. Since you're playing with temp table variables, you may also find a speed benefit from loading the updates into another temp table and then loading them in. Also, #temp will tend to perform better than @temp in anything particularly complex.
Was This Post Helpful? 1
  • +
  • -

#8 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6646
  • View blog
  • Posts: 27,203
  • Joined: 12-December 12

Re: update current and previous value

Posted 13 September 2018 - 09:18 AM

You're a star :rockon:

I tend to use temp tables myself, but every feature and variation is used here!

Thanks again.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1