Is there a better way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there a better way to do this?

Author

Topic

bumpurcs
Starting Member

4 Posts

Posted - 2007-02-27 : 12:03:00

I have a table with employee records.
And I have a table with employee transactions.
I'm trying to do a sum on the transactions like:

ssn, employee number, start date, sum(transactions)

So far all of the queries I've tried run slow, possibly because the transaction tables are a bunch of unions because they're in like 5 queries. However doing a select on the transactions query is fairly fast (pulls 3591 rows in 1 sec or less) My current query runs like 2-3 minutes and pulls 75k rows .

doing something like this now (sort of):

select *, (select sum(hours) from trans where z.ssn=ssn) from employees z

Is there a better way?
I tried going a join, but the groupbys mangle the data.
Tried this:
select a.*,sum(b.hours) from trans b, employees a where a.ssn=b.ssn group by {each column in employee table}

Any suggestions would be great..
TIA
Shane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 12:26:03

[code]SELECT z.*,
x.theHours
FROM Employees AS z
INNER JOIN (
SELECT SSN,
SUM(Hours) AS theHours
FROM Trans
GROUP BY SSN
) x AS x.SSN = z.SSN[/code]

Peter Larsson
Helsingborg, Sweden

bumpurcs
Starting Member

4 Posts

Posted - 2007-02-27 : 15:20:13

Ok, well I messed up the problem trying to use fewer fields and did a big miss on simplifying it.

This is the original sql:
SELECT *, (
SELECT SUM(sales) sales
FROM trans
WHERE trans.ssn = z.ssn AND trans.odate < z.workdate AND year(trans.odate) = year(z.workdate)
) AS calc
FROM employee z

Basically the employee records have an entry for every day of the year. I'm trying to make it roll up the transactions for each day all of the previous entries for the year. So the calc column will be a climbing value through the year.

And it takes longer than I thought, a little over 6 minutes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 15:31:33

So there is a WorkDay column in the Employees table?
Interesting...

How about posting some proper sample data and your expected output based on the sample data?

Peter Larsson
Helsingborg, Sweden

bumpurcs
Starting Member

4 Posts

Posted - 2007-02-27 : 15:45:00

employee data looks something like so:
ssn workdate shift blah blah
123 1/1/2007 A
123 1/2/2007 A
123 1/3/2007 A
123 1/4/2007 A
..
124 1/1/2007 B
124 1/2/2007 B
124 1/3/2007 B
124 1/4/2007 B

trans table something like:
ssn workdate sales
123 1/1/2007 1
124 1/1/2007 0
123 1/2/2007 3
124 1/2/2007 1
123 1/3/2007 1
124 1/3/2007 1
123 1/4/2007 5
124 1/4/2007 2

So that the query gives:
ssn workdate shift calc blah blah
123 1/1/2007 A null
123 1/2/2007 A 1
123 1/3/2007 A 4
123 1/4/2007 A 5
123 1/5/2007 A 10

..
124 1/1/2007 B null
124 1/2/2007 B 0
124 1/3/2007 B 1
124 1/4/2007 B 2
124 1/5/2007 B 4

Later I hope to run a query against this one and say, give me all employees numbers for 1/3/2007 and get:
124 1/3/2007 B 1
123 1/3/2007 A 4

bumpurcs
Starting Member

4 Posts

Posted - 2007-02-27 : 15:47:49

quote:
Originally posted by bumpurcs

employee data looks something like so:
ssn workdate shift blah blah
123 1/1/2007 A
123 1/2/2007 A
123 1/3/2007 A
123 1/4/2007 A
..
124 1/1/2007 B
124 1/2/2007 B
124 1/3/2007 B
124 1/4/2007 B

trans table something like:
ssn workdate sales
123 1/1/2007 1
124 1/1/2007 0
123 1/2/2007 3
124 1/2/2007 1
123 1/3/2007 1
124 1/3/2007 1
123 1/4/2007 5
124 1/4/2007 2

So that the query gives:
ssn workdate shift calc blah blah
123 1/1/2007 A null
123 1/2/2007 A 1
123 1/3/2007 A 4
123 1/4/2007 A 5
123 1/5/2007 A 10

..
124 1/1/2007 B null
124 1/2/2007 B 0 <- Take this out (my mistake)
124 1/3/2007 B 1
124 1/4/2007 B 2
124 1/5/2007 B 4

Later I hope to run a query against this one and say, give me all employees numbers for 1/3/2007 and get:
124 1/3/2007 B 1
123 1/3/2007 A 4

There shouldn't be a zero record in the trans btw. That is wrong. The idea is that there are only entries on days there was work. Thats why the roll up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources