Join on dates, but exclude others ... ?
-
There's probably a simple solution, but I think I'm too close to this and your ideas are appreciated. I, like everyone else, need the most efficient solution possible.
[EDITED]
What I need to do is...
- get all records from the employee table for a specific office
- include any employees from the loaned table, loaned to that office where the loan date covers a supplied date
- exclude any employees from the loaned table, loaned from that office where the loan date covers a supplied date
Using this sample data ...
CREATE TABLE employee
(employeeID INT IDENTITY(1,1)
,lastName VARCHAR(50)
,officeID INT)
CREATE TABLE loaned
(employeeID INT
,homeOfficeID INT
,loanedOfficeID INT
,startDateTime DATETIME
,endDateTime DATETIME)
INSERT INTO employee VALUES ('Smith', 1)
INSERT INTO employee VALUES ('Jones', 1)
INSERT INTO employee VALUES ('Lewis', 1)
INSERT INTO employee VALUES ('Johnson', 1)
INSERT INTO employee VALUES ('Barker', 2)
INSERT INTO employee VALUES ('Williams', 2)
INSERT INTO employee VALUES ('Jackson', 2)
INSERT INTO loaned VALUES (6, 2, 1, '4/1/2007', '4/25/2007')
INSERT INTO loaned VALUES (7, 2, 1, '4/5/2007', '4/25/2007')
INSERT INTO loaned VALUES (2, 1, 2, '4/1/2007', '4/25/2007')
INSERT INTO loaned VALUES (3, 1, 2, '4/5/2007', '4/25/2007')
The results required would be
officeID = 1, date = '4/4/2007'
Smith, Lewis, Johnson and Williams
officeID = 1, date = '4/7/2007'
Smith, Johnson, Williams and Jackson
officeID = 2, date = '4/4/2007'
Jones, Barker and Jackson
officeID = 2, date = '4/7/2007'
Jones, Lewis and Barker
Thanks in advance!
______________________________________________________________________
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. Selburg -
Outstanding post, Jason... everything is there and very easy to read... while I'm looking at this, tell me what you've tried just so reassure me that this isn't homework, please.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
LOL, it's homework alright, but from work!
I'll just post my section of the procedure. I think that should be enough to show that this isn't a "canned" question. Keep in mind that I KNOW this procedure isn't efficient, which is why I'm here.... *grin*
[EDIT]
BTW, this does work, but it's a HOG!
And to keep the post short, I'm only posting part of it...
INSERT #EFS
SELECT DISTINCT
e.EmployeeID
,e.EmployeePartyID
,e.HireDate
,e.PayrollNumber
,e.WageType
,e.DepartmentID
,e.homeOfficeID
,borrowingOfficeID =
CASE
WHEN @scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime) THEN
isnull(ol.borrowingOfficeID, e.homeOfficeID)
ELSE
e.homeOfficeID
END
,assignedToOfficeID =
CASE
WHEN @scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime) THEN
isnull(ol.borrowingOfficeID, e.homeOfficeID)
ELSE
e.homeOfficeID
END
,isScheduler = 0
,e.locationName
,e.primaryEmail
,e.jobTitle2
,loanStartDateTime =
CASE
WHEN @scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime) THEN
ol.startDateTime
ELSE
NULL
END
,loanEndDateTime =
CASE
WHEN @scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime) THEN
ol.endDateTime
ELSE
NULL
END
,reassignmentID =
CASE
WHEN @scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime) THEN
ol.reAssignmentID
ELSE
0
END
,e.IsTemp
FROM
dbo.vw_schedule_employees e
LEFT JOIN dbo.vw_employee_onLoan et -- don't include employees in transit
ON et.employeePartyID = e.employeePartyID
and et.reassignmentAccepted = 0
LEFT JOIN dbo.vw_employee_onLoan ol -- include the borrowed employee info
ON ol.employeePartyID = e.employeePartyID
and ol.reassignmentAccepted = 1
WHERE
isnull(et.employeePartyID, 0) = 0 AND
e.HireDate <= @scheduleDate AND
e.TerminateDate >= @scheduleDate AND
(ISNULL(ol.borrowingOfficeID, e.homeOfficeID) = @assignedOfficeID AND
@scheduleDate between dbo.standardDate(ol.StartDateTime)
and dbo.standardDate(ol.EndDateTime)
or e.homeOfficeID = @assignedOfficeID)
I've stripped out some extraneous columns and such. The vw_schedule_employees give all employees and the vw_employee_onloan gives all employees on loan.
FYI, I'm currently working on a solution using CTEs, and the new EXCEPT keyword in 2005. If I get the answer before you, I'll post it.
Thanks in advance.
______________________________________________________________________
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. Selburg -
Heh... sorry Jason... had to make sure... the post was too perfect (well done, by the way!!!)... you should teach! You had "Use Cases" and everything!
Anyway, using the test tables and data you posted, here's what I came up with... it's the way I try to get my guys to code (Divide and conquer)... each part of the code can be tested individually and when you put it all together, the whole of the code runs faster (usually, MUCH faster) than the sum of the parts... also makes documenting the code a bit easier...
--===== Declare some local variables that could be parameters in a stored proc
DECLARE @OfficeIDToReport INT
DECLARE @SuppliedDate DATETIME
--===== Preset those variables as if set by parameters in a stored proc
SET @OfficeIDToReport = 1
SET @SuppliedDate = '04/04/2007'
--===== Solve the problem in easy to read/test/troubleshoot individual modules
SELECT a.*
FROM Employee a
INNER JOIN
(--==== Finds all employees in the Employee table for a specific office
SELECT e.EmployeeID
FROM Employee e
WHERE e.OfficeID = @OfficeIDToReport
UNION ALL
--===== Finds employees loaned TO that office where the loan date covers a supplied date
SELECT l.EmployeeID
FROM Loaned l
WHERE l.LoanedOfficeID = @OfficeIDToReport
AND @SuppliedDate >= l.StartDateTime
AND @SuppliedDate < l.EndDateTime
) inc
ON a.EmployeeID = inc.EmployeeID
LEFT OUTER JOIN
(--==== Derived table "exc" (exclude) finds employees loaned FROM that office where the loan date covers a supplied date
SELECT l.EmployeeID
FROM Loaned l
WHERE l.HomeOfficeID = @OfficeIDToReport
AND @SuppliedDate >= l.StartDateTime
AND @SuppliedDate < l.EndDateTime
) exc
ON inc.EmployeeID = exc.EmployeeID
WHERE exc.EmployeeID IS NULL --ID is not in the exclusion
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
By the way... it looks like it'll be a tiny bit faster if you change this...
ON inc.EmployeeID = exc.EmployeeID
... to this...
ON a.EmployeeID = exc.EmployeeID
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.p.s..... you don't need a CTE for this... the derived tables have nothing in common. EXCLUDE will probably work but that's a poor man's excuse for a good solid OUTER JOIN with a NULL detector. Dunno for sure because they may have optimized EXCLUDE more than OUTER JOIN. I don't have 2k5 to test with...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
FIRST, thank you for the reply!
SECOND, it's funny. Right when you posted this solution I came upon my own. I'm anxious to see which performs better. not that this is a competition, but a challenge. *grin*
Granted, I posted this in the 2000 forum, I'm using 2005 ffeatures, but you never know wher the best solution lies.
Below is my solution (with bits of your code)....
--===== Declare some local variables that could be parameters in a stored proc
DECLARE @OfficeIDToReport INT
DECLARE @SuppliedDate DATETIME
--===== Preset those variables as if set by parameters in a stored proc
SET @OfficeIDToReport = 1
SET @SuppliedDate = '04/04/2007'
--===== Solve the problem in easy to read/test/troubleshoot individual modules
SELECT
employeeID
FROM
employee
WHERE
OfficeID = @OfficeIDToReport -- for this office
UNION
--===== Include employees loaned TO that office where the loan date covers a supplied date
SELECT
EmployeeID
FROM
borrowed
WHERE
(loanedOfficeID = @OfficeIDToReport OR homeOfficeId = @OfficeIDToReport)
AND @SuppliedDate between startDateTime and endDateTime
EXCEPT
--==== Using the new EXCLUDE keyword to remove employees loaned FROM that office where the loan date covers a supplied date
SELECT
EmployeeID
FROM loaned
WHERE
homeOfficeID = @OfficeIDToReport
AND @SuppliedDate < startDateTime
AND @SuppliedDate < endDateTime
______________________________________________________________________
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. Selburg -
Well, fortunately I do have 2k5 so I'll take the time to create my proc using your solution and post the results. Besides, even if it doesn't work better it's a learning experience. After all, isn't that what we all do?
Test = FAIL
Test = FAIL
Test = FAIL
Test = SUCCESS
______________________________________________________________________
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. Selburg -
Uh oh... whose tests failed? I was pretty sure I tested all 4 scenarios you posted and was pretty sure they came out right. 'Course, it's late and I might be a bit blind, at the moment.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
NO, I was saying that's how we all learn. TEST, then TEST, then TEST until we figure it out.
Your method worked fine.
______________________________________________________________________
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. Selburg -
Whew! Thought I had a "senior moment" or something. Thanks for the feed back.
I used the date comparisons I did because I didn't know if times were involved nor whether the end date was exclusive or inclusive. I just made the assumption that times could be involved and that the end dates should be exclusive. BETWEEN will make the end date/time inclusive. That's ok if that's what you want. 'Course, look who I'm telling... you already new that. I just had to say it to make myself feel better.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I had to account for loaned employees that were loaned for a date
(4/1/2007 00:00:00 to 4/2/1/2007 00:00:00) and have them not show in the list
BUT
(4/1/2007 00:00:00 to 4/1/2007 17:30:30) show in the list, as they were loaned for "part" of the day.
BTW, in my humble opinion, SQL 2k5's PIVOT, UNPIVOT, ROW_NUMBER, EXCEPT and INTERSECT are pretty sweet!
______________________________________________________________________
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. Selburg -
Oh yeah... I like the ROW_Number thing, especially. Haven't had the pleasure of using it in 2k5 but have done some cool things with it in (yeech) Oracle.
On the time thing... that's why I don't use BETWEEN on date comparisons. You can get around the partial day by doing the DATEADD(DATEDIFF()) thing to strip off the time and add 1 to the date... then the >= < method works a treat on finding those with partial days.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply