February 1, 2007 at 4:58 pm
Hi All,
This script has 4 select statements. The first 2 just display the sample data created. The 3rd and 4th should return the same result set. Why does the statement that uses sub-queries work but the statement that uses joins returns bogus data? I'm not seeing any ambiguity in the joins.
Thanks,
/* ******************** Begin Script ************************ */
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempEmployee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTempEmployee]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempOffDay]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTempOffDay]
GO
create table tblTempEmployee (
EmployeeID varchar(20) NOT NULL PRIMARY KEY,
StartDate datetime NOT NULL)
GO
create table tblTempOffDay (
[OffDayID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[EmployeeID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OffDayType] [char] (1) NOT NULL ,
[DaysEarned] [numeric] (6, 2) NOT NULL DEFAULT 0.0 ,
[DateEarned] [datetime] NOT NULL
)
GO
-- insert employee records
insert into tblTempEmployee
(EmployeeID, StartDate)
values ('Emp 1', '2/1/2007');
insert into tblTempEmployee
(EmployeeID, StartDate)
values ('Emp 2', '2/1/2007');
insert into tblTempEmployee
(EmployeeID, StartDate)
values ('Emp 3', '2/1/2007');
-- insert off days
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'V', 3, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'V', 5, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'V', 1, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'V', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'V', 3, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'S', 3, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'S', 5, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'S', 1, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'S', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'S', 3, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'F', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 1', 'F', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'V', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'S', 2, '2/1/2007');
insert into tblTempOffDay
(EmployeeID, OffDayType, DaysEarned, DateEarned)
values ('Emp 2', 'F', 2, '2/1/2007');
-- Here's the sample data
select * from tblTempEmployee order by EmployeeID;
select * from tblTempOffDay order by EmployeeID, OffDayType;
-- this gives bogus results
Select emp.EmployeeID,
emp.StartDate,
ISNULL(SUM(odev.DaysEarned), 0) AS VacDays,
ISNULL(SUM(odes.DaysEarned), 0) AS SckDays,
ISNULL(SUM(odef.DaysEarned), 0) AS FlxDays
from tblTempEmployee emp
left outer join tblTempOffDay odev ON (odev.EmployeeID = emp.EmployeeID) AND (odev.OffDayType = 'V') AND (YEAR(odev.DateEarned) = YEAR('2/1/2007'))
left outer join tblTempOffDay odes ON (odes.EmployeeID = emp.EmployeeID) AND (odes.OffDayType = 'S') AND (YEAR(odes.DateEarned) = YEAR('2/1/2007'))
left outer join tblTempOffDay odef ON (odef.EmployeeID = emp.EmployeeID) AND (odef.OffDayType = 'F') AND (YEAR(odef.DateEarned) = YEAR('2/1/2007'))
group by emp.EmployeeID, emp.StartDate
order by emp.EmployeeID;
-- this works
Select emp.EmployeeID,
emp.StartDate,
(select ISNULL(SUM(odev.DaysEarned), 0) from
tblTempOffDay odev where (odev.EmployeeID = emp.EmployeeID) AND (odev.OffDayType = 'V') AND (YEAR(odev.DateEarned) = YEAR('2/1/2007'))) AS VacDays,
(select ISNULL(SUM(odes.DaysEarned), 0) from
tblTempOffDay odes where (odes.EmployeeID = emp.EmployeeID) AND (odes.OffDayType = 'S') AND (YEAR(odes.DateEarned) = YEAR('2/1/2007'))) AS SckDays,
(select ISNULL(SUM(odef.DaysEarned), 0) from
tblTempOffDay odef where (odef.EmployeeID = emp.EmployeeID) AND (odef.OffDayType = 'F') AND (YEAR(odef.DateEarned) = YEAR('2/1/2007'))) AS FlxDays
from tblTempEmployee emp
group by emp.EmployeeID, emp.StartDate
order by emp.EmployeeID;
drop table tblTempEmployee;
drop table tblTempOffDay;
/* ******************** End Script ************************ */
February 1, 2007 at 5:37 pm
This works and it's easier on the eyes (and head)
SELECT
e.employeeID
,StartDate
,VacDays = SUM(CASE WHEN OffDayType = 'V' AND YEAR(DateEarned) = 2007 THEN DaysEarned ELSE 0 END)
,SckDays = SUM(CASE WHEN OffDayType = 'S' AND YEAR(DateEarned) = 2007 THEN DaysEarned ELSE 0 END)
,FlxDays = SUM(CASE WHEN OffDayType = 'F' AND YEAR(DateEarned) = 2007 THEN DaysEarned ELSE 0 END)
FROM
tblTempEmployee e
LEFT JOIN tblTempOffDay tod ON e.employeeID = tod.employeeID
GROUP BY
e.employeeID
,StartDate
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply