Hello,

I have been having a tough time writing the follow requirement for a
query.

On a table that the primary key is a tagId and an hourly timestamp, I
would like to find out for every hour which tags did not get entered
into the database. Essentially I am looking for patterns of entries
that are not making it into tableB.

Examples of the tables:
TableA TableB
TagID and TagName TagId Timestamp
PK PK1 PK2

approx 6000 rows approx 6000 rows per hour

I am thinking that I will need to do something like:

Select tableB1.time, count(*) from tableB1 group by tableB1.time
having tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagId
not in (select tagId from tableA where not exists (select
tableA.tagId, distinct.tableB2.time from tableB2)

I have been trying to create an effecient query handle this but have
not had any luck. Any assistance would be more then appreciated.

Thanks,
Andy

This requires a third tables. It would have a row for every hourly
timestamp, regardless of whether there are rows for that timestamp for
the table you described. You MIGHT get away without that table by
deriving it from TableB, but I suspect that would result in gaps.

The query performs a cross join, then eliminates rows that match
TableB using EXISTS.

SELECT *
FROM TableA as A
CROSS JOIN
TimeStamps as C
WHERE NOT EXISTS
(select * from TableB as B
where A.TagiD = B.TagIC
and C.Timestamp = B.Timestamp)

Roy Harvey
Beacon Falls, CT

On Tue, 17 Jul 2007 17:22:09 -0000, mcdonaghandy (AT) gmail (DOT) com wrote: