Finding Duplicate Records

CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>

Finding designation of an employee on a given date

Hi all,

I have two tables

CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [JPRO] (
[PromoID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[EffectiveDate] [smalldatetime] NOT NULL ,
[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION
[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION
) ON [PRIMARY]
GO

INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')

INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2002-15-11 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2003-03-01 00:00:00',8,7)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2002-01-04 00:00:00',20,22)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2005-05-01 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(5,'2001-10-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(6,'2001-08-01 00:00:00',55,NULL)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(7,'2003-10-01 00:00:00',11,8)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(8,'2001-09-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(9,'2002-01-05 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2002-11-01 00:00:00',24,25)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2003-11-15 00:00:00',23,24)

--
I wish to find the designation of employee on given date by using
promotion and master table . I am using the following query to get the
result

select isnull( ( select top 1 newdesigid from JPRO where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
JatinderHistory is usually modeled in durations with a (start_time, end_time)
pair; this lets you use a BETWEEN predicate for most of your queries.|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I wish to find the designation of employee on given date by using
> promotion and master table . I am using the following query to get the
> result
> select isnull( ( select top 1 newdesigid from JPRO where
> empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
> , (select desigid from empmast where empid=1) )
>
> It did give the result but looking for better method to solve this.

I don't see anything seriously wrong with that query. Here is an
alternate:

select Top 1 NewDesigID
from (select NewDesigID, EffectiveDate
from JPRO
where EmpID=7 and
EffectiveDate < '20050301'
union
select DESIGID, '19000101'
from JEMP
where EMPID=7) AS x
order by EffectiveDate desc

But I'm not sure in what way it would be "better".

If you are looking for a more ANSI way of doing it, you would have
to get the MAX(EffectiveDate) and then join back to the derived
table again. As you may guess, this is likely to be less effecient.
In SQL 2005, you could use a CTE (Common Table Expresssion) to avoid
repetition of the code for the derived table, but alas the query
plan is likely to be equally ineffecient.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Find the nearest date of one column of a table from another table

I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCd
Can anyone help me with this ?
Regards,
Rajeev RajputHi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
It is working but i have one issue with it.
When Lockdt is NULL i am getting this part as 1 -->>>>>
select top 1 ExchangeRate from ExchangeRates ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc
rather i should get it as NULL
Could you please help ?
Omnibuzz thanks in advance :)
Omnibuzz wrote:

> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc),0),0),0))),
4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/

Find subsequent days

I have the following table:
CREATE TABLE [dbo].[ShortStay](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_WLID] [int] NULL,
[SS_From] [smalldatetime] NOT NULL,
[SS_Till] [smalldatetime] NOT NULL)
There are these records for example:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-22', '2006-05-25')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (4, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-26', '2006-05-29')
I'm trying to find a query that returns the number of consequent days
based on a certain date:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
You get 2006-05-02 because the SS_Till is the day before 2006-05-05
that's why this row is ok.
You get 2006-05-29 because:
2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
2006-05-29
I hope this makes sense. So the days have to be consequent in order to
be able to be part of the count.
I have been trying to join the table to itself but I'm getting nowhere.
Thanks in advance,
Stijn Verrept.Consider creating an auxiliary Calendar table.
http://www.aspfaq.com/show.asp?id=2519
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Roji. P. Thomas wrote:

> Consider creating an auxiliary Calendar table.
> http://www.aspfaq.com/show.asp?id=2519
Thanks for the link, I already have a Calendar table but don't see how
this solves the subsequent problem, it's in none of the examples on the
site.
Kind regards,
Stijn Verrept.|||Stijn,
google up "How to simplify a query using a calendar table", hope that
helps|||How about something like this (note that it looks for consecutive days,
defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
aren't taken into consideration. Neither are overlapping dates.
DECLARE @.date DATETIME
SELECT @.date = '2006-05-09'
DECLARE @.SS_ID INT
SELECT @.SS_ID = 3
SELECT 'Begin' AS DateType, s1.Start
FROM (
SELECT MIN(SS_From) AS Start
FROM ShortStay
WHERE @.date BETWEEN SS_From AND SS_Till
AND SS_WLID = @.SS_ID
) s1
UNION ALL
SELECT 'End', s2.Finish
FROM
(
SELECT MAX(s1.SS_Till) AS Finish
FROM ShortStay s1, ShortStay s2
WHERE s1.SS_From = s2.SS_Till + 1
AND s1.SS_From <>
(
SELECT MIN(SS_From)
FROM ShortStay
WHERE SS_WLID = @.SS_ID
AND @.date BETWEEN SS_From AND SS_Till
)
AND s1.SS_WLID = s2.SS_WLID
AND s1.SS_WLID = @.SS_ID
) s2
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...
>I have the following table:
> CREATE TABLE [dbo].[ShortStay](
> [SS_ID] [int] IDENTITY(1,1) NOT NULL,
> [SS_WLID] [int] NULL,
> [SS_From] [smalldatetime] NOT NULL,
> [SS_Till] [smalldatetime] NOT NULL)
>
> There are these records for example:
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-02', '2006-05-04')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-22', '2006-05-25')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (4, '2006-05-05', '2006-05-21')
> Insert into ShortStay (SS_WLID, SS_From, SS_Till)
> values (3, '2006-05-26', '2006-05-29')
> I'm trying to find a query that returns the number of consequent days
> based on a certain date:
> So if I input: '2006-05-09' and SS_WLID = 3
> then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
> You get 2006-05-02 because the SS_Till is the day before 2006-05-05
> that's why this row is ok.
> You get 2006-05-29 because:
> 2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
> 2006-05-29
> I hope this makes sense. So the days have to be consequent in order to
> be able to be part of the count.
> I have been trying to join the table to itself but I'm getting nowhere.
>
> --
> Thanks in advance,
> Stijn Verrept.|||Hi There,
You may like to try this!!
I assume that SS_From is smaller than SS_Till
Select Min(SS_From),Max(SS_Till) from ShortStay where SS_WLID=3
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_From,112)
And convert(varchar(6),'20060509',112) =
convert(varchar(6),SS_Till,112)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Mike C# wrote:
> How about something like this (note that it looks for consecutive days,
> defined exactly as SS_From = prior SS_Till + 1). Hours, minutes, etc.,
> aren't taken into consideration. Neither are overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
> SELECT 'Begin' AS DateType, s1.Start
> FROM (
> SELECT MIN(SS_From) AS Start
> FROM ShortStay
> WHERE @.date BETWEEN SS_From AND SS_Till
> AND SS_WLID = @.SS_ID
> ) s1
> UNION ALL
> SELECT 'End', s2.Finish
> FROM
> (
> SELECT MAX(s1.SS_Till) AS Finish
> FROM ShortStay s1, ShortStay s2
> WHERE s1.SS_From = s2.SS_Till + 1
> AND s1.SS_From <>
> (
> SELECT MIN(SS_From)
> FROM ShortStay
> WHERE SS_WLID = @.SS_ID
> AND @.date BETWEEN SS_From AND SS_Till
> )
> AND s1.SS_WLID = s2.SS_WLID
> AND s1.SS_WLID = @.SS_ID
> ) s2
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:0--dnTs9fOP8yD_ZRVny0A@.scarlet.biz...|||Mike C# wrote:

> How about something like this (note that it looks for consecutive
> days, defined exactly as SS_From = prior SS_Till + 1). Hours,
> minutes, etc., aren't taken into consideration. Neither are
> overlapping dates.
> DECLARE @.date DATETIME
> SELECT @.date = '2006-05-09'
> DECLARE @.SS_ID INT
> SELECT @.SS_ID = 3
Hi Mike,
Thanks for the reply! This one is very close but it doesn't look for
any prior consecutive rows.
In the examply I gave:
So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.
Your query returns '2006-05-05' and '2006-05-29'
It doesn't consider the row:
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Thanks again,
Stijn Verrept.|||People will tell you to use a Calendar table. But your real problem is
a bad design. You have no key (IDENTITY cannot ever be a key! Quit
mimicing a sequential file), and your "wl_id" can be NULL so the query
is impossible to answer. You also have no constraints and use
proprietary data types. Then on top of all of that, you have attribute
splitting -- the fact of a continous stay is spread over many rows.
Why did you use a singular name for a table with more than one element
in it? You have no idea what an identifier is and stuck it on
everything. Your table should look more like this:
CREATE TABLE ShortStays
(wl_id INTEGER NOT NULL,
arrival_date DATETIME NOT NULL,
depart_date DATETIME NOT NULL,
CHECK(arrival_date < depart_date),
PRIMARY KEY (wl_id, arrival_date));
When a stay is extended, you update it instead of mimicking a paper
hotel register form. Ideally, you should have constraint to prevent
overlaps, but that is hard to do in SQL Server since it still lacks
much of the SQL-92 features. You can do it with a TRIGGER or with an
updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
Please learn the differences between rows and records. Fail to know
that lead to attribute splitting.|||--CELKO-- wrote:

> People will tell you to use a Calendar table. But your real problem
> is a bad design. You have no key (IDENTITY cannot ever be a key!
> Quit mimicing a sequential file), and your "wl_id" can be NULL so the
> query is impossible to answer. You also have no constraints and use
> proprietary data types. Then on top of all of that, you have
> attribute splitting -- the fact of a continous stay is spread over
> many rows.
Well Joe, the table creation script given here is just a simplified
version of the real table so hold your horses. People can stay in
different rooms and can be transferred from one room to another so
updating instead of inserting is not an option here. I didn't include
the column of the room because for this query it is irrelevant!

> When a stay is extended, you update it instead of mimicking a paper
> hotel register form. Ideally, you should have constraint to prevent
> overlaps, but that is hard to do in SQL Server since it still lacks
> much of the SQL-92 features. You can do it with a TRIGGER or with an
> updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.
I already have a trigger that prevents overlap so don't worry about
that.

> Please learn the differences between rows and records. Fail to know
> that lead to attribute splitting.
LOL
"In the context of a relational database, a rowalso called a record or
tuplerepresents a single, implicitly structured data item in a table."
Wikipedia.
Will learning the naming difference really solve my query? I think not.
Thanks for your very helpful reply!
Kind regards,
Stijn Verrept.

Find gaps in sequential numbering

I would like to find the missing numbers in a sequential increase in a
column
So for instance
Create table #test
(numcol int)
insert #test values(1)
insert #test values(2)
insert #test values(3)
insert #test values(4)
insert #test values(7)
insert #test values(8)
insert #test values(9)
insert #test values(11)
insert #test values(100)
Would be nice to get the range of missing values such as
Minvalue Maxvalue
5 6
10 10
12 99
ThanksHere is one solution:
SELECT minval + 1 AS MinValue,
maxval - 1 AS MaxValue
FROM
(SELECT numcol,
(SELECT MIN(numcol)
FROM #test AS T2
WHERE T2.numcol > T1.numcol)
FROM #test AS T1) AS T3(minval, maxval)
WHERE maxval - minval > 1
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hassan
select
min(i) as low,
max(i) as high
from (
select
N1.num,
count(N2.num) - N1.num
from Numbers as N1, Numbers as N2
where N2.num <= N1.num
group by N1.num
) as N(i,gp)
group by gp
"Hassan" <hassan@.hotmail.com> wrote in message
news:u5PlkdgXIHA.6140@.TK2MSFTNGP02.phx.gbl...
>I would like to find the missing numbers in a sequential increase in a
>column
> So for instance
> Create table #test
> (numcol int)
> insert #test values(1)
> insert #test values(2)
> insert #test values(3)
> insert #test values(4)
> insert #test values(7)
> insert #test values(8)
> insert #test values(9)
> insert #test values(11)
> insert #test values(100)
>
> Would be nice to get the range of missing values such as
> Minvalue Maxvalue
> 5 6
> 10 10
> 12 99
> Thanks
>

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:

> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=p...&rnu
m=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:
> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=pl&lr=&ie=UTF-8&oe=UTF-8&th=a3eb815a529ae1c5&rnum=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

FilterRows

Hi, I posted this a while back but had no response. I wonder if anyone can
help.
I am using transactional replication.
CREATE TABLE T1 (LogID INT, Quantity INT)
Table T1 is an article to be published. I only want to publish rows that
have a quantity > 5. The initial snapshot filters replicates all rows that
meet the criteria.
However, when a quantity changes to above 5, ie becomes eligible to be
replicated, this row is not replicated at the subscriber.
Publisher
Table T1 all rows with Quantities 0 - 10
Subscriber
Table T1 only rows with Quantities 5 - 10
I run the following update at the publisher
UPDATE T1
SET Quantity = 6
WHERE Quantity = 4
These rows do not get replicated.
Am i right in thinking this is by design and that it takes the exact DML
statement from the publisher and runs it on the subsciber?
Does anyone have a workaround for this or do i have to replicate the entire
table?
Thanks again
Rich
I just set this up and rows the were < than 5 and changed to be greater than
5 were replicated...
"Rich" wrote:

> Hi, I posted this a while back but had no response. I wonder if anyone can
> help.
> I am using transactional replication.
> CREATE TABLE T1 (LogID INT, Quantity INT)
> Table T1 is an article to be published. I only want to publish rows that
> have a quantity > 5. The initial snapshot filters replicates all rows that
> meet the criteria.
> However, when a quantity changes to above 5, ie becomes eligible to be
> replicated, this row is not replicated at the subscriber.
> Publisher
> Table T1 all rows with Quantities 0 - 10
> Subscriber
> Table T1 only rows with Quantities 5 - 10
> I run the following update at the publisher
> UPDATE T1
> SET Quantity = 6
> WHERE Quantity = 4
> These rows do not get replicated.
> Am i right in thinking this is by design and that it takes the exact DML
> statement from the publisher and runs it on the subsciber?
> Does anyone have a workaround for this or do i have to replicate the entire
> table?
> Thanks again
> Rich
>
>
|||I've tried again and this time its worked. Thanks!
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:DB145F6B-5C28-48F1-B714-2EED6C02F8E7@.microsoft.com...[vbcol=seagreen]
>I just set this up and rows the were < than 5 and changed to be greater
>than
> 5 were replicated...
> "Rich" wrote: