SQL Problem

[Gman]

Need a little help with this sql.

DECLARE @BedNum INT, @Leave bit, @Sleep bit, @WardCode VarChar(10), @BayCode VarChar(10)
	SET @WardCode = 'W20' 
	SET @BayCode = 'W20_B1'
SELECT IB.* FROM ImsBed IB
			LEFT JOIN BedClosure IBC ON IBC.WardCode = IB.WardCode
				AND IBC.BayCode = IB.BayCode
				AND IBC.BedNumber = IB.BedNumber
			LEFT JOIN BayClosure IBYC ON IBYC.WardCode = IB.WardCode
				AND IBYC.BayCode = IB.BayCode
			LEFT JOIN WardClosure IWC ON IWC.WardCode = IB.WardCode
		
		WHERE IB.WardCode = @WardCode
			AND IB.BayCode = @BayCode
			AND IB.BedDeletedDate IS NULL
			AND GetDate() NOT BETWEEN ISNULL(IBC.ClosureDate,'01 Dec 9999') AND ISNULL(IBC.ReopenDate,'31 Dec 9999')
			AND GetDate() NOT BETWEEN ISNULL(IBYC.ClosureDate,'01 Dec 9999') AND  ISNULL(IBYC.ReopenDate,'31 Dec 9999')
			AND GetDate() NOT BETWEEN ISNULL(IWC.ClosureDate,'01 Dec 9999') AND  ISNULL(IWC.ReopenDate,'31 Dec 9999')

Now heres a little background info, at the moment theres a problem because in the bayClosure table there are 3 enteries
all 3 enteries have the same wardCode and BayCode but all have different Closure dates like so

SO....

WordCode BayCode ClosureDate ReOpenDate
--------- --------- ------------- ---------------
W1 B1 02/08/07 00:00:00 03/08/07 00:00:00
W1 B1 04/08/07 00:00:00 05/08/07 00:00:00
W1 B1 13/08/07 00:00:00

The entry without a ReOpenDate meens that the Bay is currently closed.

The problem I'm having with the above sql is that the join on this table is seeing the first 2 enteries which is create two
duplicate entries in the results. What I need the sql to do is see that the bay is currently closed and therefore not add an
entry to the results.

Any ideas ?

 

[Gman]

humm will give that a try, but I managed to sort it this working by replacing that which you mentioned with

LEFT JOIN (SELECT BayClosure.BayCode,
			                  BayClosure.WardCode,
			                  BayClosure.ReOpenDate,
			                  BayClosure.ClosureDate
			                  FROM BayClosure
			                  INNER JOIN (SELECT BayCode,
			                                     WardCode,
			                                     MAX(ClosureDate) AS ClosureDate
			                               FROM BayClosure
			                               GROUP BY BayCode,
			                                        WardCode) Tbl1
			                  ON BayClosure.BayCode  = Tbl1.BayCode  AND
			                     BayClosure.WardCode = Tbl1.WardCode AND
			                     BayClosure.ClosureDate = Tbl1.ClosureDate) IBYC
			ON  IBYC.WardCode = IB.WardCode
			AND IBYC.BayCode = IB.BayCode
			AND IBYC.ReOpenDate IS NULL