Query To Find A Missing Number

Jul 13, 2006

Hello,



I need to write a query to find out a set of missing number in a given
sequence.



Eg : a Column in some table has the following data



Col1

1

2

3

4

5

6

8

9

10



Here I need to write a query to find out that number 7 is missing in the
given sequence.

One possible solution is by using any loop. But I am looking out if the same
can be achieved using any query.



Thanks in advance.



Regards,

Mahesh

View 4 Replies


ADVERTISEMENT

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

Get Missing Number From Query?

Apr 20, 2015

In database i have field pwd type varchar(3)

Values inside that field are 1, 2, 3, 4, 5, 7, 8, 9

If you take closely you will notice that 6 is missing. With this code i can found the missing one.

[code]SELECT MIN(u.pwd) + 1 AS FirstMissing
FROM users u
WHERE (u.pwd + 1) <> (SELECT TOP 1 u2.pwd
FROM users u2
WHERE u2.pwd > u.pwd)[code]

But what if there is not missing number. How can i get the highest one + 1.

I have 1, 2, 3 assuming there is no missing number my result needs to be 4.

View 2 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related

Find First Missing Value Sequence

Feb 22, 2014

I have a alphanumeric column that looks something like this:

T0001
T0003
T0004
T0005

How would I go about identifying T0002 as the missing value in the sequence?

View 9 Replies View Related

How To Find Dates That Are Missing

May 13, 2006

Hi,

i have a db that gets real time min by min datas everyday but sometimes somehow some of those dates did not written into that db and i wanna know which dates are missing? how can i do it?

thanks

View 15 Replies View Related

Find Missing Data From Table

Jul 23, 2005

Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal

View 4 Replies View Related

Find Missing/deleted Records?

Jul 20, 2005

I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx

View 2 Replies View Related

Script To Find Out Missing Backups

Jul 22, 2015

Looking for script to find out the databases which are not being backed up only during weekend as we have got about 300 databases and some are not being backed up.

I and my company have a backup history script from where can get the history but I am need of script to find  out the databases that were not backed up only on Saturday and Sunday's.

View 3 Replies View Related

Find Missing Sequences By Category

May 22, 2008



I have to identify missing records from the example below.








Category
BatchNo
TransactionNo

CAT1
1
1

CAT1
1
2

CAT1
2
3

CAT1
2
4

CAT1
2
5

CAT1
3
6

CAT1
3
7

CAT1
3
8

CAT1
5
12

CAT1
5
13

CAT1
5
14

CAT1
5
15

CAT1
7
18

CAT2
1
1

CAT2
1
2

CAT2
3
6

CAT2
3
7

CAT2
3
8

CAT2
3
9

CAT2
4
10

CAT2
4
11

CAT2
4
12

CAT2
6
14


I need a script that will identify missing records as below







Category
BatchNo

CAT1
4

CAT1
6

CAT2
2

CAT2
5

I do not need to know that CAT1 8 and CAT2 7 are not there as they potentially have not been inserted yet.

I idealy want a nice clean SQL statement and do not particually want to insert new table's or triggers although views i Can deal with to an extent.

Considerations
up to 50,000 records added per day!!!
Only need script to run once a day and I have insert dates to help me.
Only 12 Categorys
Batch numbers always start at 1 for different categorys

View 13 Replies View Related

How To Find Out Missing Columns Between Tables

Apr 21, 2015

We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.

We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).

I googled, but I get only all the columns in tables of database.

I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded  databases).

View 8 Replies View Related

How To Select First Missing Number In Order

Feb 21, 2014

I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.

Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.

For example I have:
ID0001
ID0002
ID0003
ID0006

My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?

View 3 Replies View Related

SQL 2012 :: Find Missing References Of Each Table

Oct 12, 2015

I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?

View 2 Replies View Related

Find Missing Records In Identical Tables

Oct 31, 2007



Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.

Any help would be great, cheers.

View 3 Replies View Related

RAID Set For Database XXX Is Missing Member Number 1.

Jul 24, 2000

I saw a previous request for help, but I did not see a solution (other than deleting and re-initializing the dump.

I'm copying a database from one server to another and I repetedly get this error when I try to restore from the .bak file I create on backup. The host machine for the database is a workstation (no RAID). The Destination does have RAID, but is funtioning properly (according to the SysOps and in testing file copies). I've tried copying the .bak file to several different drives several different times and I get the same error. I also tried to back-up a database on the Destination server and restore the .bak file to a newly created database and I get the same error.

Anyone solve this problem?

View 1 Replies View Related

Transact SQL :: Trying To Do A Simple Sum But Missing Transaction Number

Jul 22, 2015

I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned.  I have a One to Many relationship between my mapping table and both PD and PDHist.

Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project.  However, it should be something like this, I think.

IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).

The addition isn’t working. I think with a small push I can get this straightened out. 

View 6 Replies View Related

SQL Server 2012 :: How To Find A Missing Sequence In A Column

Mar 20, 2014

Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )

insert into Sample

select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union

[code]....

select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.

View 8 Replies View Related

Comparing Data In Two Tables To Find Missing Records

Jul 20, 2005

I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

SQL Server 2012 :: Find Missing Numbers In Continues Series

Dec 19, 2013

CategoryNos
A 1
A 2
A 3
A 5
A 6
A 9
A 10
B 23
B 24
B 25
B 27
B 28
B 29
B 31

Consider the above data...

I need following output... query ..

CategoryMin RangeMax Range
A 1 3
A 5 6
A 9 10
B 23 25
B 27 29
B 31 31

Logic Behind :

if any number is missing in the continues series the return the min and max of number in that range.

View 1 Replies View Related

How To Find Missing Records From Tables Involving Composite Primary Keys

Nov 23, 2006

Table 1







     Code
    Quarter

500002
26

500002
27

500002
28

500002
28.5

500002
29

 

Table 2







     Code
           Qtr

500002
26

500002
27

 

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result







    Code
   Quarter

500002
28

500002
28.5

500002
29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

View 3 Replies View Related

Find Highest Number

Sep 26, 2004

hello all,

i'm not new to SQL but i cant seem to get this right:
hope some one will:

how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)

i.e :

id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9

requested result:
if input param(name)=name1

result:
1 | name1 | 2.7

if input param(name)=name2

result:
8 | name2 | 2.9

hope some one can write the sql cmd for this
i'll be grateful !
thank you!

View 3 Replies View Related

Find Number Of Licenses

Jul 20, 2005

How do you find the number of licenses that are installed on a SQL Server2000?

View 2 Replies View Related

Find First Free Number

Sep 8, 2006

I have a table

Col1 Col2

1 1000

2 1001

4 1003

5 1004

7 1006

I want to find the first free number from first column.

Now It should return 3.

After inserting a row with col1 = 3 it should return 6 and after inserting a row with col1 = 6 it should return 8.

Is it posible ?

View 3 Replies View Related

SP To Find A Gap In A Number Field

Feb 13, 2008

I have a part number field which contains whole numbers. (1 - 5000+) however there are gaps in the numbers e.g. Part Number 3876 maybe missing. The table has new parts inserted from a VB Client. Im trying to write a stored procedure where when a user trys to run an update without entering a part number the first missing part number is found. If they do provide a part number then their number is committed as long as it is unique. I've tried using cursors but am not sure on the best approach. Any suggestions?

Thanks in advance

Alex

View 6 Replies View Related

Find Number Of Decimals

May 14, 2008

Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.

(a bug forgot to round)

Thanks for your help

View 4 Replies View Related

How To Find The Row Number Transformation

Mar 31, 2006

I need help on how to find the Row Number Transformation,

I have followed this link http://www.sqlis.com/default.aspx?93

but I cannot find the Trash Destination. Could someone please tell me where I can download it?



Thanks

View 3 Replies View Related

Find Max Serial Number From 2 Tables

Feb 11, 2008

I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time  select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.

View 3 Replies View Related

How To Find The Number Of Rows In A Table

May 12, 2006

I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:




Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb    Line: 41
What to do? I need a complete example to see how it works.
Thanks...

View 3 Replies View Related

How To Find Out The Number Of User Connections

Aug 9, 2000

I am using SQL 6.5 and I would like to know (using Isql/w)
the number of users connected to my server at any given time.
Thanks for your help in advance.

View 3 Replies View Related

Find The Smallest Number In Two Columns Was: How Do I Do This ?

Jun 15, 2005

Hi,
I have a sql query which gives me the result set with lots of columns and rows.

a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395

i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?

Thanks

View 1 Replies View Related

How To Find Current Row Number In MS SQL 2000

Jan 17, 2007

hi
How to find current row number in MS SQL 2000 server DB ?
like in oracle rowid is there to get unique no. of each row.
I want to track the last row in the resultset ..
is any fun for this ??

View 7 Replies View Related

How To Find Maximum Number From Two Tables

Feb 19, 2012

My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.

View 11 Replies View Related

How To Find Age And Group By Student Number Like (IT%)

Aug 4, 2013

I have created a table:

CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;

And insert the following data:

INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)

How do i calculate the age and how can i group by the StudentNumber like 'IT%'

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved