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