0

Hi I am trying to figure out how to return one row from a table that has the highest value in a column. Before I can do that I have to take a substring of that column and convert it to an INT.

The Main table name is "CalendarPeriod"

Here is an example of the column

FiscalPeriod - 0120

I have to take the two characters from the left, which will be the number I am trying to find the MAX value of

Below is a list of the rows with there column Names. FiscalPeriod is the Column that I need to Substring and Cast to an Int to find the max value.

enter image description here

The result of this query should return return the row with the FiscalPeriod "1220" with the Id being "1134", but it is returning the first one.

Here is the query that I found, but I am having trouble trying to figure out how to convert that back to the NVARCHAR. I though that If I added the regular Fiscal Period inside the inner join I would be able to use that in the Where Clause to get the max value, but I was wrong.

I also am Inner Joining another Table "CalendarPeriodHeader" which has the year which I will also need for a comparison in the where. This might be messing up the query results as well, but I am stumped Below is the query I have so far.

Select Top 1 cp.*
From CalendarPeriod as cp
INNER JOIN
(
    Select  Top 1 Id, Max(Cast(Substring(FiscalPeriod, 1,2) as Int)) as MAXFiscal, FiscalPeriod
    From CalendarPeriod 
    Group By Id, FiscalPeriod
    
)cp2
On cp2.Id = cp.Id
Inner Join CalendarPeriodHeader as header on header.Id = cp.HeaderId
Where cp2.FiscalPeriod = cp.FiscalPeriod And header.Year = 2020
Eric
  • 212
  • 2
  • 15
  • 1
    Using `top` without `order by` is usually problematic. – Gordon Linoff Feb 12 '21 at 17:16
  • find the max, then subquery using it https://stackoverflow.com/questions/16182700/how-to-use-max-on-a-subquery-result –  Feb 12 '21 at 17:17
  • @GordonLinoff That might work I don't know why I didn't catch that! – Eric Feb 12 '21 at 17:20
  • Why are you storing dates in datetime columns? 2 digit years? Don't start now. Want to sort by fiscal period? Year should come first. Doing that will make your goal much simpler since you can use max aggregate with appropriate filter. – SMor Feb 12 '21 at 17:26
  • Have to store dates in a datetime due to the complexity of calendar periods in accounting for example a 52/53 week calendar period the fiscal period 0120 could be April 27,2020 and the 1220 fiscal period could be April 28, 2021 – Eric Feb 12 '21 at 18:41
  • @GordonLinoff your solution worked! – Eric Feb 12 '21 at 18:41

1 Answers1

1

I would use this query: SELECT TOP 1 * FROM dbo.CalendarPeriod ORDER BY Cast(Substring(FiscalPeriod, 1,2) as Int) DESC

Máté Gábor
  • 96
  • 5