Is there anything equivalent to a Last() Function

Question

Is there anything equivalent to a Last() Function

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

Jeffrey Williams wrote:

Jeff Moden wrote:
Ah... you're correct. No pivots required for this problem so no Cross Tab (although the code is nearly identical... just missing a CASE in each sum for the non-CROSSTAB stuff). You can still use WITH ROLLUP (or the Grouping Sets you speak of) to get all of the sub-totals and grand total and, either way, you can use the Grouping() function to control what gets displayed and when for things like that first column.
Yes - you can get the totals as outlined in his example using grouping...but that doesn't solve the problem of getting the last value for those specific columns. Another approach would be:
 Select Top 1 With Ties
        mt.Code
      , mt.Name
      , mt.Region
      , mt.Portfolio
      , mt.NumberOfUnits
      , Contacts = sum(mt.Contacts) over(Partition By mt.Code, mt.Name, mt.Region, mt.Portfolio)
      , Shows = sum(mt.Shows) over(Partition By mt.Code, mt.Name, mt.Region, mt.Portfolio)
      , Leased = sum(mt.Leased) over(Partition By mt.Code, mt.Name, mt.Region, mt.Portfolio)
   From @mockTable              mt
  Order By
        row_number() over(Partition By mt.Code, mt.Name, mt.Region, mt.Portfolio Order By mt.StartDate desc);
This also gets the raw data for the report...but I really don't know which one would be the most efficient and testing over the full set of data would be needed.
We could do the above in a CTE and then wrap that with a group by or grouping sets for the overall totals.

It's funny that the OP didn't actually post the "last value" in what appears to be the desired report. Where do you supposed that "last value" fits into that? Or are we actually talking about two separate problems on the same thread?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Jeff Moden wrote:

It's funny that the OP didn't actually post the "last value" in what appears to be the desired report. Where do you supposed that "last value" fits into that? Or are we actually talking about two separate problems on the same thread?

He did post an image of the data - and the expected results. In the expected results he wants 54 for 'Monthly Contacts' which is the value from the row where Start = '12/21/2020'. In fact - all of the 'last' values specified as the desired results come from the 'last' row for each partition - and the sum values are a total of each associated column.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Crud. I missed the (LAST) in the parenthesis on the first graphic he posted. Thanks for the correction, Jeffrey.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)