Figuring out a pattern to find out the maximum numerical value

Databases
1

I have the following SQL query from SSMS and it is returning CompanyCase column values like

Sample Data:1

MH 9090
MH 9089
MH 9088
MH 9087
MH 9086
MH 9085

So as you can see, CompanyCase are in descending orders and in a sequence as far as numbers are concerned. Now, there can be a scenario when
some of them might not be in a sequence and it can be like the following:

Sample Data:2

MH 10005
MH 10001
MH 9099
MH 9090
MH 9089
MH 9088
MH 9087
MH 9086
MH 9085

In such scenarios, I want to write a SQL query which can find out 3 or 5 sequential values and based on that I can warn the user about the next
sequentia value. In above example, since MH9085 - MH9090 are in sequential, I would like to suggest the user that you should use MH9091 as the next value
I think even if I can get MH9090 in this case, I can suggest by adding 1 to 9090 that MH9091 is the next CompanyCase which coud be used.
Is there a way to modify the below query to achieve the same? The datatype of CompanyCase column is varchar(16) in the Employee table.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [employeeID]
      ,[clientID]
      ,[speciesID]
      ,[CaseID]
      ,[CompanyCase]
      ,[entryDate]
      ,[lastUpdate]
      ,[checkInDate]
      ,[endDate]
  FROM [mydatabase].[dbo].[Employee]
  where CompanyCase LIKE 'MH %'
  ANd endDate IS NULL
  order by checkInDate desc, CompanyCase desc

Edit:
What I’m looking for:
I am looking to find a pattern of sequence if it exists. For example, in Sample Data:1, it’s very clear that all numerical data is sequential so if I have to go and pick up the case that has the largest numerical value, I can just go and grab the top one, which is MH9090. However, in case of Sample Data:2, I would still like to grab MH9099 since that has the sequential pattern of numerical values and MH10001 and MH10005 are not sequential.

2

don’t you mean MH9090 here?

in any case, you’ll need a gaps and islands analysis

here’s a good article to get you started – Introduction to Gaps and Islands Analysis

3

Also sounds like you’d better have some pretty tight deletion cascades on that database.

4

Best way to find out maximum of your num values is to apply common table expressions CTEs. Simply assign row numbers and calculate differences between the row numbers and the CompanyCase Value. Hope these would be useful for you!!

5

Correct.

6

Do you have an example for this? Thanks!

7

Thanks. Reading about it.

8

WITH CTE AS ( SELECT CompanyCase, ROW_NUMBER() OVER (ORDER BY CompanyCase DESC) AS RowNum FROM Employee WHERE CompanyCase LIKE ‘MH %’ AND endDate IS NULL ), CTE2 AS ( SELECT CompanyCase, RowNum, CAST(SUBSTRING(CompanyCase, 4, LEN(CompanyCase) - 3) AS INT) - RowNum AS Diff FROM CTE ) SELECT TOP 1 CompanyCase FROM CTE2 WHERE Diff = (SELECT MIN(Diff) FROM CTE2) ORDER BY CompanyCase DESC

What will happened next?

  • It creates a CTE that assigns a row number to each CompanyCase value in descending order.

  • It creates another CTE that calculates the difference between the row number and the numeric part of the CompanyCase value.

  • It selects the top one CompanyCase value from the second CTE where the difference is the minimum, which means it is the most sequential.

For example, if the data is:

CompanyCase RowNum Diff
MH 10005 1 10004
MH 10001 2 9999
MH 9099 3 9096
MH 9090 4 9086
MH 9089 5 9084
MH 9088 6 9082
MH 9087 7 9080
MH 9086 8 9078
MH 9085 9 9076

The query will return MH9099 as the maximum sequential value, because it has the minimum difference of 9096. MH9100 as the next value. hope this could help!!

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.