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 |
|
shilgerink
Starting Member
2 Posts |
 Posted - 2012-04-18 : 05:55:03
|
| I have a query the outputs the followingID StartDate EndData Number1 2012-01-01 NULL 42 2012-03-01 NULL 43 NULL 2012-02-25 24 1999-12-15 2012-04-01 24 2012-04-08 NULL 4Basically this is a selection of contracts, which have a startdate, an enddate and a number that represents whether this line is a contract that has started, or a contract that has ended.Now, what I want is to select all lines, but not the double lines. The lines with ID=4 represent two contracts. One ends april 1st, the second one starts april 8th. Now I don't need the first line of ID 4, since there is already a new contract available. I don't need to know when the old contract ended, I only need that info if there is not already a new contract. How can I select from this query only the relevant lines? I would need a MAX(Number) or a MAX(COALESCE(StartDate, Enddate)) or something? When I select the max(number) for example, I will get the line for ID=4 that I need, but when I also select the date fields, it will return two lines again, only both with de max(number), which is 4. How can I get my result??Sander HilgerinkDBA @ De Woonplaats, NL |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-04-18 : 06:06:23
|
| Try thisselect columns from tablewhere StartDate is not null and EndData is nullMadhivananFailing to plan is Planning to fail |
 |
|
|
shilgerink
Starting Member
2 Posts |
Posted - 2012-04-18 : 07:07:39
|
| @madhivanan: the problem with your solution is, that I will also lose the lines with ID=3. For that contract, there is not already a new contract with a enddate=null, so it won't show in the results anymore.Sander HilgerinkDBA @ De Woonplaats, NL |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-04-19 : 03:58:09
|
| So, if either of data is null, you want to select that row?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
How to select the max. line from subquery?