New perspective on Query question (Saturday)

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

SQL Server Development (2000)

New perspective on Query question (Saturday)

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 04:53:21

I am still having major problems. I cannot figure how to group the users that have worked part days in multiple departments and group them into one, calculating their hours worked. Below is the full code which I couldn't access on Saturday. Can anyone show me how I integrate the above results please.

CREATE PROCEDURE dbo.usp_REPORT_IncompleteDay_Report

@site Varchar(25)

AS

SELECT Distinct LMM.dbo.Timesheets.[date] as 'Date', LMM.dbo.Timesheets.AgentID,
ACall.dbo.CLIENT_AGENTDETAILS.FirstName + ' ' + ACall.dbo.CLIENT_AGENTDETAILS.surname as 'Agent Name',
ACall.dbo.CLIENT_PERSONKEYIDS.FullName AS 'Sales Manager Name',
ACall.dbo.AC_CLIENT_DIM_SM_CAMPAIGNS.CampaignName as 'Campaign Name',
LMM.dbo.Timesheets.Hours as 'Hours',ACall.dbo.AC_CLIENT_DIM_SM_CAMPAIGNS.Site,
ACall.dbo.CLIENT_AGENTDETAILS.FTPT as 'Status',ACall.dbo.CLIENT_AGENTDETAILS.Agency as 'Agency'

FROM (LMM.dbo.Timesheets INNER JOIN ACall.dbo.AC_CLIENT_DIM_SM_CAMPAIGNS
ON LMM.dbo.Timesheets.CampaignID = ACall.dbo.AC_CLIENT_DIM_SM_CAMPAIGNS.CampaignID
AND LMM.dbo.Timesheets.[Date] = convert(varchar, DATEADD(d,-3,GETDATE()), 103)
AND ACall.dbo.AC_CLIENT_DIM_SM_CAMPAIGNS.Site = @site)
INNER JOIN ACall.dbo.CLIENT_AGENTDETAILS
ON LMM.dbo.Timesheets.AgentID = ACall.dbo.CLIENT_AGENTDETAILS.KeyID
INNER JOIN ACall.dbo.CLIENT_PERSONKEYIDS
ON LMM.dbo.Timesheets.SMID = ACall.dbo.CLIENT_PERSONKEYIDS.PersonKeyID
where ACall.dbo.CLIENT_AGENTDETAILS.FTPT = 'FT' AND ACall.dbo.CLIENT_AGENTDETAILS.Agency = 'LBM' AND LMM.dbo.Timesheets.Hours < 7.75
OR ACall.dbo.CLIENT_AGENTDETAILS.FTPT = 'FT' AND ACall.dbo.CLIENT_AGENTDETAILS.Agency <> 'LBM' AND LMM.dbo.Timesheets.Hours < 7.5
ORDER BY 'Agent Name' ASC
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 04:56:48

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 06:00:59

Below is a sample resultset I am getting. If you look at the entry for
Peter Mitchell which appears 4 times, I need to group him into 1 result
and calculate his total Hours.

AgentID Agent Name Sales Manager Campaign Hours Date

106154 CONSTANCE BROWN John Clark Inbound_New 1.00 21/10/2005
106154 CONSTANCE BROWN John Clark Inbound_New 6.50 21/10/2005
106148 DAVID SHERWOOD Ben Hartley Inbound_New 4.00 21/10/2005
105852 LEILA MOUSA David Dugmore LMS_AP 3.00 21/10/2005
105911 PETER MITCHELL Peter Mitchell Aspira_Campaign 1.94 21/10/2005
105911 PETER MITCHELL Peter Mitchell CashScore_Oct05 1.94 21/10/2005
105911 PETER MITCHELL Peter Mitchell Health_Shield 1.94 21/10/2005
105911 PETER MITCHELL Peter Mitchell LinkHR_Jan05 1.93 21/10/2005
105953 SARA SAMOUELLE John Evans Talm_AP 4.00 21/10/2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 06:08:16

Try this

Select AgentID, Agent, Name, Sales, Manager, Campaign ,Sum(Hours), Date
from yourTable group by AgentID, Agent, Name, Sales, Manager, Campaign ,Date

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 07:07:46

This groups Constance Brown into one row, but totals her Hours to 15.00???? Peter Mitchell still appears as 4 rows.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 07:16:47

Better post the result you want from that sample data

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 07:26:36

106154 CONSTANCE BROWN John Clark Inbound_New 7.50 21/10/2005
106148 DAVID SHERWOOD Ben Hartley Inbound_New 4.00 21/10/2005
105852 LEILA MOUSA David Dugmore LMS_AP 3.00 21/10/2005
105953 SARA SAMOUELLE John Evans Talm_AP 4.00 21/10/2005

Peter Mitchell should not appear at all because when his 4 rows (Hours) are added together, they take his total to 7.75. Constance Brown should appear because her total hours when added should read 7.50. The Hours column does not seem to be adding up correctly.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 07:48:52

>>Peter Mitchell should not appear at all because when his 4 rows (Hours) are added together, they take his total to 7.75.

Do you want not to retrieve if total >7?

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 08:29:08

I want to retrieve all rows that are less than 7.75 hours. Some will be computed from 2 or 3 rows and some will be single rows.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 08:51:16

Try this

Select AgentID, Agent, Name, Sales, Manager, Campaign ,Sum(Hours), Date
from yourTable group by AgentID, Agent, Name, Sales, Manager, Campaign ,Date
having Sum(Hours)<7.75

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-24 : 14:45:07

Thanks for all your help. I sorted the problem in the end. It seems that the problem was coming from the campaign column. One of those errors that you wonder why it took so long to find!! Anyway, thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources