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.
| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2008-07-11 : 02:27:55
|
| HI, I am using this site for the first time. So i am posting an issue in this page. I have to choose a set of columns based on a date range. Like i have an employee table , projectlist table and allocation table . Now based on allocation start and end dates, i must choose employee and project names. But, if the employee is working on more than one project, then i want it to choose between min(start_date) and max(end_date) so that only one record is shown.Right now one record per project is shown for each employee. so if he is in more than one project, tht record will repeat.so can someone help me to get single record for each employee by giving the date range BETWEEN min and max of those dates?Pls help me....its very urgent....Thanks in advance. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-11 : 02:38:12
|
| Could you post the table structure, some sample data and your desired output please?--Gail ShawSQL Server MVP |
 |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2008-07-11 : 02:56:26
|
| Hi,Thanks for ur reply.The screen now looks somwwht like this:EmployeeName ProjectName StartDate EndDate A ABC 01/25/2004 12/31/2004 A DEF 01/01/2005 10/01/2006 A XYZ 01/01/2007 12/31/2010That is the employee A is alloacted for 3 projects.So, he is not free from 01/25/2004 to 12/31/2010.so the output must be somewht like this:EmployeeName ProjectName StartDate EndDate A need not be 01/25/2004 12/31/2010 mentionedI want to view the employees who are not allocated into any project between the 2 dates which user enters.I have from_date and to_date parameters, they must not fall within this date range(as shown in 2nd o/p)Thanks again... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-11 : 03:48:50
|
| is there a chance of having gaps in the middle of two projects as well? |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2008-07-11 : 03:56:54
|
| yes...there may be a gap between two projects but still he will not be available for any other project till the max(end_date).even if there are gaps btn projects, only the min(start_date) and max(end_date) must be considered. |
|
|
|
|
|
|
SQL Aggregate functions