Findout missed numbers

[tqmd1]

Dear Experts

Sqlserver Table1holds data as follows

date---------------sno
01/01/2010-------1
01/01/2010-------2
01/01/2010-------4
01/01/2010-------5
01/01/2010-------7
02/01/2010-------1
02/01/2010-------2
02/01/2010-------6
02/01/2010-------8

How to findout missed sno in 01/01/2010?

I want to get this result
3
6

Please help

[Bruce Fox]

Did the sno 3 and 6 ever exist for 01/01/2010?

In case the above response is true, generaly, in DataBases records should never be 'deleted', just canceled or made inactive etc. With that design (and a Primary Key), you would have an easy way to find out what records were canceled/inactive for any period.

[Bruce Fox]

Also, how do you know that sno 8 didn't exist in that period?

[jmcilhinney]

While I'm sure it's possible, I'm not aware of a simple way to do that purely in SQL. I'd probably suggest that you get a list of distinct values from the database, then use LINQ to get the complement. For instance, you could query the database like so:

SELECT DISTINCT sno
FROM Table1
WHERE date = @date

You can then do this in LINQ:

Dim table As New DataTable
 
table.Columns.Add("sno", GetType(Integer))
 
table.Rows.Add(1)
table.Rows.Add(2)
table.Rows.Add(4)
table.Rows.Add(5)
table.Rows.Add(7)
 
'*** The code above simply simulates getting the data from the database.
 
Dim presentValues = (From row In table.AsEnumerable() _
                     Select row.Field(Of Integer)("sno")).ToArray()
Dim maxValue = presentValues.Max()
Dim allValues = Enumerable.Range(1, maxValue)
Dim missingValues = From int In allValues Where Not presentValues.Contains(int)
 
For Each v In missingValues
    MessageBox.Show(v.ToString())
Next

[jmcilhinney]

I should point out that I just answered the question as asked. I have to concur with Bruce though: if you're doing this to reuse IDs then you really shouldn't.