| Author |
 Topic  |
|
|
vipinjha123
Starting Member
India
45 Posts |
 Posted - 07/23/2012 : 10:28:58
|
I am having 3 table
Dental_201:-Domain,project,process,date,empid
Agent:-empid,domain,project
Target:-domain,project,process,target
My aim is to find below by using all 3 table
Project Process productivity month avgtarget achived
To calculate avgtarget logic is :-sum(target)/count(agent)
To calculate achived is productivity/count(agent)/21
select PROJECT,PROCESS,COUNT(*) Productivity,DATENAME(month,date) Months from DENTAL_201
group by PROJECT,PROCESS,DATE
order by PROJECT
select SUM(Daily_Target) total_target from Process_Target
select COUNT(*) agent from Agent_Master
Please suggest proper join
Regards,
Vipin jha
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/23/2012 : 10:44:07
|
you'vent given rule to calculate productivity so make sure you add that also and calculate achieved in below query
SELECT m.*,
n.TargetSum * 1.0/AgentCnt AS avgtarget,
..
FROM
(
SELECT t.*,d.[Month],d.[Year]
FROM (SELECT DISTINCT domain,project,process FROM Target) t
CROSS JOIN (SELECT DISTINCT YEAR(date) AS [Year],DATENAME(mm,date) AS [Month] FROM Dental_201)d
)m
CROSS APPLY (SELECT SUM(target) AS TargetSum
FROM target
WHERE domain = m.domain
AND project = m.project
AND process = m.process)n
CROSS APPLY (SELECT count(empid) as AgentCnt
FROM Dental_201 dt
INNER JOIN Agent a
ON a.empid= dt.epmid
AND a.domain = dt.domain
AND a.project = dt.project
WHERE DATENAME(mm,date) = m.[Month]
AND YEAR(date) = m.[Year]
)o
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
vipinjha123
Starting Member
India
45 Posts |
Posted - 07/24/2012 : 01:19:42
|
Table structure of all given tables:-
CREATE TABLE [dbo].[Agent_Master](
[EMPLOYEEID] [nvarchar](255) NULL,
[EMPLOYEENAME] [nvarchar](255) NULL,
[DESIGNATION] [nvarchar](255) NULL,
[Domain] [nvarchar](255) NULL,
[PROJECT] [nvarchar](255) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Process_Target](
[Domain] [nvarchar](255) NULL,
[Project] [nvarchar](255) NULL,
[Daily_Target] [float] NULL,
[Process] [nvarchar](255) NULL,
[SubProcess] [nvarchar](255) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[DENTAL_201](
[DATE] [datetime] NULL,
[DOMAIN] [nvarchar](100) NULL,
[PROJECT] [nvarchar](188) NULL,
[PROCESS] [nvarchar](100) NULL,
[SUBPROCESS] [nvarchar](100) NULL,
[EMPID] [nvarchar](10) NULL,
[ERROR] [int] NULL
) ON [PRIMARY]
I am getting count(*) from agent_master means number of agent
from sum(daily_target) from process_target means sum of al target
select count(*) productivity,project,process,datename(month,date) month from dental_201
group by project ,process ,date
it give my productivity on monthly basis
My aim is to find below by using all 3 table
PROJECT PROCESS PRODUCTIVITY AVGTARGET ACHIEVED
To calculate avgtarget logic is :-sum(target)/count(agent)
To calculate achived is productivity/count(agent)/21
regards,
vipin jha
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/24/2012 : 09:55:56
|
First start with suggestion given and extend it to add productivity calculation too.
Dont wait for full answers to be spoonfed to you..
Will definitely help if you face any issues while trying it out yourself..
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
SQL Query issue
