SQL Query issue - 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 2008 Forums

Transact-SQL (2008)

SQL Query issue

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-07-23 : 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

52326 Posts

Posted - 2012-07-23 : 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

45 Posts

Posted - 2012-07-24 : 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

52326 Posts

Posted - 2012-07-24 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources