Data that is absent

I have a single table which we use to keep track of data from different
physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
column is datetime and the rest are char. Every day each machine we collect
data on gets a row of information: The date of the collection, the machine
name, & any notes on the machine.
Since everyday there is a new row for each machine, what I would like to do
is in Query Analyzer be able to tell if there are any dates missing. Then we
could manually check the machine and see why it is not reporting.
I read an article that said to use the EXISTS command to select data based
on the preence or absence of values. I tried a couple different code samples
,
none showed signs of progress. If I were coding in VB I would use an array
and compare the distinct values. Is there a way in SQL to show values absent
from a known sequence.
Thanks
vmI assume you have a table with one row for each machine you want to manage.
You can do something like this.
tableA = Machine Names
tableB = Holds daily rows
SELECT a.Name FROM tableA AS a
WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
Andrew J. Kelly SQL MVP
"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>I have a single table which we use to keep track of data from different
> physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
> column is datetime and the rest are char. Every day each machine we
> collect
> data on gets a row of information: The date of the collection, the machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would like to
> do
> is in Query Analyzer be able to tell if there are any dates missing. Then
> we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select data based
> on the preence or absence of values. I tried a couple different code
> samples,
> none showed signs of progress. If I were coding in VB I would use an array
> and compare the distinct values. Is there a way in SQL to show values
> absent
> from a known sequence.
> Thanks
> vm|||Actually all of the data is in a single table. Each row in the table contain
s
the date, machine name, and any notes for the machine in different columns.
The database is simple and probably inefficient because I am fairly new to
SQL and the data I need to store and access is fairly straight forward. The
main reason I went with SQL db over Access, Excel, or even text files is
because the number of machines I have to work with multiplied by the number
of days would have blown everything else away. The data is simple enough tha
t
I could have went with any of the other storage options except for the sheer
number of records.
Is there any way your code will work with a single table. What I have read
on EXISTS mentions multiple tables.
Thanks
vm
vm
"Andrew J. Kelly" wrote:

> I assume you have a table with one row for each machine you want to manage
.
> You can do something like this.
> tableA = Machine Names
> tableB = Holds daily rows
> SELECT a.Name FROM tableA AS a
> WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
> AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
> --
> Andrew J. Kelly SQL MVP
>
> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>
>|||"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> I have a single table which we use to keep track of data from
different
> physical machines. The columns are: TheDate, TheName, & TheNotes.
The Date
> column is datetime and the rest are char. Every day each machine
we collect
> data on gets a row of information: The date of the collection, the
machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would
like to do
> is in Query Analyzer be able to tell if there are any dates
missing. Then we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select
data based
> on the preence or absence of values. I tried a couple different
code samples,
> none showed signs of progress. If I were coding in VB I would use
an array
> and compare the distinct values. Is there a way in SQL to show
values absent
> from a known sequence.
> Thanks
> vm
vm,
Basically, Andrew Kelly was right.
In order to have a known sequence of dates, you would build a
calendar table with your dates.
This way, you can run a NOT EXISTS or Frustrated Outer Join query
against the calendar table, and that will show you the missing
dates.
Sincerely,
Chris O.|||Excellent, that seems the simpelest solution. I am away from work for the
wend, but will try on Monday.
Thanks to both!
vm
"Chris2" wrote:

> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> different
> The Date
> we collect
> machine
> like to do
> missing. Then we
> data based
> code samples,
> an array
> values absent
> vm,
> Basically, Andrew Kelly was right.
> In order to have a known sequence of dates, you would build a
> calendar table with your dates.
> This way, you can run a NOT EXISTS or Frustrated Outer Join query
> against the calendar table, and that will show you the missing
> dates.
>
> Sincerely,
> Chris O.
>
>