I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith
Showing posts with label missing. Show all posts
Showing posts with label missing. Show all posts
Friday, March 30, 2012
finding missing number
Hi Guys,
I am using sql server 2000 and i want to find missing
number between 1 and 1000 in a table.
what is the query for that?
pls advice me.
RGDS
BijuAssuming you have another table called Numbers that contains all the
required numbers:
SELECT num
FROM Numbers
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE num = Numbers.num)
AND num BETWEEN 1 AND 1000
David Portas
SQL Server MVP
--|||If there's only one Num Missing, this will work...
Select Num - 1
From <TableName> T
Where Num Between 1 And 1001
And Not Exists
(Select * From <TableName>
Where Num = T.Num - 1)
If there's a possibility of multiple Sequential numbers missing,
asin
1
2
3
6
7
...
then Use David's solution
"bijupg" wrote:
> Hi Guys,
> I am using sql server 2000 and i want to find missing
> number between 1 and 1000 in a table.
> what is the query for that?
> pls advice me.
> RGDS
> Biju
>|||Or use SQL Server 2000's nice TABLE variable to create a control table:
-- Use the Edit menu's 'Replace Template Parameters...' command to replace
the your_table/your_field values
DECLARE @.control TABLE ( control_no INT PRIMARY KEY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Add control numbers to temp table
WHILE @.i Between 1 And 1000
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List missing values
SELECT t.*
FROM @.control t
LEFT JOIN <your_table, SYSNAME, > c ON t.control_no = c.<your_field,
SYSNAME, >
WHERE c.control_no Is Null|||If you want the starting of each gap, you can do:
SELECT nbr + 1
FROM tbl
WHERE NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.nbr = tbl.nbr + 1 )
AND nbr <= 1000 ;
If you want the start & end of each set of missing numbers:
SELECT t1.Nbr + 1 AS "start",
MIN( t2.Nbr ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN( t2.Nbr ) - t1.Nbr > 1;
If you want to list all the missing numbers, following the suggestions to
use a table of sequential numbers.
Anith
I am using sql server 2000 and i want to find missing
number between 1 and 1000 in a table.
what is the query for that?
pls advice me.
RGDS
BijuAssuming you have another table called Numbers that contains all the
required numbers:
SELECT num
FROM Numbers
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE num = Numbers.num)
AND num BETWEEN 1 AND 1000
David Portas
SQL Server MVP
--|||If there's only one Num Missing, this will work...
Select Num - 1
From <TableName> T
Where Num Between 1 And 1001
And Not Exists
(Select * From <TableName>
Where Num = T.Num - 1)
If there's a possibility of multiple Sequential numbers missing,
asin
1
2
3
6
7
...
then Use David's solution
"bijupg" wrote:
> Hi Guys,
> I am using sql server 2000 and i want to find missing
> number between 1 and 1000 in a table.
> what is the query for that?
> pls advice me.
> RGDS
> Biju
>|||Or use SQL Server 2000's nice TABLE variable to create a control table:
-- Use the Edit menu's 'Replace Template Parameters...' command to replace
the your_table/your_field values
DECLARE @.control TABLE ( control_no INT PRIMARY KEY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Add control numbers to temp table
WHILE @.i Between 1 And 1000
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List missing values
SELECT t.*
FROM @.control t
LEFT JOIN <your_table, SYSNAME, > c ON t.control_no = c.<your_field,
SYSNAME, >
WHERE c.control_no Is Null|||If you want the starting of each gap, you can do:
SELECT nbr + 1
FROM tbl
WHERE NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.nbr = tbl.nbr + 1 )
AND nbr <= 1000 ;
If you want the start & end of each set of missing numbers:
SELECT t1.Nbr + 1 AS "start",
MIN( t2.Nbr ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN( t2.Nbr ) - t1.Nbr > 1;
If you want to list all the missing numbers, following the suggestions to
use a table of sequential numbers.
Anith
finding missing dates in a sequence
I have 200 tables that have data entered into the daily. I need to identify
all dates that are missing from the tables.
for example:
date column1 column2
1/1/2005 5 20
2/1/2005 67 35
4/1/2005 3 17
5/1/2005 9 6
8/1/2005 7 99
I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
can you please assist?
jayi should have also said i do not want to have a lookup table, ie a table tha
t
permenantly stores dates.
cheers jay
jay
"jay" wrote:
> I have 200 tables that have data entered into the daily. I need to identi
fy
> all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
> --
> jay|||How about a temporary lookup table?
DROP TABLE #tmp_data
CREATE TABLE #tmp_data ( entry_date DATETIME PRIMARY KEY, col1 INT NOT NULL,
col2 INT NOT NULL )
SET NOCOUNT ON
INSERT INTO #tmp_data VALUES ( '20050101', 5, 20 )
INSERT INTO #tmp_data VALUES ( '20050102', 67, 35 )
INSERT INTO #tmp_data VALUES ( '20050104', 3, 17 )
INSERT INTO #tmp_data VALUES ( '20050105', 9, 6 )
INSERT INTO #tmp_data VALUES ( '20050108', 7, 99 )
DROP TABLE #tmp_lookup
CREATE TABLE #tmp_lookup ( entry_date DATETIME PRIMARY KEY )
DECLARE @.i INT
DECLARE @.stop INT
DECLARE @.min_date DATETIME
DECLARE @.max_date DATETIME
-- Initialise
SET @.i = 0
-- Calculate the range of dates to be added to the temp lookup table
SELECT
@.min_date = MIN( entry_date ),
@.max_date = MAX( entry_date )
FROM #tmp_data
SET @.stop = DATEDIFF( day, @.min_date, @.max_date ) + 1
-- Add the dates to the lookup table
WHILE @.i < @.stop
BEGIN
INSERT INTO #tmp_lookup SELECT DATEADD( day, @.i, @.min_date )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- Show the missing values
SELECT t1.*
FROM #tmp_lookup t1
LEFT JOIN #tmp_data t2 ON t1.entry_date = t2.entry_date
WHERE t2.entry_date IS NULL
This could even be wrapped in a parameterized stored procedure.
Let me know how you get on.
Damien
"jay" wrote:
> i should have also said i do not want to have a lookup table, ie a table t
hat
> permenantly stores dates.
> cheers jay
> --
> jay
>
> "jay" wrote:
>|||jay <jay@.discussions.microsoft.com> wrote:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
It's difficult to generate data that isn't there with an SQL query. Your
best bet is to write a stored procedure that does the job.
Another approach is to find wholes in the data sequence. Not exactly what
you want but this might work (untested):
select t1.ts, t2.mts
from tab t1, (
select min(ts) as mts
from tab tx
where tx.ts > t1.ts
) t2
where datediff('dd', t1.ts, t2.mts) > 1
order by t1.ts
Kind regards
robert|||"jay" <jay@.discussions.microsoft.com> wrote in message
news:BE8860E2-EB68-4EA0-A389-B5D53996608E@.microsoft.com...
> "jay" wrote:
>
to identify
> i should have also said i do not want to have a lookup table, ie a
table that
> permenantly stores dates.
> cheers jay
> --
> jay
>
jay,
Why?
Books on SQL and RDBMs recommend it. Many top names on the subjects
recommend it. It's the way to go.
Sincerely,
Chris O.|||>i should have also said i do not want to have a lookup table, ie a table
>that
> permenantly stores dates.
WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
bring any of that flu vaccine near me!
A calendar table seems to be exactly what you need, and is going to be far
more efficient than looping solutions or generating your entire date range
on the fly every time. Please read http://www.aspfaq.com/2519|||because...the calendar table would need to be kept up to date...who would do
that? you do not know the restrictions on the situation so please do not so
easily pass judgement when you do not know all the issues!
I appreciate any help NOT judgements from ill informed people!
cheers jay
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:efmMbhe$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
> bring any of that flu vaccine near me!
> A calendar table seems to be exactly what you need, and is going to be far
> more efficient than looping solutions or generating your entire date range
> on the fly every time. Please read http://www.aspfaq.com/2519
>
>|||"Jay Walker" <jay@.bladecomputing.com.au> wrote in message
news:eCF4kyf$FHA.3064@.TK2MSFTNGP10.phx.gbl...
> because...the calendar table would need to be kept up to
date...who would do
> that? you do not know the restrictions on the situation so please
do not so
> easily pass judgement when you do not know all the issues!
> I appreciate any help NOT judgements from ill informed people!
> cheers jay
>
Jay Walker,
Aaron is among the best informed people around here.
Also, you did not explain the restrictions of your situation. I
asked, earlier, what those restrictions were, and still have no
answer.
You mentioned: "because...the calendar table would need to be kept
up to date...who would do that?"
What do you mean? A calendar table is loaded, and that is that.
There is no "maintenance" (at least not in our lifetimes).
Sincerely,
Chris O.|||Chris2 (rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com) writes:
> What do you mean? A calendar table is loaded, and that is that.
> There is no "maintenance" (at least not in our lifetimes).
Depends on what you fill it with. If you fill it with dates, and only
dates, you can fill it up until 2150 or so. And for Jay's problem this
would do.
But for a more elaborate calendar that keeps track of business days,
there is of course maintenance to do, as holidays are changed. For instance,
my pocket calendar for 2005 printed May 16th as red, and June 6th as
black, when the days come, May 16th was a busiess day and June 6th was not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jay (jay@.discussions.microsoft.com) writes:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
Here is a query that does not use a calendar table. It will not list all
dates though, only the first and last date in an interval. Also, performance
is not likely to be fantastic. To that end a calendar table will be
better. For this reason, I'm including a script that fills up a dates
table with all dates from 1990 to 2149.
Here is the query (runs in Northwind):
SELECT gapstart, MIN(gapend)
FROM (select gapstart = dateadd(DAY, 1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, 1, A.OrderDate))) X
JOIN (select gapend = dateadd(DAY, -1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, -1, A.OrderDate))) Y
ON gapstart <= gapend
GROUP BY gapstart
ORDER BY gapstart
And here is the script:
CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)
-- Make sure it's empty.
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) +
' rows into #numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
all dates that are missing from the tables.
for example:
date column1 column2
1/1/2005 5 20
2/1/2005 67 35
4/1/2005 3 17
5/1/2005 9 6
8/1/2005 7 99
I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
can you please assist?
jayi should have also said i do not want to have a lookup table, ie a table tha
t
permenantly stores dates.
cheers jay
jay
"jay" wrote:
> I have 200 tables that have data entered into the daily. I need to identi
fy
> all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
> --
> jay|||How about a temporary lookup table?
DROP TABLE #tmp_data
CREATE TABLE #tmp_data ( entry_date DATETIME PRIMARY KEY, col1 INT NOT NULL,
col2 INT NOT NULL )
SET NOCOUNT ON
INSERT INTO #tmp_data VALUES ( '20050101', 5, 20 )
INSERT INTO #tmp_data VALUES ( '20050102', 67, 35 )
INSERT INTO #tmp_data VALUES ( '20050104', 3, 17 )
INSERT INTO #tmp_data VALUES ( '20050105', 9, 6 )
INSERT INTO #tmp_data VALUES ( '20050108', 7, 99 )
DROP TABLE #tmp_lookup
CREATE TABLE #tmp_lookup ( entry_date DATETIME PRIMARY KEY )
DECLARE @.i INT
DECLARE @.stop INT
DECLARE @.min_date DATETIME
DECLARE @.max_date DATETIME
-- Initialise
SET @.i = 0
-- Calculate the range of dates to be added to the temp lookup table
SELECT
@.min_date = MIN( entry_date ),
@.max_date = MAX( entry_date )
FROM #tmp_data
SET @.stop = DATEDIFF( day, @.min_date, @.max_date ) + 1
-- Add the dates to the lookup table
WHILE @.i < @.stop
BEGIN
INSERT INTO #tmp_lookup SELECT DATEADD( day, @.i, @.min_date )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- Show the missing values
SELECT t1.*
FROM #tmp_lookup t1
LEFT JOIN #tmp_data t2 ON t1.entry_date = t2.entry_date
WHERE t2.entry_date IS NULL
This could even be wrapped in a parameterized stored procedure.
Let me know how you get on.
Damien
"jay" wrote:
> i should have also said i do not want to have a lookup table, ie a table t
hat
> permenantly stores dates.
> cheers jay
> --
> jay
>
> "jay" wrote:
>|||jay <jay@.discussions.microsoft.com> wrote:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
It's difficult to generate data that isn't there with an SQL query. Your
best bet is to write a stored procedure that does the job.
Another approach is to find wholes in the data sequence. Not exactly what
you want but this might work (untested):
select t1.ts, t2.mts
from tab t1, (
select min(ts) as mts
from tab tx
where tx.ts > t1.ts
) t2
where datediff('dd', t1.ts, t2.mts) > 1
order by t1.ts
Kind regards
robert|||"jay" <jay@.discussions.microsoft.com> wrote in message
news:BE8860E2-EB68-4EA0-A389-B5D53996608E@.microsoft.com...
> "jay" wrote:
>
to identify
> i should have also said i do not want to have a lookup table, ie a
table that
> permenantly stores dates.
> cheers jay
> --
> jay
>
jay,
Why?
Books on SQL and RDBMs recommend it. Many top names on the subjects
recommend it. It's the way to go.
Sincerely,
Chris O.|||>i should have also said i do not want to have a lookup table, ie a table
>that
> permenantly stores dates.
WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
bring any of that flu vaccine near me!
A calendar table seems to be exactly what you need, and is going to be far
more efficient than looping solutions or generating your entire date range
on the fly every time. Please read http://www.aspfaq.com/2519|||because...the calendar table would need to be kept up to date...who would do
that? you do not know the restrictions on the situation so please do not so
easily pass judgement when you do not know all the issues!
I appreciate any help NOT judgements from ill informed people!
cheers jay
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:efmMbhe$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
> bring any of that flu vaccine near me!
> A calendar table seems to be exactly what you need, and is going to be far
> more efficient than looping solutions or generating your entire date range
> on the fly every time. Please read http://www.aspfaq.com/2519
>
>|||"Jay Walker" <jay@.bladecomputing.com.au> wrote in message
news:eCF4kyf$FHA.3064@.TK2MSFTNGP10.phx.gbl...
> because...the calendar table would need to be kept up to
date...who would do
> that? you do not know the restrictions on the situation so please
do not so
> easily pass judgement when you do not know all the issues!
> I appreciate any help NOT judgements from ill informed people!
> cheers jay
>
Jay Walker,
Aaron is among the best informed people around here.
Also, you did not explain the restrictions of your situation. I
asked, earlier, what those restrictions were, and still have no
answer.
You mentioned: "because...the calendar table would need to be kept
up to date...who would do that?"
What do you mean? A calendar table is loaded, and that is that.
There is no "maintenance" (at least not in our lifetimes).
Sincerely,
Chris O.|||Chris2 (rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com) writes:
> What do you mean? A calendar table is loaded, and that is that.
> There is no "maintenance" (at least not in our lifetimes).
Depends on what you fill it with. If you fill it with dates, and only
dates, you can fill it up until 2150 or so. And for Jay's problem this
would do.
But for a more elaborate calendar that keeps track of business days,
there is of course maintenance to do, as holidays are changed. For instance,
my pocket calendar for 2005 printed May 16th as red, and June 6th as
black, when the days come, May 16th was a busiess day and June 6th was not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jay (jay@.discussions.microsoft.com) writes:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
Here is a query that does not use a calendar table. It will not list all
dates though, only the first and last date in an interval. Also, performance
is not likely to be fantastic. To that end a calendar table will be
better. For this reason, I'm including a script that fills up a dates
table with all dates from 1990 to 2149.
Here is the query (runs in Northwind):
SELECT gapstart, MIN(gapend)
FROM (select gapstart = dateadd(DAY, 1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, 1, A.OrderDate))) X
JOIN (select gapend = dateadd(DAY, -1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, -1, A.OrderDate))) Y
ON gapstart <= gapend
GROUP BY gapstart
ORDER BY gapstart
And here is the script:
CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)
-- Make sure it's empty.
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) +
' rows into #numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Thursday, March 29, 2012
Finding jobs that wont run due to Daylight Savings Time?
Since this weekend is Daylight Savings, we want to make sure of any
jobs that won't run due to the "missing hour".
jobs that won't run due to the "missing hour".
I have written a small query against sysjobschedules that tells me
which ones have a schedule that runs between 2 and 3. However, it
doesn't include any jobs that run every X minutes/hours, and so might
be run during that time. (i.e. a job that starts at midnight and runs
until 6, running every 2 hours)
Is there any (easy) way to determine this? I might be able to build a
function that uses the other fields in sysjobschedules to give a list
of times that the job will run, but I haven't gotten to that point yet.
Figured someone might have something already, rather than reinvent the
wheel.
Thanks,
MichaelI don't believe there's any easy way, but sysjobhistory might be a good
starting point - if a job ran between 2 and 3 in the past, it will
probably do so again. You could join on sysjobschedules to narrow it
down to daily schedules, schedules on a certain weekday or date etc.
And you can check the next_run_date column as well, of course.
Simon
Friday, March 9, 2012
Find Non-Matching End Points
Hello,
I'm trying to manipulate some data to find occurances where a certain end point it missing. For example, in my table I have the following record:
SHAPE_ID SEQ_NUM X_COORDINATE Y_COORDINATE
For shape_ id = 82 (I have thousands of these to process), Seq_num = 0 is the beginning of the item, while seq_num 9 is the end point. The end point (x_coordinate/y_coordinate)i.e. Seq_num=9 is supposed to match the begin x_coordinate/y_coordinate values, but in this example, they do not match.
How do I find each occurance of the same shape_id where the end seq_num (#9, or whatever number is has been incremented to) x_coordinate/y_cooordinate, does not match the begin x_coordinate/y_coordinate seq_num (#0) ?
As well, if any of these records are indeed found, how do I increment to the next highest sequence number and update that record with the begin point (seq_num=0) x_coordinate/y_coordinate values?
I would appreciate any help with this problem....Thx. in advance...Walt
Moving to the Transact-SQL forum. I believe that's better equipped to provide an answer.
|||
Walt:
Is this close to what you are looking for:
|||set nocount on
declare @.sample table
( shape_id integer,
seq_num integer,
x_coordinate numeric (12,3),
y_coordinate numeric (12,3),
primary key (shape_id, seq_num)
)
insert into @.sample values (82, 0, 606494.606, 4820354.142 )
insert into @.sample values (82, 1, 606487.996, 4820344.571 )
insert into @.sample values (82, 2, 606512.455, 4820319.828 )
insert into @.sample values (82, 3, 606590.877, 4820420.208 )
insert into @.sample values (82, 4, 606611.069, 4820404.432 )
insert into @.sample values (82, 5, 606634.138, 4820434.514 )
insert into @.sample values (82, 6, 606614.812, 4820449.179 )
insert into @.sample values (82, 7, 606670.587, 4820520.016 )
insert into @.sample values (82, 8, 606638.161, 4820546.216 )
insert into @.sample values (82, 9, 606500.606, 4820400.142 )
--insert into @.sample values (82,10, 606494.606, 4820354.142 )--select * from @.sample
select a.shape_id,
a.seq_num,
a.x_coordinate as [Last X],
a.y_coordinate as [Last Y],
z.x_coordinate as [Zero X],
z.y_coordinate as [Zero Y]
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)-- shape_id seq_num Last X Last Y Zero X Zero Y
-- -- -- -- -- -- --
-- 82 9 606500.606 4820400.142 606494.606 4820354.142update @.sample
set x_coordinate = a.x_coordinate,
y_coordinate = a.y_coordinate
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)select * from @.sample where seq_num = 0
-- shape_id seq_num x_coordinate y_coordinate
-- -- -- -- --
-- 82 0 606500.606 4820400.142
Dave
Dave,
Thanks for your prompt reply. I'll hopefully give your code a try tomorrow.
Is this a sql statement that I can paste into the Sql View of a new sql statement?
Sorry for such a dumb question......Walt
|||Dave,
I'm in the process of importing my table into my Oracle schema and am just in the process of creating a new table.
I have over 500,000 records to load so I'll give your reply a try.
thx again....Walt
find missing/deleted records?
I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thx
One way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thx
One way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP
find missing/deleted records?
I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--
find missing/deleted records?
I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxMansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--
find missing/deleted records?
I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...[vbc
ol=seagreen]
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>[/vbcol]|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP
--
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...[vbc
ol=seagreen]
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>[/vbcol]|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP
--
Find Missing data from table
Here is an issue that has me stumped for the past few days. I have a
table called MerchTran. Among various columns, the relevant columns for
this issue are:
table called MerchTran. Among various columns, the relevant columns for
this issue are:
FileDate datetime
, SourceTable varchar(25)
SQL statement:
SELECT DISTINCT
FileDate
, SourceTable
FROM
MerchTran
ORDER BY
FileDate
, SourceTable
Data looks like this:
FileDate DataSource
-----------
2005-02-13 00:00:00.000S1
2005-02-13 00:00:00.000S2
2005-02-13 00:00:00.000S3
2005-02-14 00:00:00.000S1
2005-02-14 00:00:00.000S2
2005-02-14 00:00:00.000S3
2005-02-15 00:00:00.000S2
2005-02-15 00:00:00.000S3
2005-02-16 00:00:00.000S1
2005-02-16 00:00:00.000S2
2005-02-16 00:00:00.000S3
2005-02-17 00:00:00.000S1
2005-02-17 00:00:00.000S2
2005-02-18 00:00:00.000S1
2005-02-18 00:00:00.000S2
2005-02-18 00:00:00.000S3
2005-02-19 00:00:00.000S1
2005-02-19 00:00:00.000S3
We run a daily process that inserts data in to this table everyday for
all 3 sources S1, S2, S3
Notice how some data is missing indicating the import process for that
particular source failed.
Example: Missing record
2005-02-15 00:00:00.000S1
2005-02-17 00:00:00.000S3
2005-02-19 00:00:00.000S2
Can someone please help me with a SQL Statement that will return me the
3 missing records as above.
Thanks in advance for all your help!
DBA in distress!
Vishal[posted and mailed, please reply in news]
SQLJunkie (vsinha73@.gmail.com) writes:
> Here is an issue that has me stumped for the past few days. I have a
> table called MerchTran. Among various columns, the relevant columns for
> this issue are:
>...
> We run a daily process that inserts data in to this table everyday for
> all 3 sources S1, S2, S3
> Notice how some data is missing indicating the import process for that
> particular source failed.
> Example: Missing record
> 2005-02-15 00:00:00.000 S1
> 2005-02-17 00:00:00.000 S3
> 2005-02-19 00:00:00.000 S2
> Can someone please help me with a SQL Statement that will return me the
> 3 missing records as above.
The below assumes that daily means daily and not only Monday to Friday.
It will catch if a date is missing entirely, although if the first or
last day is missing.
CREATE TABLE demo (date datetime NOT NULL,
src char(2) NOT NULL,
CONSTRAINT pk_demo PRIMARY KEY (date, src))
go
-- Creates a numbers table in a somewhat casual way that
-- is not guaranteed to work. The MAXDOP turns of parallelism
-- to improve our chances.
SELECT TOP 8000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
OPTION (MAXDOP 1)
go
INSERT demo (date, src)
SELECT '2005-02-13 00:00:00.000', 'S1' UNION
SELECT '2005-02-13 00:00:00.000', 'S2' UNION
SELECT '2005-02-13 00:00:00.000', 'S3' UNION
SELECT '2005-02-14 00:00:00.000', 'S1' UNION
SELECT '2005-02-14 00:00:00.000', 'S2' UNION
SELECT '2005-02-14 00:00:00.000', 'S3' UNION
SELECT '2005-02-15 00:00:00.000', 'S2' UNION
SELECT '2005-02-15 00:00:00.000', 'S3' UNION
SELECT '2005-02-16 00:00:00.000', 'S1' UNION
SELECT '2005-02-16 00:00:00.000', 'S2' UNION
SELECT '2005-02-16 00:00:00.000', 'S3' UNION
SELECT '2005-02-17 00:00:00.000', 'S1' UNION
SELECT '2005-02-17 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S1' UNION
SELECT '2005-02-18 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S3' UNION
SELECT '2005-02-19 00:00:00.000', 'S1' UNION
SELECT '2005-02-19 00:00:00.000', 'S3'
go
SELECT *
FROM (SELECT date = dateadd(DAY, n - 1, mindate)
FROM numbers n
CROSS JOIN (SELECT mindate = MIN(date),
maxdate = MAX(date)
FROM demo) d
WHERE n BETWEEN 1 AND datediff(DAY, mindate, maxdate) + 1) AS a
CROSS JOIN (SELECT src = 'S1' UNION SELECT 'S2' UNION SELECT 'S3') AS s
WHERE NOT EXISTS (SELECT *
FROM demo
WHERE demo.date = a.date
AND demo.src = s.src)
go
DROP TABLE numbers
DROP TABLE demo
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the quick response Erland. I see what you are trying to do,
but I have data in the table starting 2003. Would be a lot of work if I
have to insert 3 records in table numbers for everyday (including
saturday and sunday) starting 2003. But I will try this nonetheless.
Thanks!
Vishal|||SQLJunkie (vsinha73@.gmail.com) writes:
> Thanks for the quick response Erland. I see what you are trying to do,
> but I have data in the table starting 2003. Would be a lot of work if I
> have to insert 3 records in table numbers for everyday (including
> saturday and sunday) starting 2003. But I will try this nonetheless.
Numbers need to have as many rows as there days in the timespan.
In fact, it does have to be a numbers table. I used a numbers, because
1) it was easier to compose one on the fly.
2) a numbers table is applicable other problems as well.
However, in our system we don't have a numbers - but we have a dates table,
and here is how we fill it in a safe way:
CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE Readings
(collection_date DATETIME NOT NULL,
source CHAR(2) NOT NULL
CHECK(source IN ('S1', 'S2', 'S3')),
PRIMARY KEY (collection_date, source));
This is a quick way to see what you do have on the days with missing
data:
CREATE VIEW Shortdates(collection_date, source)
AS
SELECT collection_date, source
FROM Readings
GROUP BY collection_date
HAVING COUNT(*) < 3;
We can now use this view or make it into a derived table. We will also
another table, which probably exists already.
CREATE TABLE Sources (sources CHAR(2) NOT NULL PRIMARY KEY, ..);
SELECT R1.collection_date, S1.source
FROM Readings AS R1,
Sources AS S1
WHERE S1.source
NOT IN (SELECT R2.source
FROM Shortdates AS R2 -- or use a derived table
WHERE R1.collection_date
= R2.collection_date);
-- Untested
Wednesday, March 7, 2012
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
>
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
>
Friday, February 24, 2012
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.
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.
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.
Sunday, February 19, 2012
Filters & Parameters pane missing in MDX Query Designer
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?
Filters & Parameters pane missing in MDX Query Designer
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?
Subscribe to:
Posts (Atom)