SQL Problem

Gman

Gman

Gman

Soldato
Joined
18 Oct 2002
Posts
4,925
Location
Yorkshire
Need a little help with this sql.

Code: 
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 ?
 
I presume you only want to consider entries in the bayClosure table that currently have closed bays?

Can you just do an inline view in the query to restrict things at this point? (Hopefully MS SQL supports that?)

So you would replace this line:
Code: 
LEFT JOIN BayClosure IBYC ON IBYC.WardCode = IB.WardCode
with:

Code: 
LEFT JOIN (SELECT * FROM BayClosure bc
           WHERE GetDate() BETWEEN bc.ClosureDate
                           AND ISNULL(bc.ReopenDate,'31 Dec 9999')
           ) IBYC ON IBYC.WardCode = IB.WardCode
 
humm will give that a try, but I managed to sort it this working by replacing that which you mentioned with

Code: 
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
 
You must log in or register to reply here.
Back
Top Bottom