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
ADVERTISEMENT
Apr 27, 2015
Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....
View 2 Replies
View Related
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
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
Jul 23, 2014
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
View 2 Replies
View Related
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
Oct 7, 2015
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
View 4 Replies
View Related
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 9 Replies
View Related
Jul 16, 2014
Can't seem to make this SQL query work!
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
View 2 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Dec 18, 2012
I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 Replies
View Related
Jun 30, 2015
In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?
DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR
View 2 Replies
View Related
Feb 4, 2015
Is there any possibility to trace, most used column in every table on the SQL server?
The intention is, a query which suggests some columns to be indexed; Based on the usage of the column by the queries that run on server.
View 4 Replies
View Related
Dec 12, 2012
I've started using a SEQUENCE in a table instead of an identity.
I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.
For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7002, but the last inserted row has a primary key of 7032!
I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.
When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.
The definition for the sequence is:
CREATE SEQUENCE [dbo].[IntegrationQueueSEQ]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 9223372036854775807
CYCLE
CACHE 50
The default constraint for the primary key on the table is defined as:
ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]
View 5 Replies
View Related
Feb 21, 2014
I need to be able to identify breaks in a sequence so I can evaluate the data more correctly. In the sample I have given I need to be able to identify the break in sequence at 69397576, ideally I would set that as a D. My query also needs to recognize that the 3 sequences following 69397576 are sequential and would belong to that set. so the out come would look like this.
id file_name page_follow
693975631555557564_22222221114014810D
693975641555557564_22222221114014810F
693975651555557564_22222221114014810F
693975661555557564_22222221114014810F
693975671555557564_22222221114014810F
693975681555557564_22222221114014810F
693975691555557564_22222221114014810F
693975761555557564_22222221114014810D
693975771555557564_22222221114014810F
693975781555557564_22222221114014810F
693975791555557564_22222221114014810F
here is some test data.
create table test1 (id INT
, [file_name] VARCHAR(100)
, page_follow CHAR(1));
go
[code]....
View 9 Replies
View Related
Jul 29, 2015
I'm looking to see if there is a way to populate starting number for the sequence from a max value of a table.
CREATE SEQUENCE test_seq
AS [int]
START WITH (select max(col1)+1000 from table1)
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
CACHE
GO
View 3 Replies
View Related
Oct 7, 2006
Hi, I'm new to sql server but have used oracle sequence numbers to create unique keys in my tables. I've been looking for examples on how to create a sequence type table in sql server and have also found that there is a column called identifier but am unsure how to use this... I created a table and used this column attempting to insert (thinking it would autoincrement) but it fails... Any insight into how I can accomplish a sequence type activity for unquie numeric ids would be helpful, Thank you for your help,Jay
View 2 Replies
View Related
Aug 4, 2014
We are running SQL Server 2012 on Windows Server 2008. Just wondering what type of actions would break the backup-chain or backup sequence? For instance, if you have tlog backups being taken every 10 minutes and you stop the SQL Server Service for say 30 minutes. Would this action break the backup chain? Or would everything return to normal once the SQL Server Service is restarted?
View 7 Replies
View Related
Sep 3, 2014
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
View 5 Replies
View Related
Jul 29, 2015
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
View 10 Replies
View Related
Jul 15, 2014
Today I have a very similar situation, only today I am dealing with missing text data, not numeric data.
DECLARE @MissingTextData TABLE
(
RowID int
,UserID int
, EmailAddress varchar(20)
,StreetAddress varchar(20)
[code]...
I would like to fill in the NULL columns with data from the other row, and then select the one row that is filled with all data. I was able to use MAX() for a numeric value, but I am really stumped on the text data. Everything that I have tried is not working.
View 1 Replies
View Related
Jan 20, 2015
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)
2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...
What I want is this (as is shown above). Is this possible?
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
View 7 Replies
View Related
Jul 25, 2013
I have recently started using replication in SQL 2012 SP1. When a stored procedure is altered on the source, the changes are replicated to the subscribers; however, the comment headers are removed at the subscribers. Due to the vast number of stored procedures I have, I do not want to move the comments below the Create Procedure statement. Are there any other ways to have comment header move with the stored procedures?
Here is what I am experiencing
Source SP
ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END
Destination SP
ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END
View 7 Replies
View Related
Jan 27, 2015
After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.
The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.
I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run
select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169
it gets me the SQL being run at that time not the one that causing the event.
View 3 Replies
View Related
Oct 24, 2015
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
View 7 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Aug 25, 2015
Somehow all of my sequences got reset to 1. The only thing that occured was the database was renamed.
SQL Server 2014
Is this a bug ?
View 5 Replies
View Related
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
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
Jul 13, 2006
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
View 4 Replies
View Related
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
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
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
