Can't figure out how to write query

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)

Can't figure out how to write query

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-02-17 : 14:49:11

I have a table TABLE1. My company has 2 sites. This table contains employees with the amount of hours they worked on which project at which sites.

[CODE]
CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)

INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')

INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')

INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')

INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')

INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')
[/code]

I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a project that ended in a 5. If employees worked on projects that did not end in 5 I need the totals for their site only. A parameter of site will be passed to the stored procedure.

So for example: If site parameter of 1 is passed.
I need to see the following results:

Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5

If site parameter of 2 is passed.
I need to see the following results:

Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5

Any help would be greatly appreciated,

Thanks,
ninel

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-17 : 15:50:39

Assuming I understand correctly...

DECLARE @Site INT

SET @Site = 1
--SET @Site = 2

SELECT CallDate, Project, SUM(Hours) AS TotalHours
FROM #TABLE1
WHERE Site = @Site
AND RIGHT(Project, 1) <> '5'
GROUP BY CallDate, Project
UNION
SELECT CallDate, Project, SUM(Hours) AS TotalHours
FROM #TABLE1
WHERE RIGHT(Project, 1) = '5'
GROUP BY CallDate, Project

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources