2 CTE's and Case Expression - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

2 CTE's and Case Expression

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-11-17 : 05:37:45

I am trying to build a remaining vacation report, but have hit a wall with my noob sql skills.

I have the following tables:

DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](25) NOT NULL)

DECLARE @VACATION TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[TotalVacationDays] [decimal](6, 2) NULL,
[RemainingVacationDays] [decimal](6, 2) 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)

Here is some test data:

INSERT INTO @EMPLOYEE
(Name)
VALUES
(Steve)

INSERT INTO @Vacation
(EmployeeID, TotalVacationDays, RemainingVacationDays)
VALUES
(1, 120, 200)

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)

In this example, Steve has a bunch of carryover hours from the previous year.

I need to be able to filter the report based on a starting and ending date so I can see the remaining hours that existed for the selected time period. So in this example if I selected a start date of 01-01-2011 and an end date of 03-10-2011 only the first 2 items from the AbsenceHistory table would be included. The remaining vacation hours should be 200 - (2 days * 8 hours + 1 day * 8 hours) = 176.

I am getting closer, but am really struggling with it.
Here is the query I have been working on:

WITH TEMP AS
(SELECT
E.ID,
E.Name,
AH.StartDate,
AH.EndDate,
((DATEDIFF(day, AH.StartDate, AH.EndDate) +1) * AH.Hours) AS Hours,
V.TotalVacationDays,
V.RemainingVacationDays
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID AND
(AH.StartDate >= '01-01-2011' AND AH.EndDate <= '03-10-2011'),
(@Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID FROM @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.ID = V.EmployeeID AND
((AH.StartDate >= '01-01-2011' AND AH.EndDate <= '03-10-2011') OR AH.ID IS NULL)
)
SELECT
TMP.Name,
TMP.TotalVacationDays,
(CASE
WHEN ((SELECT COUNT(T.ID)FROM TEMP T) IS NOT NULL)
THEN TMP.RemainingVacationDays
WHEN (((SUM(Hours) + TMP.RemainingVacationDays) > TMP.TotalVacationDays))
THEN (TMP.RemainingVacationDays + SUM(Hours)) - SUM(Hours)
WHEN (((SUM(Hours) + TMP.RemainingVacationDays) < TMP.TotalVacationDays))
THEN TMP.RemainingVacationDays
ELSE
(TMP.TotalVacationDays - SUM(Hours))
END) AS 'RemainingVacationDays'
FROM
TEMP TMP
GROUP BY
TMP.Name, TMP.TotalVacationDays, TMP.RemainingVacationDays

The query needs to handle 4 different scenarios and I am using the Case expression to try and get the result based on one of the 4 scenarios.

1. if employee has carryover hours and no entries in the AbsenceHistory Table
Then return the TotalRemainingHours from the Vacation Table.

2. if employee has carryover hours and has entries in AbsenceHistory
Then return (sum of hours in history table + TotalRemainingVacation) - (sum of hours in history table based on start/end date.)

3. if employee has no carryover and no entries in absence history
Then return TotalRemainingHours

4. if employee has no carryover and has entries in absence history
Then return TotalVacationHours - (Sum of hours in history table based on start/end date.)

I am thinking I need to create a 2nd temp table similar to the one I am using, except with no data filtered based on the date. That way I can calculate the remaining hours based off the 2 tables. One containing all the absencehistory data, and one that contains the data based on date filter. But I don't know how to get that 2nd temp table working, or if that's even the correct idea.

Any advice on how to get this working would be greatly appreciated. It's really starting to hurt my head!

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-17 : 05:58:15

Haven't looked at the query but if you need multiple ctes

;with
cte1 as (select .....) ,
cte2 as (select ....) ,
cte3 as (select ....)
select
from ...

The ctes can reference the previous ones if you need to.

I've had a quick look as you've been kind enough to give test data but it's a bit too complicated to do at the moment.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-11-17 : 12:01:22

Thanks Nigel,

after a little searching I discovered how to add multiple cte's.
My problem is in the select statement using those cte's.

I need the SUM of the Hours from each cte in the final select statement, but I don't know how to get the sums of both tables. The FROM, WHERE, and Group By Clauses always end up filtering down the TEMP2 data. So the TEMP and TEMP2 data just end up being the same. See the new queries below.

DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](25) NOT NULL);

DECLARE @VACATION TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[TotalVacationDays] [decimal](6, 2) NULL,
[RemainingVacationDays] [decimal](6, 2) 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 @Vacation
(EmployeeID, TotalVacationDays, RemainingVacationDays)
VALUES
(1, 120, 214);

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 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,V.TotalVacationDays,V.RemainingVacationDays
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'),
(@Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM @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.ID = V.EmployeeID AND
((AH.StartDate >= '01-01-2011' AND AH.EndDate <= '02-10-2011') OR AH.ID IS NULL)
),
TEMP2 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,V.TotalVacationDays,V.RemainingVacationDays
FROM
@Employee E LEFT OUTER JOIN @AbsenceHistory AH ON E.ID = AH.EmployeeID,
(@Vacation V INNER JOIN (SELECT MAX(id) id,EmployeeID
FROM @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.ID = V.EmployeeID
)
SELECT
TMP.Name,
TMP.TotalVacationDays,
TMP.RemainingVacationDays,
SUM(TMP.HOURS),
SUM(TMP2.Hours),
(CASE
WHEN ((SELECT COUNT(T.ID)FROM TEMP T) IS NOT NULL) AND (SELECT COUNT(T.ID)FROM TEMP T) < 1
THEN TMP.RemainingVacationDays
WHEN (((SUM(TMP2.Hours) + TMP.RemainingVacationDays) > TMP.TotalVacationDays))
THEN (TMP.RemainingVacationDays + SUM(TMP2.Hours)) - SUM(TMP.Hours)
WHEN (((SUM(TMP2.Hours) + TMP.RemainingVacationDays) < TMP.TotalVacationDays))
THEN TMP.RemainingVacationDays
ELSE
(TMP.TotalVacationDays - SUM(TMP.Hours))
END) AS 'RemainingVacationDays'
FROM
TEMP TMP, TEMP2 TMP2
WHERE
TMP.StartDate >= '01-01-2011' AND TMP.EndDate <= '02-10-2011' AND
TMP.AHID = TMP2.AHID
GROUP BY
TMP.Name, TMP.TotalVacationDays, TMP.RemainingVacationDays

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources