Need query help! - 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 query help!

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-05-03 : 06:58:08

In the screenshot below I have a query and the results in QA.

This query looks at the past 5 days and shows the date(s), Agent and their respective Sales Manager where the Agent has fallen below 80% to target on that particular day.

I have now been instructed to change this query to do the following:

Each Agent must be displayed only once with a combined '% To Target' for the last 5 days, but only showing if the combined '% To Target' falls below 80% in relation to the number of days they worked. So, if they have worked 3 days in the last 5 then it would be 80% of 300(3 days) or if they worked 4 days it would be 80% of 400.

Can someone please help as I cannot begin to figure this one out.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 07:02:17

I can't see the screenshot. Please post your query and the related table structure with sample data and the expected result

patshaw
Posting Yak Master

177 Posts

Posted - 2006-05-03 : 07:25:38

My query is as follows:

SELECT distinct LBMM.dbo.vw_LBMM_TimesheetFact.[date] AS 'Date', LBMM.dbo.vw_LBMM_TimesheetFact.AgentID AS 'Agent ID',
AIMCall.dbo.AC_CLIENT_AGENTDETAILS.FirstName + ' ' + AIMCall.dbo.AC_CLIENT_AGENTDETAILS.surname as 'Agent_Name',
AIMCALL.dbo.AC_CLIENT_PERSONKEYIDS.FullName AS 'SM Name', LBMM.dbo.vw_LBMM_TimesheetFact.perctarget / entrycount AS '% To Target'
FROM LBMM.dbo.vw_LBMM_TimesheetFact INNER JOIN AIMCall.dbo.AC_CLIENT_AGENTDETAILS
ON LBMM.dbo.vw_LBMM_TimesheetFact.AgentID = AIMCall.dbo.AC_CLIENT_AGENTDETAILS.KeyId
INNER JOIN AIMCALL.dbo.AC_CLIENT_PERSONKEYIDS
ON LBMM.dbo.vw_LBMM_TimesheetFact.SMID = AIMCALL.dbo.AC_CLIENT_PERSONKEYIDS.PersonKeyId
WHERE LBMM.dbo.vw_LBMM_TimesheetFact.perctarget / LBMM.dbo.vw_LBMM_TimesheetFact.entrycount < 80
AND LBMM.dbo.vw_LBMM_TimesheetFact.perctarget / LBMM.dbo.vw_LBMM_TimesheetFact.entrycount > 0
AND (LBMM.dbo.vw_LBMM_TimesheetFact.[date] BETWEEN CONVERT(varchar, DATEADD(d, - 6, GETDATE()), 112) AND CONVERT(varchar, GETDATE()-1, 112))
ORDER BY AIMCALL.dbo.AC_CLIENT_PERSONKEYIDS.FullName, LBMM.dbo.vw_LBMM_TimesheetFact.AgentID, LBMM.dbo.vw_LBMM_TimesheetFact.[date]

and the results sample is:

Date,Agent ID,Agent_Name,SM Name,% To Target
20060427,107035,RICHARD EVANS,Anthony Ward,48.390000
20060502,108175,Mark Bailey,Anthony Ward,60.000000
20060427,108316,Yusuf Razak,Anthony Ward,48.390000
20060502,108316,Yusuf Razak,Anthony Ward,32.260000
20060427,107888,Ryan Hedley,Ben Hartley,77.140000
20060429,108467,Laura Bullock,Ben Hartley,77.140000
20060427,108153,James Lomax,BRAY DANIELLE,57.145000
20060427,108026,Rebecca Salmon,Christina Barker,40.000000
20060502,108026,Rebecca Salmon,Christina Barker,76.920000
20060427,108348,Nicola Warburton,Christina Barker,77.330000
20060427,108370,Shahid Hussain,Christina Barker,60.000000
20060427,108383,Joanne Burrell,Christina Barker,76.000000
20060428,108383,Joanne Burrell,Christina Barker,61.330000
20060502,108383,Joanne Burrell,Christina Barker,66.670000

As mentioned earlier, I need to change this to produce:
Each Agent must be displayed only once with a combined '% To Target' for the last 5 days, but only showing if the combined '% To Target' falls below 80% in relation to the number of days they worked. So, if they have worked 3 days in the last 5 then it would be 80% of 300(3 days) or if they worked 4 days it would be 80% of 400.

Hope this helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 07:35:19

can you also post the expected result ? and explain further how to calculate "combined '% To Target'" ?

patshaw
Posting Yak Master

177 Posts

Posted - 2006-05-03 : 07:49:20

Hi,
If you take 'Joanne Burrell' at the bottom of the list, she has 3 entries in the last 5 days. She has 76.0%, 61.33%, and 66.67%. Because she has only worked 3 days in the last 5, she needs to achieve over 80% of 300 (3 days) to NOT appear in the new list.
As 80% of 300 is 240 she would appear as her 3 days when totaled together come to 204. So, she should appear in the new list as 1 row with 204 as her combined % to target.

The other problem is that some agents may have only worked 2 days or 4 days or all 5. The combined figure must be compared to the number of days they worked so if they worked 5 then 500 would be the comparison.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources