September 14, 2011 at 11:18 am
I have the following tables and query:
declare @Employee table (
ID int,
Name varchar(20)
)
insert into @Employee (
ID,
Name
)
values
(1, 'Bob'),
(2, 'Doug'),
(3, 'Bill')
declare @AbsenceType table (
ID int,
Descrip varchar(50)
)
insert into @AbsenceType (
ID,
Descrip
)
values
(1, 'Flex'),
(2, 'Vacation')
declare @AbsenceHistory table (
ID int,
EmployeeId int,
StartDate date,
EndDate date,
SpansWeekend bit,
AbsType int
)
insert into @AbsenceHistory (
ID,
EmployeeId,
StartDate,
EndDate,
SpansWeekend,
AbsType
)
values
(1, 1, '2011-07-01','2011-07-04', 0, 2),
(2, 1, '2011-08-25','2011-08-29', 0, 2),
(3, 1, '2011-06-01','2011-06-01', 0, 1),
(4, 2, '2011-08-01','2011-08-04', 0, 2),
(5, 2, '2011-09-04','2011-09-06', 0, 1);
WITH TEMP AS
(SELECT
E.ID,
E.Name,
AH.StartDate, AH.EndDate, AH.SpansWeekend, AH.AbsType,
CASE WHEN AH.SpansWeekend = 'True'
THEN
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * 8)
ELSE
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2)
-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)) * 8)
END AS Hours
FROM
@Employee E,
@AbsenceHistory AH
WHERE
E.ID IN ('1','2','3') AND
E.ID = AH.EmployeeID AND
(AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-10-31') AND
AH.AbsType IN (1,2)
)
SELECT
TMP.Name,
SUM(CASE WHEN AT.Descrip='Vacation' THEN Hours ELSE 0 END) AS 'Vacation',
SUM(CASE WHEN AT.DESCRIP='Flex' THEN Hours ELSE 0 END) AS 'Flex'
FROM
TEMP TMP,@AbsenceType AT
WHERE
TMP.AbsType = AT.ID
GROUP BY
TMP.Name
This query returns the data like so:
Name Vacation Flex
Bob 40 8
Doug 32 16
However, I need the user who has no entries in the AbsenceHistory too. It will be 0, but I still need that data. Like this:
Name Vacation Flex
Bob 40 8
Doug 32 16
Bill 0 0
I think I need to use a Join, but I can't figure it out. Been struggling with this all day.
Thanks in advance!
September 14, 2011 at 11:26 am
...
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID
WHERE
E.ID IN ('1','2','3') AND
(((AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-10-31') AND AH.AbsType IN (1,2)) OR AH.ID IS NULL)
...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2011 at 11:48 am
I made the changes, but the query still returns the same results:
WITH TEMP AS
(SELECT
E.ID,
E.Name,
AH.StartDate, AH.EndDate, AH.SpansWeekend, AH.AbsType,
CASE WHEN AH.SpansWeekend = 'True'
THEN
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * 8)
ELSE
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2)
-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)) * 8)
END AS Hours
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID
WHERE
E.ID IN ('1','2','3') AND
(((AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-10-31') AND AH.AbsType IN (1,2)) OR AH.ID IS NULL)
)
SELECT
TMP.Name,
SUM(CASE WHEN AT.Descrip='Vacation' THEN Hours ELSE 0 END) AS 'Vacation',
SUM(CASE WHEN AT.DESCRIP='Flex' THEN Hours ELSE 0 END) AS 'Flex'
FROM
TEMP TMP,@AbsenceType AT
WHERE
TMP.AbsType = AT.ID
GROUP BY
TMP.Name
Thanks for your response.
September 14, 2011 at 12:52 pm
SELECT
TMP.Name,
SUM(CASE WHEN AT.Descrip='Vacation' THEN Hours ELSE 0 END) AS 'Vacation',
SUM(CASE WHEN AT.DESCRIP='Flex' THEN Hours ELSE 0 END) AS 'Flex'
FROM
TEMP TMP LEFT OUTER JOIN @AbsenceType AT ON TMP.AbsType = AT.ID
GROUP BY
TMP.Name
Stop joining in the where clause, you can only do inner joins that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2011 at 2:59 pm
Thanks for all your help GilaMonster!!
One more thing though. If the date range entered doesn't fall within items in the AbsenceHistory table, nothing is returned.
In this case I switched the end date to 2001-04-28.
I want it to return like this:
Name Vacation Flex
Bill 0 0
Bob 0 0
Doug 0 0
WITH TEMP AS
(SELECT
E.ID,
E.Name,
AH.StartDate, AH.EndDate, AH.SpansWeekend, AH.AbsType,
CASE WHEN AH.SpansWeekend = 'True'
THEN
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * 8)
ELSE
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2)
-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)) * 8)
END AS Hours
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID
WHERE
E.ID IN ('1','2','3') AND
(((AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-04-28')) OR AH.ID IS NULL)
)
SELECT
TMP.Name,
SUM(CASE WHEN AT.Descrip='Vacation' THEN Hours ELSE 0 END) AS 'Vacation',
SUM(CASE WHEN AT.DESCRIP='Flex' THEN Hours ELSE 0 END) AS 'Flex'FROM
TEMP TMP LEFT OUTER JOIN @AbsenceType AT ON TMP.AbsType = AT.ID GROUP BY TMP.Name
Again, thanks for the help, much appreciated.
September 14, 2011 at 3:35 pm
In that case...
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID AND (AH.StartDate >= '2011-01-01' AND AH.EndDate <= '2011-04-28')
WHERE
E.ID IN ('1','2','3')
That way the result set from AbsenceHistory is limited before the join, not after.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2011 at 4:33 pm
Thanks so much for your help Gilamonster, I really appreciate it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply