0

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? – ypercubeᵀᴹ May 23 at 9:22
  • @ypercubeᵀᴹ yes, it has other columns but they are irrelvant so no – Mathematics May 23 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? – danblack May 23 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 – Mathematics May 23 at 9:29
1

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
0

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? – ypercubeᵀᴹ May 23 at 10:06
  • @ypercubeᵀᴹ, Yes,Why ? – KumarHarsh May 23 at 10:26
  • Just mentioning it, because I don't see any in your code. Except the one before WITH – ypercubeᵀᴹ May 23 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.