Nested Query

Hi All,

I am stuck to a senario in which i need the help of u all.

well the present senaio is i have two table xx and yy.

the table conatins data as below:

table xx

EmployeeID Departments

10014 A

10015 A

10002 A

10013 B

10019 B

10056 B

table yy

Empoyee ID ActuaDate Start time EndTime

10014 03/30/2007 0600 1445

10015 03/30/2007 0600 1445

10002 03/30/2007 0600 1445

10013 03/31/2007 1130 1300

10019 03/31/2007 0300 1300

10056 03/31/2007 0300 1100

--

conditions :

Actdate ='03/30/2007' and starttime>='600'

or actdate ='03/31/2007' and starttime<='0300' -for pick

Result should be as:

Department EmployeeCount Pick Drop

A 3 3 3

B 3 3 3

please help me in solving the problem.

Regards

sufian

Your requirements are a bit 'unclear'. Perhaps this is in the right direction:

Code Snippet

SET NOCOUNT ON

DECLARE @.xx table
( EmployeeID int,
Department char(1)
)

INSERT INTO @.xx VALUES ( 10014, 'A' )
INSERT INTO @.xx VALUES ( 10015, 'A' )
INSERT INTO @.xx VALUES ( 10002, 'A' )
INSERT INTO @.xx VALUES ( 10013, 'B' )
INSERT INTO @.xx VALUES ( 10019, 'B' )
INSERT INTO @.xx VALUES ( 10056, 'B' )

DECLARE @.yy table
( EmployeeID int,
ActuaDate datetime,
StartTime int,
EndTime int
)

INSERT INTO @.yy VALUES ( 10014, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10015, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10002, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', 1130, 1300 )
INSERT INTO @.yy VALUES ( 10019, '03/31/2007', 0300, 1300 )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, 1100 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', NULL, NULL )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, NULL )

SELECT
x.Department,
EmployeeCount = count( DISTINCT y.EmployeeID ),
Picked = count( y.StartTime ),
Dropped = count( y.EndTime )
FROM @.XX x
JOIN @.YY y
ON x.EmployeeID = y.EmployeeID
GROUP BY x.Department

Department EmployeeCount Picked Dropped
- - -- --
A 3 3 3
B 3 4 3

|||

While I can't completely follow your requirements, I think this will give you the idea for what you want. The idea is to do a select with a group by and manufacture values using SUM

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department

The full code follows, and if you could provide this kind of structure (and change mine if it is wrong) it is a lot easier. Plus, if your results are what you expected to receive, it seems wrong. If it is just a basice feel for the results, I understand.

I would also consider making the start and end values a datetime. Use a constraint to make sure they are in the same day perhaps, but it will be easier to work with with date and time in the same column.

Code Snippet

create table xx
(
EmployeeId int primary key,
Department char(1)
)
insert into xx
select 10014,'A'
union all
select 10015,'A'
union all
select 10002,'A'
union all
select 10013,'B'
union all
select 10019,'B'
union all
select 10056,'B'

create table yy
(
employeeId int,
actuaDate datetime,
startTime int,
endTime int
)
insert into yy
select 10014,'03/30/2007',0600,1445
union all
select 10015,'03/30/2007',0600,1445
union all
select 10002,'03/30/2007',0600,1445
union all
select 10013,'03/31/2007',1130,1300
union all
select 10019,'03/31/2007',0300,1300
union all
select 10056,'03/31/2007',0300,1100
go

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department