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.
>
>

Data set towards a chain of character

Hello :

I have a request which me dismissal 10 lines (normal select), I want to have quite lines in a single chain of character, separated one in the others by a comma:

A
B
C
D > A,B,C,D

What is that you have an idea for ca.

Thank you.

If you use SQL Server 2005,

Code Snippet

Declare @.data table (val char);

Insert Into @.data

Select 'A'

Union All

Select 'B'

Union All

Select 'C'

Union All

Select 'D'

Select

Substring((Select ',' + val as [text()] from @.data for xml path('')),2,8000)

|||Thank's , but i use oracle !!!

|||

Oracle?

Ok, I believe except SQL Server none of the DB provide such a easy way to covert the result-set into XML (For XML clause). But oracle do have the xml output – you can try to get the answer on oracle forums. But I can provide few hints (what I remember still), XMLElement, XMLAttributes, XMLForest these are the XML generator functions in Oracle.

Data of different dates

I want to make a report which is using parameter field of a single date. But the same time I have some fields those have to show some data out of the parameter field. And the grouping is same.

For example, a coloum show data of the date which is slected but another field have to show data of the current month but Less and equal to the selected field.

ThankyouYour best bet is to create one as a subreport to the other. These 2 reports link by the parameter field, but use different filter.

That's all I can say with the info you provide.

Good luck.