Posted by Roy Harvey on 07/17/07 17:34

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@gmail.com wrote:

>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

[Back to original message]