Need data from six tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need data from six tables

Author

Topic

Chester
Starting Member

27 Posts

Posted - 2004-03-04 : 15:20:58

I'm running SQL2000 and have to sum the hours worked by employees from
five tables on multiple dates. Here is some data to load:

create table tbl1 (
chief varchar(10) null,
person varchar(10) null
)

INSERT INTO tbl1 VALUES ('chief1', 'Mark')
INSERT INTO tbl1 VALUES ('chief1', 'Randy')
INSERT INTO tbl1 VALUES ('chief1', 'Lisa')
INSERT INTO tbl1 VALUES ('chief2', 'Nancy')
INSERT INTO tbl1 VALUES ('chief2', 'Bruce')
INSERT INTO tbl1 VALUES ('chief3', 'Don')
INSERT INTO tbl1 VALUES ('chief3', 'Ed')

create table tbl2 (
person varchar(10) null,
hrs int null,
dt smalldatetime null
)

INSERT INTO tbl2 VALUES ('Mark', 10, '10/19/2003')
INSERT INTO tbl2 VALUES ('Mark', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Mark', 5, '11/30/2003')
INSERT INTO tbl2 VALUES ('Randy', 9, '10/19/2003')
INSERT INTO tbl2 VALUES ('Randy', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Nancy', 12, '10/19/2003')
INSERT INTO tbl2 VALUES ('Ed', 15, '10/19/2003')

create table tbl3 (
person varchar(10) null,
hrs int null,
dt smalldatetime null
)

INSERT INTO tbl2 VALUES ('Mark', 20, '10/19/2003')
INSERT INTO tbl2 VALUES ('Randy', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Lisa', 11, '11/30/2003')
INSERT INTO tbl2 VALUES ('Lisa', 4, '12/14/2003')
INSERT INTO tbl2 VALUES ('Bruce', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Bruce', 12, '11/30/2003')
INSERT INTO tbl2 VALUES ('Don', 13, '11/2/2003')

create table tbl4 (
person varchar(10) null,
hrs int null,
dt smalldatetime null
)

INSERT INTO tbl2 VALUES ('Mark', 3, '10/19/2003')
INSERT INTO tbl2 VALUES ('Randy', 5, '10/19/2003')
INSERT INTO tbl2 VALUES ('Randy', 7, '12/14/2003')
INSERT INTO tbl2 VALUES ('Nancy', 9, '11/2/2003')
INSERT INTO tbl2 VALUES ('Nancy', 8, '11/30/2003')
INSERT INTO tbl2 VALUES ('Nancy', 12, '12/14/2003')
INSERT INTO tbl2 VALUES ('Ed', 11, '11/2/2003')

create table tbl5 (
person varchar(10) null,
hrs int null,
dt smalldatetime null
)

INSERT INTO tbl2 VALUES ('Randy', 2, '12/28/2003')
INSERT INTO tbl2 VALUES ('Lisa', 5, '11/2/2003')
INSERT INTO tbl2 VALUES ('Lisa', 6, '11/30/2003')
INSERT INTO tbl2 VALUES ('Bruce', 9, '10/19/2003')
INSERT INTO tbl2 VALUES ('Bruce', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Don', 12, '11/16/2003')
INSERT INTO tbl2 VALUES ('Don', 15, '11/30/2003')

create table tbl6 (
person varchar(10) null,
hrs int null,
dt smalldatetime null
)

INSERT INTO tbl2 VALUES ('Mark', 13, '12/28/2003')
INSERT INTO tbl2 VALUES ('Lisa', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Nancy', 5, '11/30/2003')
INSERT INTO tbl2 VALUES ('Bruce', 9, '10/19/2003')
INSERT INTO tbl2 VALUES ('Don', 8, '11/2/2003')
INSERT INTO tbl2 VALUES ('Ed', 4, '10/19/2003')
INSERT INTO tbl2 VALUES ('Ed', 7, '12/14/2003')

So, tbl1 has the chief (the supervisor) and person while the other
five tables have the person, hours & dates the hours were worked on.I have to sum the hours for each person from each table where the dates
match. The output needs to be something like this:

Chief person date total_hrs
chief1 Mark 10/19/2003 33
chief1 Mark 11/2/2003 8
chief1 Mark 11/30/2003 5
chief1 Mark 12/28/2003 13
chief1 Randy 10/19/2003 14
etc.

What is the best way to get this? I tried doing a join on these
tables but it takes too long. Is there something I can do with
case statements or subqueries to make this work? TIA

Chester

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-04 : 15:51:26

I know this may sound flippant, and I don't mean it that way, but... you could redesign your database so that you don't have six different tables where "Hours Worked" are stored.

Short of that, you might be able to create a view that UNIONs the tables together and then run a GROUP BY Date with SUM(hours)

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-04 : 16:12:59

I agree that you should consider redesigning your schema as what you have now is not good. But this seems to work (based on what Mark said could work):

CREATE VIEW View1
AS
SELECT person, dt, hrs
FROM tbl2
UNION ALL
SELECT person, dt, hrs
FROM tbl3
UNION ALL
SELECT person, dt, hrs
FROM tbl4
UNION ALL
SELECT person, dt, hrs
FROM tbl5
UNION ALL
SELECT person, dt, hrs
FROM tbl6
GO

SELECT person, dt, SUM(hrs) AS Hours
FROM View1
GROUP BY person, dt

DROP VIEW View1

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources