# How to find if difference between all row data for a specific columns are same

I have a table with column with following data,

``````Column1
0.50
1.00
1.50
2.00
2.50
....
10000.00
``````

Is there a way to validate the pattern that each data row has a difference of 0.50 ?

I tried googling but even struggling with keywords

I want to achieve is that, I want to make sure no value between 1 and 10000 is missing, e.g. I want to know if 100,00 is missing or 9999.50 is missing. I am not even sure if it's achievable?

• Does the table have any other columns? A timestamp or datetime or an increasing id column to identify the order that should be considered for this check? May 23 '19 at 9:22
• @ypercubeᵀᴹ yes, it has other columns but they are irrelvant so no May 23 '19 at 9:24
• What do you expect the output to be? say example output for the sample column provided? How is the order of the column meaningful? May 23 '19 at 9:25
• @danblack I added the end result, I just want to make sure that all numbers are there, and nothing is missing or added extra May 23 '19 at 9:29

validate the pattern that each data row has a difference of 0.50

For example, look at

``````WITH cte AS ( SELECT Column1 - COALESCE(LEAD(Column1) OVER (ORDER BY Column1), 0) delta
FROM table )
SELECT delta, COUNT(delta)
FROM cte
GROUP BY delta
``````

Create one Number table which contain Decimal number. Number table with Integer will also work.

I have created small number table,you can populate it as per your requirement.

``````CREATE TABLE TBLNUMBER1(number decimal(7,2) primary key)
insert into TBLNUMBER1 (number)
select ROW_NUMBER()over(order by number)*(0.50) from master..spt_values
--Select * from TBLNUMBER1 t
``````

Sample data,

``````CREATE TABLE #TEMP (COL1 DECIMAL(7,2))

INSERT INTO #TEMP (COL1)
VALUES(0.50),(1.00),(1.50),(2.00),(2.50),(3.00),(3.50),(5.50)

--SELECT * FROM #TEMP
``````

My sample data is small.

`````` select * from TBLNUMBER1 t
where not exists(select 1 from #temp t1 where t.number=t1.col1)
and t.number between 1 and 10000
``````

I want to make sure no value between 1 and 10000 is missing,

Suppose your `Calendar table` contain more than 10000 rows.Then you must put where condition.

``````and t.number between 1 and 10000
``````

Is there a way to validate the pattern that each data row has a difference of 0.50 ?

You should throw real table structure .

``````;with CTE as
(
SELECT *,lead(t.COL1)over(order by col1)Nextcol FROM #TEMP t
)

select * from cte where nextcol-col1>0.50
``````
• You know that statement separators (`;`) should be be put between statements, and not only before CTEs, right? May 23 '19 at 10:06
• @ypercubeᵀᴹ, Yes,Why ? May 23 '19 at 10:26
• Just mentioning it, because I don't see any in your code. Except the one before `WITH` May 23 '19 at 10:52