September 10, 2008 at 9:04 am
/*
I have SQL that identifys overlaps in employee assignments and places the overlapped rows into temporary table #OVERLAP.
Each employee's overlapped rows contain a percentage.
I need to identify when the sum of the percentages for an employee with overlaps exceeds 1.
The SQL below works for EmpID 2, but for EmpID 1 the SQL incorrectly sums the 3 overlapped records for
ALog = 353 to 1.6 and it should just be 1.
That is:
352 03/08/2008 thru 03/31/2008 Percent = 0.6 |-----------|
353 03/08/2008 thru 04/04/2008 Percent = 0.4 |------------------|
354 04/01/2008 thru 04/04/2008 Percent = 0.6 |----|
My incorrect SQL results in Alog of 353 totaling to 1.6 when it should only be 1.
I hope I have stated the problem clearly.
Can this be accomplished in SQL or do I need to define a Cursor and go after this row by row?
*/
if object_id('tempdb..#OVERLAP','U') is not null
DROP TABLE #OVERLAP
CREATE TABLE #OVERLAP
(
ALog INT,
ABegin DATETIME,
AEnd DATETIME,
EmpID char(11),
APercent float,
PRIMARY KEY (ALog, ABegin, AEnd)
)
insert into #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)
select 352, '03/08/2008', '03/31/2008', 1, 0.6 union all
select 353, '03/08/2008', '04/04/2008', 1, 0.4 union all
select 354, '04/01/2008', '04/04/2008', 1, 0.6 union all
select 8347, '01/01/2008', '04/30/2008', 2, 0.33 union all
select 4381, '01/01/2008', '01/31/2008', 2, 0.31 union all
select 4382, '02/01/2008', '02/28/2008', 2, 0.31 union all
select 4383, '03/01/2008', '03/31/2008', 2, 0.98 union all
select 4384, '04/01/2008', '04/30/2008', 2, 0.31
if object_id('tempdb..#TOTPERCNT','U') is not null
DROP TABLE #TOTPERCNT
CREATE TABLE #TOTPERCNT
(
EmpID char(11),
ALog INT,
ABegin Datetime,
AEnd Datetime,
TotPrcnt float,
RowsInOverlap INT,
PRIMARY KEY (EmpID, ALog, ABegin, AEnd)
)
INSERT INTO #TOTPERCNT (EmpID, ALog, ABegin, AEnd, TotPrcnt, RowsInOverlap)
select T1.EmpID, T1.ALog, T1.ABegin As ABegin, T1.AEnd As AEnd, Sum(T2.APercent) as TotPrcnt, Count(T2.APercent) As RowsInOverlap
FROM #OVERLAP As T1 INNER JOIN #OVERLAP AS T2 ON T1.EmpID = T2.EmpID
Where (T1.ABegin Between T2.ABegin And T2.AEnd) OR (T2.ABegin Between T1.ABegin ANd T1.AEnd)
Group By T1.EmpID, T1.ALog, T1.ABegin, T1.AEnd
Order By T1.EmpID, T1.ALog
SELECT *
FROM #OVERLAP
SELECT *
FROM #TOTPERCNT
September 12, 2008 at 12:56 pm
I have the solution to my isuue.
The complete thread can be found here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508">
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508
You can't have both logic into same query!
Either use this logic
For EmpID 1
ALog 1 overlaps with ALog 2 0.6 + 0.4 = 1.0
ALog 2 overlaps with ALogs 1,30.4 + 0.6 + 0.6 = 1.6
ALog 3 overlaps with ALog 2 0.6 + 0.4 = 1.0
For EmpID 2
ALog 4 overlaps with ALogs 5,6,7,80.33 + 0.31 + 0.31 + 0.98 + 0.31 = 2.24
ALog 5 overlaps with ALog 40.31 + 0.33 = 0.64
ALog 6 overlaps with ALog 40.31 + 0.33 = 0.64
ALog 7 overlaps with ALog 40.98 + 0.33 = 1.31
ALog 8 overlaps with ALog 40.31 + 0.33 = 0.64
or use this logic
For EmpID 1
ALog 1 overlaps with ALog 2 0.6 + 0.4 = 1.0
ALog 2 overlaps with ALogs 10.4 + 0.6 = 1.0
ALog 2 overlaps with ALogs 30.4 + 0.6 = 1.0
ALog 3 overlaps with ALog 2 0.6 + 0.4 = 1.0
ALog 1 = 1.0
ALog 2 = 1.0 (max of the two values above)
ALog 3 = 1.0
For EmpID 2
ALog 4 overlaps with ALogs 50.33 + 0.31 = 0.64
ALog 4 overlaps with ALogs 60.33 + 0.31 = 0.64
ALog 4 overlaps with ALogs 70.33 + 0.98 = 1.31
ALog 4 overlaps with ALogs 80.33 + 0.31 = 0.64
ALog 5 overlaps with ALog 40.31 + 0.33 = 0.64
ALog 6 overlaps with ALog 40.31 + 0.33 = 0.64
ALog 7 overlaps with ALog 40.98 + 0.33 = 1.31
ALog 8 overlaps with ALog 40.31 + 0.33 = 0.64
ALog 4 = 1.31 (max of the four values above)
ALog 5 = 0.64
ALog 6 = 0.64
ALog 7 = 1.31
ALog 8 = 0.64
You decide...
if object_id('tempdb..#OVERLAP','U') is not null
DROP TABLE #OVERLAP
CREATE TABLE #OVERLAP
(
ALog INT,
ABegin DATETIME,
AEnd DATETIME,
EmpID char(11),
APercent float,
PRIMARY KEY (ALog, ABegin, AEnd)
)
insert into #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)
select 1, '03/08/2008', '03/31/2008', 1, 0.6 union all
select 2, '03/08/2008', '04/04/2008', 1, 0.4 union all
select 3, '04/01/2008', '04/04/2008', 1, 0.6 union all
select 4, '01/01/2008', '04/30/2008', 2, 0.33 union all
select 5, '01/01/2008', '01/31/2008', 2, 0.31 union all
select 6, '02/01/2008', '02/28/2008', 2, 0.31 union all
select 7, '03/01/2008', '03/31/2008', 2, 0.98 union all
select 8, '04/01/2008', '04/30/2008', 2, 0.31
SELECT T1.EmpID, T1.ALog, T1.APercent,
right('0' + rtrim(month(T1.ABegin)),2) + '/' + right('0' + rtrim(day(T1.ABegin)),2) + '/' + rtrim(year(T1.ABegin)) AS BDte,
right('0' + rtrim(month(T1.AEnd)),2) + '/' + right('0' + rtrim(day(T1.AEnd)),2) + '/' + rtrim(year(T1.AEnd)) AS EDte,
MAX(T2.APercent + T1.APercent) as MaxPercentOverlapped
FROM #OVERLAP As T1 INNER JOIN #OVERLAP AS T2 ON T1.EmpID = T2.EmpID
Where ((T1.ABegin Between T2.ABegin And T2.AEnd) Or (T2.ABegin Between T1.ABegin ANd T1.AEnd)) And T1.ALog <> T2.ALog
GROUP BY T1.EmpID, T1.ALog, T1.ABegin, T1.AEnd, T1.APercent
September 15, 2008 at 8:52 am
I have discovered that the above SQL fails if an additional activity log for EmpID 2 from 2008-04-01 to 2008-04-30 at 68% is added.
The above SQL (with the data set below) will report that for EmpID 2 ALogs 4, 7 and 9 are > 1.
Alog 8 is reported at .99 and should be 1.32!!
Any ideas???
Data set
insert into #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)
select 1, '03/08/2008', '03/31/2008', 1, 0.6 union all
select 2, '03/08/2008', '04/04/2008', 1, 0.4 union all
select 3, '04/01/2008', '04/04/2008', 1, 0.6 union all
select 4, '01/01/2008', '04/30/2008', 2, 0.33 union all
select 5, '01/01/2008', '01/31/2008', 2, 0.31 union all
select 6, '02/01/2008', '02/28/2008', 2, 0.31 union all
select 7, '03/01/2008', '03/31/2008', 2, 0.98 union all
select 8, '04/01/2008', '04/30/2008', 2, 0.31 union all
select 9, '04/01/2008', '04/30/2008', 2, 0.68
September 15, 2008 at 9:10 am
Here is a solution written by Peso... original post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508">
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508
DECLARE@Sample TABLE
(
aLog INT,
aBegin DATETIME,
aEnd DATETIME,
EmpID INT,
aPercent SMALLMONEY
)
INSERT@Sample
SELECT1, '03/08/2008', '03/31/2008', 1, 0.6 UNION ALL
SELECT2, '03/08/2008', '04/04/2008', 1, 0.4 UNION ALL
SELECT3, '04/01/2008', '04/04/2008', 1, 0.6 UNION ALL
SELECT4, '01/01/2008', '04/30/2008', 2, 0.33 UNION ALL
SELECT5, '01/01/2008', '01/31/2008', 2, 0.31 UNION ALL
SELECT6, '02/01/2008', '02/28/2008', 2, 0.31 UNION ALL
SELECT7, '03/01/2008', '03/31/2008', 2, 0.98 UNION ALL
SELECT8, '04/01/2008', '04/30/2008', 2, 0.31 UNION ALL
SELECT9, '04/01/2008', '04/30/2008', 2, 0.68
;WITH Yak(EmpID, theDate, aEnd)
AS (
SELECTEmpID,
MIN(aBegin),
MAX(aBegin)
FROM@Sample
GROUP BYEmpID
UNION ALL
SELECTEmpID,
DATEADD(DAY, 1, theDate),
aEnd
FROMYak
WHEREtheDate < aEnd
)
SELECTaLog,
MAX(thePercent) AS aPercentTotal
FROM(
SELECTs.aLog,
SUM(s.aPercent) OVER (PARTITION BY y.EmpID, y.theDate) AS thePercent
FROMYak AS y
LEFT JOIN@Sample AS s ON s.EmpID = y.EmpID
AND s.aBegin <= y.theDate
AND s.aEnd >= y.theDate
) AS d
GROUP BYaLog
ORDER BYaLog
September 15, 2008 at 9:46 am
Is this what you're looking for?
[font="Courier New"]SET DATEFORMAT mdy
DROP TABLE #OVERLAP
CREATE TABLE #OVERLAP
(
ALog INT,
ABegin DATETIME,
AEnd DATETIME,
EmpID CHAR(11),
APercent FLOAT,
PRIMARY KEY (ALog, ABegin, AEnd)
)
INSERT INTO #OVERLAP (ALog, ABegin, AEnd, EmpID, APercent)
SELECT 1, '03/08/2008', '03/31/2008', 1, 0.6 UNION ALL
SELECT 2, '03/08/2008', '04/04/2008', 1, 0.4 UNION ALL
SELECT 3, '04/01/2008', '04/04/2008', 1, 0.6 UNION ALL
SELECT 4, '01/01/2008', '04/30/2008', 2, 0.33 UNION ALL
SELECT 5, '01/01/2008', '01/31/2008', 2, 0.31 UNION ALL
SELECT 6, '02/01/2008', '02/28/2008', 2, 0.31 UNION ALL
SELECT 7, '03/01/2008', '03/31/2008', 2, 0.98 UNION ALL
SELECT 8, '04/01/2008', '04/30/2008', 2, 0.31 --union all
--select 9, '04/01/2008', '04/30/2008', 2, 0.68
SELECT a.EmpID, a.ALog, a.ABegin, a.AEnd, COUNT(*) AS RowsInOverlap,
--a.APercent,
--SUM(b.APercent) AS ChildPercentTotal,
--a.APercent + SUM(b.APercent) AS TotPrcnt,
MAX(a.APercent + b.APercent) AS MaxPercent
FROM #OVERLAP a
LEFT JOIN #OVERLAP b ON b.EmpID = a.EmpID AND b.ALog <> a.ALog
AND ((b.ABegin BETWEEN a.ABegin AND a.AEnd OR b.AEnd BETWEEN a.ABegin AND a.AEnd)
OR (a.ABegin BETWEEN b.ABegin AND b.AEnd OR a.AEnd BETWEEN b.ABegin AND b.AEnd))
GROUP BY a.EmpID, a.ALog, a.ABegin, a.AEnd, a.APercent
ORDER BY a.EmpID, a.ALog
/*
ALog 4 = 1.31 (max of the four values above)
ALog 5 = 0.64
ALog 6 = 0.64
ALog 7 = 1.31
ALog 8 = 0.64
*/
[/font]
Results:
EmpID ALog ABegin AEnd RowsInOverlap MaxPercent
----- ----- ------------------------ ------------------------ ------------- -------------------
1 1 2008-03-08 00:00:00.000 2008-03-31 00:00:00.000 1 1.0
1 2 2008-03-08 00:00:00.000 2008-04-04 00:00:00.000 2 1.0
1 3 2008-04-01 00:00:00.000 2008-04-04 00:00:00.000 1 1.0
2 4 2008-01-01 00:00:00.000 2008-04-30 00:00:00.000 4 1.3100000000000001
2 5 2008-01-01 00:00:00.000 2008-01-31 00:00:00.000 1 0.64000000000000001
2 6 2008-02-01 00:00:00.000 2008-02-28 00:00:00.000 1 0.64000000000000001
2 7 2008-03-01 00:00:00.000 2008-03-31 00:00:00.000 1 1.3100000000000001
2 8 2008-04-01 00:00:00.000 2008-04-30 00:00:00.000 1 0.64000000000000001
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 15, 2008 at 12:12 pm
Chris, Thank you for taking the time to review this issue.
Your SQL is correct for the EmpID 2 with only Alogs 4 through 8.
But when I uncomment the code and run with EmpID 2 having ALogs 4 through 9...
The MaxPercent column is correct for all rows except EmpID 2 ALog 8 = 0.99 and the value I am looking for would be 1.32. Alog 8 = 0.31 overlaps with Alog 4 = 0.33 and 9 = 0.68.
Thoughts...
September 15, 2008 at 12:44 pm
jwpquinn (9/15/2008)
Chris, Thank you for taking the time to review this issue.Your SQL is correct for the EmpID 2 with only Alogs 4 through 8.
But when I uncomment the code and run with EmpID 2 having ALogs 4 through 9...
The MaxPercent column is correct for all rows except EmpID 2 ALog 8 = 0.99 and the value I am looking for would be 1.32. Alog 8 = 0.31 overlaps with Alog 4 = 0.33 and 9 = 0.68.
Thoughts...
Surely that's correct? Alog 8 = 0.31 plus Alog 9 = 0.68 gives a max percent of 0.99, not 1.32.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 15, 2008 at 4:04 pm
Yes, the SQL is correct, but the logic is not producing the desired result.
EmpID 2 ALog 4 1/1/2008 - 4/30/2008 0.33
EmpID 2 ALog 8 4/1/2008 - 4/30/2008 0.31
EmpID 2 ALog 9 4/1/2008 - 4/30/2008 0.68
ALog4 1/1--------------------------------------------4/30
ALog8 4/1-----4/30
ALog9 4/1-----4/30
All share common days of overlap so all must be in the total calulation.
0.33 + 0.31 +0.68 = 1.32
September 16, 2008 at 12:53 am
So, some of the time you take the maximum value of the overlaps, and other times you take the sum of the overlaps. What's the logic for the decision?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 16, 2008 at 7:45 am
I have not clearly defined my question and the parameters of what I am after.
The failure is mine, to be able have others be able to help, one must clearly define the question first.
It is my failure to be able to do so that has made this soo hard... I am working on it.
I have identified for each EmpID ALogs that have overlaps. Now I am trying to identify each ALog's percentage overlapped that is > 1. The percentages represent the portion of an FTE that was claimed for that EmpID during that time frame. If at any point in time during the time frame the ALog overlaps with another ALog(s) for the same EmpID and the sum of all the ALog percentages involved are > 1, then I need to flag those ALogs for further processing. One ALog could overlap with 1, 2, 3, 4 or more other ALog entries. Whether all or only one is involved in the sum is determined by if all have days in common in the overlap, if so all will have their percentages added in, if one overlaps with all of them, but there are no(or only some) common days between all the rest of them. Then all the individual overlaps that have days in common will have their percentages summed and the Max(of all the overlapped percentages for a particular ALog) is to be used. Using the Max of all possible ALog percentages that are overlapped (that is percentages are summed only if all overlapped ALogs have common days) for a particular ALog thus gives me the highest result and I can identify if at any point in time in a ALog's date range it is > 1 and I have to flag it for further processing.
This code written by Peso yields the results I am looking for, the thread for which can be read here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508">
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110508
His solution
DECLARE @Sample TABLE
(
aLog INT,
aBegin DATETIME,
aEnd DATETIME,
EmpID INT,
aPercent SMALLMONEY
)
INSERT @Sample
SELECT 1, '03/08/2008', '03/31/2008', 1, 0.6 UNION ALL
SELECT 2, '03/08/2008', '04/04/2008', 1, 0.4 UNION ALL
SELECT 3, '04/01/2008', '04/04/2008', 1, 0.6 UNION ALL
SELECT 4, '01/01/2008', '04/30/2008', 2, 0.33 UNION ALL
SELECT 5, '01/01/2008', '01/31/2008', 2, 0.31 UNION ALL
SELECT 6, '02/01/2008', '02/28/2008', 2, 0.31 UNION ALL
SELECT 7, '03/01/2008', '03/31/2008', 2, 0.98 UNION ALL
SELECT 8, '04/01/2008', '04/30/2008', 2, 0.31 UNION ALL
SELECT 9, '04/01/2008', '04/30/2008', 2, 0.68
;WITH Yak(EmpID, theDate, aEnd)
AS (
SELECT EmpID,
MIN(aBegin),
MAX(aBegin)
FROM @Sample
GROUP BY EmpID
UNION ALL
SELECT EmpID,
DATEADD(DAY, 1, theDate),
aEnd
FROM Yak
WHERE theDate < aEnd
)
SELECT aLog,
MAX(thePercent) AS aPercentTotal
FROM (
SELECT s.aLog,
SUM(s.aPercent) OVER (PARTITION BY y.EmpID, y.theDate) AS thePercent
FROM Yak AS y
LEFT JOIN @Sample AS s ON s.EmpID = y.EmpID
AND s.aBegin <= y.theDate
AND s.aEnd >= y.theDate
) AS d
GROUP BY aLog
ORDER BY aLog
results
(9 row(s) affected)
aLog aPercentTotal
----------- ---------------------
1 1.00
2 1.00
3 1.00
4 1.32
5 0.64
6 0.64
7 1.31
8 1.32
9 1.32
(9 row(s) affected)
Thank you for taking the time to review this issue. I do appreciate it!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply