query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can have multiple rows with the same checkdate. I am trying to query the two tables so I can get the sum of the earnings for a particular checkdate and the employees status at the time
of the check. Here is an example of the data and what I have written so far:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employeeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows for each ID each month will be painful.
How can I write this query so that the select will return the sum of earnings by employeeID on a specific checkdate and the employee status on that date. I thought of using a cursor, but I am not advanced enough to write one. I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!
I realize I should have posted this in data mining, but I don't want to double post
sql

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can ha
ve multiple rows with the same checkdate. I am trying to query the two tabl
es so I can get the sum of the earnings for a particular checkdate and the e
mployees status at the time
of the check. Here is an example of the data and what I have written so far
:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employ
eeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows f
or each ID each month will be painful.
How can I write this query so that the select will return the sum of earning
s by employeeID on a specific checkdate and the employee status on that date
. I thought of using a cursor, but I am not advanced enough to write one.
I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!I realize I should have posted this in data mining, but I don't want to doub
le post

Query from Access 2000 won't work in MSDE 2000

I have the following SQL query which works great in MS Access 2000
SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
[tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
[tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
[tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
[tbKeymap].[ItemIndex]
This query does not work in MSDE 2000.
I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near '.'.
I have no idea why this is the case since the MDAC is 2.7 (I'm
assuming since this is MSDE 2000).
Any ideas or solutions would be appreciated.
Ali
SQL Server (MSDE) and Access use different dialect of SQL language.
keyword DISTINCTROW does not exist in SQL Server's T-SQL: it is DISTINCT in
SQL Server, while in Access, you have DISTINCTROW and DISTINCT, they do
things slightly different
"Ali Syed" <alijsyed@.hotmail.com> wrote in message
news:26c82868.0408181145.34f0f6e9@.posting.google.c om...
> I have the following SQL query which works great in MS Access 2000
> SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
> [tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
> [tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
> ([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
> [tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
> [tbKeymap].[ItemIndex]
>
> This query does not work in MSDE 2000.
> I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near '.'.
>
> I have no idea why this is the case since the MDAC is 2.7 (I'm
> assuming since this is MSDE 2000).
>
> Any ideas or solutions would be appreciated.
>
> Ali
|||Thanks Norman that fixed it.
It seems that I will have to compensate for this issue.
Ali

Query Foreign Key Columns and Tables

I am trying to query the database to get me the foreign key columns and the tables they belong to.

I have:

The name of the table

I need:

The name of the column in thetarget table

The name of the column in thereferenced table

The name of thereferenced table

Any help would be great, thanks

You can use the Management Studio diagramming tool to create it easily but through code it can get complex, the link below will take you in the right directions. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

|||You'd better use system procedure to do this,?which?is?always recommended. For example:

use northwind
go
EXEC sp_helpconstraint Orders

If the result set doesn't fit your need, you can perform query directly on the sysforeighkeys table, but this is not recommended for tons of reasons. For exampe:

DECLARE @.tblName sysname
SET @.tblName='Orders'

SELECT OBJECT_NAME(fkeyid) AS TargetTable,OBJECT_NAME(rkeyid) AS ReferencedTable,
OBJECT_NAME(constid) AS FKName,COL_NAME(fkeyid,fkey) AS TargetColumn,
COL_NAME(rkeyid,rkey) AS ReferencedColumn
FROM sysforeignkeys
WHERE fkeyid=OBJECT_ID(@.tblName)|||

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

|||

BurnChrome:

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

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