Query Help

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>sql

Query Help

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>

Query every entry not in each hour

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,
AndyThis 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:

Quote:

Originally Posted by

>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