Help with complex query

Hi Everyone,

I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.

--

Agent Name: John Doe

Date Range: 1/1/2007 - 6/30/2007

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

Assuming your table is the grouped sum's by department:

Code Snippet

create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )

insert into #t1

select 1, 26355, 52920, 'Service'

union all select 2, 9000, 52920, 'Parts'

union all select 3, 17565, 52920, 'Dispatch'

select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),

round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'

from #t1

|||

DaleJ,

The table data is not grouped. Thats what makes this query complex.

|||

SamCosta wrote:

DaleJ,

The table data is not grouped. Thats what makes this query complex.

Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||

And some additional sample data.

It's not that difficult, but would like to get it right the first time (or two )

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

I need to then group the query results by EmpID to get a total Average Productivity Level

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2

54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),

Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.

Help With Another Query

Hello and Thank You for your previous help,
I have a table simular to the following:
ID = int (Key)
Name= VarChar
Department = nChar
Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S
I am looking for a query that will Return
Name All Departments
Chuck ATS
Mark AS
Without duplicate Names.
Thanks,
ChuckHi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50) NOT NULL
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:OrLlfeQXHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello and Thank You for your previous help,
> I have a table simular to the following:
> ID = int (Key)
> Name= VarChar
> Department = nChar
> Example Data
> ID Name Department
> 1 Chuck A
> 2 Mark A
> 3 Chuck T
> 4 Chuck S
> 5 Mark S
> I am looking for a query that will Return
> Name All Departments
> Chuck ATS
> Mark AS
> Without duplicate Names.
> Thanks,
> Chuck
>

help selecting

Hi,
I have the following tables, and I would like to filter the categories
to a specific department (and then supplier). Something like an
intersection.
How can I do it? I'm new to SQL, especially with joins, so are the
following 2 joins correct?
1) Filtering to Department can be easy:
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
WHERE Product.DepartmentId = @.DepartmentId
2) Adding a filtering to Supplier and I'm lost...
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
WHERE (Product.DepartmentId = @.DepartmentId)
AND (ProductSupplier.SupplierId = @.SupplierId)
table Product
ProductId: PK
CategoryId: FK
DepartmentId: FK
table Category
CategoryId: PK
CategoryName
table Department
DepartmentId: PK
table ProductSupplier
ProductId, SupplierId: combined PK
table Supplier
SupplierId
Explanation: a category can be found in one department, and also in
another department, example is category Basin can be in department
Plastic ware and also in department Aluminium ware. Some departments do
not necessarily have a category, example is Department Plastic ware do
not have category Boy short.
If the table are poorly designed, just let me know, I can still change
them now before it's too late.
Thanks for the help--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I don't believe you should have the DepartmentID in the Products table.
You should have an intersection table for Departments & Products, as you
do in the table ProductSupplier.
CREATE TABLE Product (
ProductID INTEGER NOT NULL PRIMARY KEY ,
CategoryID INTEGER NOT NULL
REFERENCES Category
ON UPDATE CASCADE
)
CREATE TABLE DepartmentProducts (
DepartmentID INTEGER NOT NULL
REFERENCES Departments
ON DELETE CASCADE ,
ProductID INTEGER NOT NULL
REFERENCES Products
ON DELETE CASCADE ,
CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
)
Perhaps:
SELECT C.CategoryId, C.CategoryName
FROM Category As C
INNER JOIN
Product As P ON C.CategorID = P.CategorID
INNER JOIN
ProductSupplier AS PS ON PS.ProductId = P.ProductId
INNER JOIN
DepartmentProducts As DP ON DP.ProductID = P.ProductID
WHERE DP.DepartmentId = @.DepartmentId
AND PS.SupplierId = @.SupplierId
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAi3i4echKqOuFEgEQJsCwCg0uoaQoPF+YzN
qSw63TfoergZKDEAnjss
6/qYpDxxxrlemVg9YppKsJIj
=g7sR
--END PGP SIGNATURE--
Michael Wong wrote:
> Hi,
> I have the following tables, and I would like to filter the categories
> to a specific department (and then supplier). Something like an
> intersection.
> How can I do it? I'm new to SQL, especially with joins, so are the
> following 2 joins correct?
> 1) Filtering to Department can be easy:
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> WHERE Product.DepartmentId = @.DepartmentId
> 2) Adding a filtering to Supplier and I'm lost...
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
> WHERE (Product.DepartmentId = @.DepartmentId)
> AND (ProductSupplier.SupplierId = @.SupplierId)
>
> table Product
> ProductId: PK
> CategoryId: FK
> DepartmentId: FK
> table Category
> CategoryId: PK
> CategoryName
> table Department
> DepartmentId: PK
> table ProductSupplier
> ProductId, SupplierId: combined PK
> table Supplier
> SupplierId
>
> Explanation: a category can be found in one department, and also in
> another department, example is category Basin can be in department
> Plastic ware and also in department Aluminium ware. Some departments do
> not necessarily have a category, example is Department Plastic ware do
> not have category Boy short.
> If the table are poorly designed, just let me know, I can still change
> them now before it's too late.
> Thanks for the help|||Hi MGFoster,
So what you suggest is that a product can be part of multiple departments.
I have initially designed a product to be also part of multiple
categories, but then I simplifed it to be only part of one single
category. Maybe I'll have to think more carefullt about it again.
Thank you for the quick reply.
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> I don't believe you should have the DepartmentID in the Products table.
> You should have an intersection table for Departments & Products, as you
> do in the table ProductSupplier.
> CREATE TABLE Product (
> ProductID INTEGER NOT NULL PRIMARY KEY ,
> CategoryID INTEGER NOT NULL
> REFERENCES Category
> ON UPDATE CASCADE
> )
> CREATE TABLE DepartmentProducts (
> DepartmentID INTEGER NOT NULL
> REFERENCES Departments
> ON DELETE CASCADE ,
> ProductID INTEGER NOT NULL
> REFERENCES Products
> ON DELETE CASCADE ,
> CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
> )
> Perhaps:
> SELECT C.CategoryId, C.CategoryName
> FROM Category As C
> INNER JOIN
> Product As P ON C.CategorID = P.CategorID
> INNER JOIN
> ProductSupplier AS PS ON PS.ProductId = P.ProductId
> INNER JOIN
> DepartmentProducts As DP ON DP.ProductID = P.ProductID
> WHERE DP.DepartmentId = @.DepartmentId
> AND PS.SupplierId = @.SupplierId
>