November 17, 2011 at 2:12 pm
I have the following 2 tables and query:
DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](25) NOT NULL);
DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[Hours] [decimal](6, 2) NOT NULL);
INSERT INTO @EMPLOYEE
(Name)
VALUES
('Steve');
INSERT INTO @AbsenceHistory
(EmployeeID, StartDate, EndDate, Hours)
VALUES
(1, '01-01-2011', '01-02-2011', 8),
(1, '03-01-2011', '03-01-2011', 8),
(1, '04-01-2011', '04-01-2011', 8),
(1, '05-01-2011', '05-01-2011', 8);
WITH TEMP_FILTER_BY_DATE AS
(SELECT
E.ID,E.Name, AH.ID AS AHID, AH.StartDate, AH.EndDate,((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.Hours) AS Hours
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID AND
(AH.StartDate >= '01-01-2011' AND AH.EndDate <= '02-10-2011')
WHERE
E.ID IN ('1') AND
((AH.StartDate >= '01-01-2011' AND AH.EndDate <= '02-10-2011') OR AH.ID IS NULL)
),
TEMP_NO_FILTER AS
(SELECT
E.ID,E.Name,AH.ID As AHID, AH.StartDate, AH.EndDate,((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.Hours) AS Hours
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID
WHERE
E.ID IN ('1')
)
SELECT
T1.Name,
SUM(T1.HOURS) AS FilteredHours,
SUM(T2.Hours) AS NonFiltered
FROM
TEMP_FILTER_BY_DATE T1, TEMP_NO_FILTER T2
WHERE
T1.StartDate >= '01-01-2011' AND T1.EndDate <= '02-10-2011'
GROUP BY
T1.Name
I need to return the SUM of the hours for each CTE, but am having difficulty determining how to setup the 2 CTE's in the final select statement.
I keep getting cartesian products or end up filtering out the non filtered hours so they end up being the same.
Currently this will return the following:
Name | FilteredHours | NonFiltered
Steve | 64 | 40
What I need it to return is:
Name | FilteredHours | NonFiltered
Steve | 20 | 40
Any suggestions?
Thanks!
November 17, 2011 at 2:39 pm
bubs (11/17/2011)
What I need it to return is:Name | FilteredHours | NonFiltered
Steve | 20 | 40
Where does the 20 come from? I get 16 from your filtered query.
The reason you're getting 64 hours is because you're not sub-querying your result set. What you need looks like this:
SELECT
E.[Name],
Filtered.SumOfFilteredHours,
UnFiltered.SumOfUnfilteredHours
FROM
@Employee E
LEFT JOIN
(SELECT [ID] AS eid, SUM( [Hours]) AS SumOfFilteredHours
FROM TEMP_FILTER_BY_DATE
GROUP BY [ID]
) AS Filtered
ONE.[ID] = Filtered.eid
LEFT JOIN
(SELECT [ID] AS eid, SUM( [Hours]) AS SumOfUnfilteredHours
FROM TEMP_NO_FILTER
GROUP BY [ID]
) AS UnFiltered
ONE.[ID] = UnFiltered.eid
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2011 at 2:48 pm
Your cartesian is because you are not using current join syntax on your two tables.
FROM
TEMP_FILTER_BY_DATE T1, TEMP_NO_FILTER T2
This is the old type join and will create a cartesian when you don't specify a join condition in the where clause.
I think you have a number of issues going on here though. Not really sure what value you are trying to get back. You are taking the number of days + 1 * the hours. Is this supposed to be 8 hours a day for every day from 1/1/2011 - 1/2/2011?
Shouldn't that be either 8 * 2 OR 8 * 32 (depending on your date format)?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 17, 2011 at 5:06 pm
Thanks for the quick response Craig, it's greatly appreciated.
Yes you are correct the filtered hours should be 16, not 20 in this example.
I tried to simplify my post as much as possible and remove a lot of the stuff to get to the point, which was how to implement the 2 CTE's in the final select statement. When I try to implement your solution into my actual query I am getting lost again.
Here's the actual query being used. I know, it's a nightmare, but my sql skills are fairly limited and I pieced this together as best I could.
If you have a more elegant solution I am all ears!
[Code]
WITH FILTERED AS
(SELECT
E.ID,
(E.LastName + ', ' + E.FirstName) AS Name,
AH.StartDate,
AH.EndDate,
AH.SpansWeekend,
AH.AbsenceTypeId,
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.AbsenceLength) AS "Hours",
V.TotalVacationDays,
V.RemainingVacationDays
FROM
HRIS_Employee E LEFT OUTER JOIN HRIS_AbsenceHistory AH ON E.ID = AH.EmployeeID AND
(AH.StartDate >= '01-01-2011' AND AH.EndDate <= '04-01-2011'),
(HRIS_Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM HRIS_Vacation
WHERE EmployeeID in ('41')
GROUP BY EmployeeID) T ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('41') AND
E.TerminationID IS NULL AND
E.ID = V.EmployeeID AND
(((AH.StartDate >= '01-01-2011' AND AH.EndDate <= '04-01-2011')) OR AH.ID IS NULL)
),
UNFILTERED AS
(SELECT
E.ID,
(E.LastName + ', ' + E.FirstName) AS Name,
AH.StartDate,
AH.EndDate,
AH.SpansWeekend,
AH.AbsenceTypeId,
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.AbsenceLength) AS 'Hours',
V.TotalVacationDays,
V.RemainingVacationDays
FROM
HRIS_Employee E LEFT OUTER JOIN HRIS_AbsenceHistory AH ON E.ID = AH.EmployeeID,
(HRIS_Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM HRIS_Vacation
WHERE EmployeeID in ('41')
GROUP BY EmployeeID) T ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('41') AND
E.TerminationID IS NULL AND
E.ID = V.EmployeeID
)
SELECT
F.Name,
F.TotalVacationDays,
(CASE
WHEN ((SELECT COUNT(FT.ID)FROM FILTERED FT) IS NOT NULL) OR ((SELECT COUNT(FT.ID)FROM FILTERED FT) < 1)
THEN F.RemainingVacationDays
WHEN (((SUM(UF.Hours) + F.RemainingVacationDays) > F.TotalVacationDays) AND F.AbsenceTypeID=2)
THEN (F.RemainingVacationDays + SUM(UF.Hours)) - SUM(CASE WHEN F.AbsenceTypeID=2 THEN F.Hours ELSE 0 END)
WHEN (((SUM(UF.Hours) + F.RemainingVacationDays) < F.TotalVacationDays) AND F.AbsenceTypeID=2)
THEN F.RemainingVacationDays
ELSE
(F.TotalVacationDays - SUM(CASE WHEN F.AbsenceTypeID='2' THEN F.Hours ELSE 0 END))
END) AS 'RemainingVacationDays'
FROM
FILTERED F, UNFILTERED UF
GROUP BY
F.Name, F.TotalVacationDays, F.RemainingVacationDays, F.AbsenceTypeID
[/code]
November 17, 2011 at 5:20 pm
Probably could whip something up, but I'm going to need similar schema/data samples as you originally provided for HRIS_Employee, HRIS_AbsenseHistory, HRIS_Vacation, why UnFiltered is using only the last entry logging component for Vacations, and why Filtered isn't looking for all vacations within the correct range.
I assume the EmployeeID = 41 and the date ranges are actually parameters you're just hard-coding for the moment and the proc will take as parameters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2011 at 6:46 pm
Thanks for your help Craig.
Here is the schema/data.
Yes, I have just hardcoded some values in there for simplicity sake.
I am only using the last entry in the vacations table becuase it has history in the table. There can be multiple entries for a single employee.
Not sure what you mean by "Filtered isn't looking for all vacations within the correct range".
DECLARE @HRIS_Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](25) NOT NULL,
[LastName] [varchar](25) NOT NULL,
[TerminationID] [int] NULL);
DECLARE @HRIS_AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[AbsenceTypeID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[AbsenceLength] [decimal](6, 2) NOT NULL);
DECLARE @HRIS_Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[TotalVacationDays] [decimal](6, 2) NULL,
[RemainingVacationDays] [decimal](6, 2) NULL)
INSERT INTO @HRIS_Employee
([FirstName]
,[LastName]
,[TerminationID])
VALUES
('John',
'Smith',
NULL);
INSERT INTO @HRIS_Vacation
([EmployeeID]
,[TotalVacationDays]
,[RemainingVacationDays])
VALUES
(1,
120,
254);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '01-01-2011', '01-01-2011', 8);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '02-01-2011', '02-01-2011', 8);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '03-01-2011', '03-02-2011', 8);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '05-01-2011', '05-03-2011', 8);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '07-15-2011', '07-15-2011', 8);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1, 2, '09-21-2011', '09-22-2011', 8);
WITH FILTERED AS
(SELECT
E.ID,
(E.LastName + ', ' + E.FirstName) AS Name,
AH.StartDate,
AH.EndDate,
AH.AbsenceTypeId,
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.AbsenceLength) AS "Hours",
V.TotalVacationDays,
V.RemainingVacationDays
FROM
@HRIS_Employee E LEFT OUTER JOIN @HRIS_AbsenceHistory AH ON E.ID = AH.EmployeeID AND
(AH.StartDate >= '01-01-2011' AND AH.EndDate <= '04-01-2011'),
(@HRIS_Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM @HRIS_Vacation
WHERE EmployeeID in ('1')
GROUP BY EmployeeID) T ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('1') AND
E.TerminationID IS NULL AND
E.ID = V.EmployeeID AND
(((AH.StartDate >= '01-01-2011' AND AH.EndDate <= '04-01-2011')) OR AH.ID IS NULL)
),
UNFILTERED AS
(SELECT
E.ID,
(E.LastName + ', ' + E.FirstName) AS Name,
AH.StartDate,
AH.EndDate,
AH.AbsenceTypeId,
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.AbsenceLength) AS 'Hours',
V.TotalVacationDays,
V.RemainingVacationDays
FROM
@HRIS_Employee E LEFT OUTER JOIN @HRIS_AbsenceHistory AH ON E.ID = AH.EmployeeID,
(@HRIS_Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM @HRIS_Vacation
WHERE EmployeeID in ('1')
GROUP BY EmployeeID) T ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('1') AND
E.TerminationID IS NULL AND
E.ID = V.EmployeeID
)
SELECT
F.Name,
F.TotalVacationDays,
(CASE
WHEN ((SELECT COUNT(FT.ID)FROM FILTERED FT) IS NOT NULL) OR ((SELECT COUNT(FT.ID)FROM FILTERED FT) < 1)
THEN F.RemainingVacationDays
WHEN (((SUM(UF.Hours) + F.RemainingVacationDays) > F.TotalVacationDays) AND F.AbsenceTypeID=2)
THEN (F.RemainingVacationDays + SUM(UF.Hours)) - SUM(CASE WHEN F.AbsenceTypeID=2 THEN F.Hours ELSE 0 END)
WHEN (((SUM(UF.Hours) + F.RemainingVacationDays) < F.TotalVacationDays) AND F.AbsenceTypeID=2)
THEN F.RemainingVacationDays
ELSE
(F.TotalVacationDays - SUM(CASE WHEN F.AbsenceTypeID=2 THEN F.Hours ELSE 0 END))
END) AS 'RemainingVacationDays'
FROM
FILTERED F, UNFILTERED UF
GROUP BY
F.Name, F.TotalVacationDays, F.RemainingVacationDays, F.AbsenceTypeID
November 19, 2011 at 12:42 pm
Can anyone please help me out?
November 19, 2011 at 5:57 pm
bubs (11/19/2011)
Can anyone please help me out?
Yes, but first can you answer these:
Why do you work in hours for absence history and days for vacation?
What is the meaning of TotalVacationDays and RemainingVacationDays? Especially given that in your example, TotalVacationDays is less than RemainingVacationDays.
Why are the RemainingVacationDays so high? 254 days is a lot! (Is this supposed to be RemainingVacationHours ? That would seem odd if a day is 8 hours -> 31.75 days ???)
If you can answer these, I can show you how to do this without CTEs in one pass.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 19, 2011 at 7:19 pm
Why do you work in hours for absence history and days for vacation?
The front end allows a user to enter a vacation entry by start/end date and then the length of hours per day. This is saved to the vacation history table by start/end date and hours per day. I know the vacation table columns says Days but it’s actually hours. It was originally days but we changed it later and just haven’t updated the column name yet..sorry, that’s confusing.
What is the meaning of TotalVacationDays and RemainingVacationDays? Especially given that in your example, TotalVacationDays is less than RemainingVacationDays.
The TotalVacationDays is the # of hours a user is entitled to during a given year. The RemainingVacationDays is the number of hours a user has remaining for a given year. It’s setup this way so I can handle carryover hours between years.
The report allows a user to select a start/end date to view remaining hours for selected employees. So I have to calculate the # of remaining hours based off the start/end date the user selected and the entries in the history table.
Why are the RemainingVacationDays so high? 254 days is a lot! (Is this supposed to be RemainingVacationHours ? That would seem odd if a day is 8 hours -> 31.75 days ???)
Again, it’s supposed to be hours.
Thanks!!!
November 19, 2011 at 7:35 pm
Maybe something like this?
SELECT E.ID
, (E.LastName + ', ' + E.FirstName) AS Name
, V.TotalVacationDays
, V.RemainingVacationDays
, sum(CASE AH.AbsenceTypeID
WHEN 2 THEN
(datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE
0
END) AS 'Hours Taken'
, V.RemainingVacationDays -
sum(CASE AH.AbsenceTypeID
WHEN 2 THEN
(datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE
0
END) AS 'Hours Remaining'
FROM
@HRIS_Employee E
LEFT OUTER JOIN @HRIS_AbsenceHistory AH
ON E.ID = AH.EmployeeID,
(@HRIS_Vacation V
INNER JOIN (SELECT max(id) id
, EmployeeID
FROM
@HRIS_Vacation
WHERE
EmployeeID IN ('1')
GROUP BY
EmployeeID) T
ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('1')
AND
E.TerminationID IS NULL
AND
E.ID = V.EmployeeID
GROUP BY
E.ID
, (E.LastName + ', ' + E.FirstName)
, V.TotalVacationDays
, V.RemainingVacationDays
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 20, 2011 at 2:59 pm
Thanks for the help mister.magoo!!
The query works great for retrieving the remaining vacation to date.
However, the report allows the user to select a date range to query the remaining vacation.
For example, an accountant wants to see how many vacation days were on the books at the end of March. So they would select a start date of 01-01-2011 and an end date of 03-31-2011. The query needs to take that date range into account. I need to see how many hours the users had remaining at the end of March. So in our example this particular user had taken 32 hours of vacation from Jan 1 to Mar 31, which should return 222 hours.
That's why I was using the 2 CTE's. One to query all entries for a particular user (UNFILTERED) and one to query entries that fall in the selected date range (FILTERED). Then I can calculate the remaining hours for given date range.
November 20, 2011 at 4:07 pm
bubs (11/20/2011)
Thanks for the help mister.magoo!!The query works great for retrieving the remaining vacation to date.
However, the report allows the user to select a date range to query the remaining vacation.
For example, an accountant wants to see how many vacation days were on the books at the end of March. So they would select a start date of 01-01-2011 and an end date of 03-31-2011. The query needs to take that date range into account. I need to see how many hours the users had remaining at the end of March. So in our example this particular user had taken 32 hours of vacation from Jan 1 to Mar 31, which should return 222 hours.
That's why I was using the 2 CTE's. One to query all entries for a particular user (UNFILTERED) and one to query entries that fall in the selected date range (FILTERED). Then I can calculate the remaining hours for given date range.
It's just a slight tweak to add in the date range selection...
DECLARE @SelStart Date, @SelEnd Date
SELECT @SelStart = '1 jan 2011'
, @SelEnd = '31 mar 2011'
SELECT E.ID
, (E.LastName + ', ' + E.FirstName) AS Name
, V.TotalVacationDays
, V.RemainingVacationDays
, sum(CASE AH.AbsenceTypeID
WHEN 2 THEN
CASE
WHEN AH.StartDate BETWEEN @SelStart AND @SelEnd THEN
CASE
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN
(datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE
(datediff(DAY, AH.StartDate, @SelEnd) + 1) * AH.AbsenceLength
END
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN
(datediff(DAY, @SelStart, AH.EndDate) + 1) * AH.AbsenceLength
END
ELSE
0
END) AS 'Hours Taken'
, V.RemainingVacationDays -
sum(CASE AH.AbsenceTypeID
WHEN 2 THEN
CASE
WHEN AH.StartDate BETWEEN @SelStart AND @SelEnd THEN
CASE
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN
(datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE
(datediff(DAY, AH.StartDate, @SelEnd) + 1) * AH.AbsenceLength
END
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN
(datediff(DAY, @SelStart, AH.EndDate) + 1) * AH.AbsenceLength
END
ELSE
0
END) AS 'Hours Remaining'
FROM
@HRIS_Employee E
LEFT OUTER JOIN @HRIS_AbsenceHistory AH
ON E.ID = AH.EmployeeID,
(@HRIS_Vacation V
INNER JOIN (SELECT max(id) id
, EmployeeID
FROM
@HRIS_Vacation
WHERE
EmployeeID IN ('1')
GROUP BY
EmployeeID) T
ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE
E.ID IN ('1')
AND
E.TerminationID IS NULL
AND
E.ID = V.EmployeeID
GROUP BY
E.ID
, (E.LastName + ', ' + E.FirstName)
, V.TotalVacationDays
, V.RemainingVacationDays
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 20, 2011 at 9:00 pm
Damnit, I messed another detail up...
The value in the RemainingVacationDays column was wrong. It should be 82, not 254.
254 was the RemainingVacationDays value on Jan 1, 2011. This is higher than the TotalVacationDays value due to carryover from 2010.
I made the changes to the data.
The RemainingVacationDays value will change whenever a user enters a new vacation request.
So in this example below, the Hours Remaining should be 82, but the calculation ends up being -90. It's subtracting the Total Hours Taken from the RemainingVacationDays. It should be (RemainingVacationDays + SUM(ALL entries In AbsenceHistory table)) - SUM(Entries in AbsenceHistory based on selected date range). Which would be (98 + 172) - 172. In this case the end date is set to dec 20, 2011, so all entries are included.
If the end date was set to say 20 mar 2011 then the calculation would be (98 + 172) - 36.
DECLARE @HRIS_Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](25) NOT NULL,
[LastName] [varchar](25) NOT NULL,
[TerminationID] [int] NULL);
DECLARE @HRIS_AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[AbsenceTypeID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[AbsenceLength] [decimal](6, 2) NOT NULL);
DECLARE @HRIS_Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[TotalVacationDays] [decimal](6, 2) NULL,
[RemainingVacationDays] [decimal](6, 2) NULL)
INSERT INTO @HRIS_Employee
([FirstName]
,[LastName]
,[TerminationID])
VALUES
('John',
'Smith',
NULL);
INSERT INTO @HRIS_Vacation
([EmployeeID]
,[TotalVacationDays]
,[RemainingVacationDays])
VALUES
(1,
120,
82);
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-02-07','2011-02-07',4)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-03-07','2011-03-10',8)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-04-04','2011-04-06',8)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-05-02','2011-05-02',4)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-06-06','2011-06-06',4)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-07-04','2011-07-04',8)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-08-08','2011-08-17',8)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-09-06','2011-09-06',12)
INSERT INTO @HRIS_AbsenceHistory ([EmployeeID],[AbsenceTypeID],[StartDate],[EndDate],[AbsenceLength])
VALUES(1,2,'2011-11-08','2011-11-08',4)
DECLARE @SelStart Date, @SelEnd Date
SELECT @SelStart = '1 jan 2011', @SelEnd = '20 dec 2011'
SELECT
E.ID,
(E.LastName + ', ' + E.FirstName) AS Name,
V.TotalVacationDays,
V.RemainingVacationDays,
sum(CASE AH.AbsenceTypeID WHEN 2
THEN CASE WHEN AH.StartDate BETWEEN @SelStart AND @SelEnd
THEN CASE WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN (datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE (datediff(DAY, AH.StartDate, @SelEnd) + 1) * AH.AbsenceLength END
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd THEN (datediff(DAY, @SelStart, AH.EndDate) + 1) * AH.AbsenceLength END ELSE 0 END) AS 'Hours Taken',
V.RemainingVacationDays - sum(CASE AH.AbsenceTypeID WHEN 2
THEN CASE WHEN AH.StartDate BETWEEN @SelStart AND @SelEnd
THEN CASE WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN (datediff(DAY, AH.StartDate, AH.EndDate) + 1) * AH.AbsenceLength
ELSE (datediff(DAY, AH.StartDate, @SelEnd) + 1) * AH.AbsenceLength
END
WHEN AH.EndDate BETWEEN @SelStart AND @SelEnd
THEN (datediff(DAY, @SelStart, AH.EndDate) + 1) * AH.AbsenceLength
END
ELSE 0 END) AS 'Hours Remaining'
FROM
@HRIS_Employee E
LEFT OUTER JOIN
@HRIS_AbsenceHistory AH ON E.ID = AH.EmployeeID,
(@HRIS_Vacation V
INNER JOIN (SELECT max(id) id,
EmployeeID
FROM
@HRIS_Vacation
WHERE
EmployeeID IN ('1')
GROUP BY
EmployeeID) T ON V.EmployeeID = V.EmployeeID AND V.id = t.id)
WHERE E.ID IN ('1') AND
E.TerminationID IS NULL AND E.ID = V.EmployeeID
GROUP BY E.ID, (E.LastName + ', ' + E.FirstName), V.TotalVacationDays, V.RemainingVacationDays
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply