Calculate difference between max and min value

1

How can I calculate the difference between min and max in the following scenario of dbfiddle (9090-1500) ?

image
image836×393 11.4 KB

Here's the code:


CREATE TABLE dbo.Company
(
  checkInDate date, -- these columns aren't important
  endDate     date, -- ^^^^^
  CompanyCase varchar(16)
);

INSERT dbo.Company(CompanyCase) VALUES
('MH 09090'),
('MH 09089'),
('MH 09088'),
('MH 09087'),
('MH 09086'),
('MH 09085'),
('MH 09084'),
('MH 09027B'), 
('MH 09019 x%6'),
('MH 001500  B'),
('A  48348 4$'),
(' /00000112'),
('NCD344534'),
('NCD 34445');



SELECT TOP (10) co = TRY_CONVERT(int, LTRIM(RTRIM(SUBSTRING(CompanyCase,3,7))))
  FROM dbo.Company 
  WHERE CompanyCase LIKE 'MH [0-9][0-9][0-9][0-9][0-9]%'
  ORDER BY checkInDate DESC, CompanyCase DESC;
2

hi

hope this helps

; with cte as 
(
select cast(replace(left(CompanyCase,9),'MH','') as int ) as value from Company where CompanyCase like '%MH%'
) 
select max(value) - min(value) from cte

image

3 
WITH nums
AS
(
	SELECT LEFT(C.CompanyCase, 2) AS Code
		,CAST(SUBSTRING(C.CompanyCase, X1.nStart, X2.nLen) AS int) AS num
	FROM dbo.Company C
		CROSS APPLY (VALUES(PATINDEX('%[0-9]%', CompanyCase))) X1 (nStart)
		CROSS APPLY
		(
			VALUES
			(
				CASE
					WHEN PATINDEX('%[^0-9]%', SUBSTRING(C.CompanyCase, X1.nStart, 255)) > 0
					THEN PATINDEX('%[^0-9]%', SUBSTRING(C.CompanyCase, X1.nStart, 255)) - 1
					ELSE 255
				END
			)
		) X2 (nLen)
	WHERE C.CompanyCase LIKE 'MH%'
)
,Gaps
AS
(
	SELECT Code, num
		,CASE
			WHEN num + 1 = LAG(num) OVER (PARTITION BY Code ORDER BY num DESC)
			THEN 0
			ELSE 1
		END AS Gap
	FROM nums
)
,Grps
AS
(
	SELECT Code, num
		,SUM(Gap) OVER (PARTITION BY Code ORDER BY num DESC) AS Grp
	FROM Gaps
)
SELECT Code
	,MIN(num) AS MinNum
	,MAX(num) AS MaxNum
FROM Grps
GROUP BY Code, Grp;

ps Trying to fill in missing gaps in a sequence is usually a bad idea. What happens if the missing number once existed and needs to be restored into the live dataset? etc.

1 Like
4

Thanks. I only want the difference between the max MH value and min MH value of top 10 records? Is it possible to modify your query to achieve the same?

5

Select the top 10 in a cte first

Then apply the remaining code

6

Are you talking about your code or Ifor's code? Your code is not handling al the patterns and failing. Ifor code works fine as it seems to be handling patterns.

7

hi

i am talking about IFORs code

since it handles all the patterns ..which is what you want

8

hi

i have changed my code .. now it picks up your patterns

; with cte as 
  (
    select 
        replace(companycase,'MH ','') as Case1 
    from 
	   Company where CompanyCase like 'MH %'
   )
select 
     cast(left(case1,case when PATINDEX('%[^0-9]%',Case1)-1 = -1 then 5 else  PATINDEX('%[^0-9]%',Case1)-1 end ) as int) 
from 
   cte

image