SQL Help with Joins 1

[agentwalker]

Firstly here the bit of sql i'm playing with

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 entries which creates 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, in effect ignoring the other two entries in the table as they are not the most recent, the one with just the closed date is the recent one.

Hope that makes sense
Any ideas ?

 

[bborissov]

Could you post some simple data?
How do you keep empty dates in that table?
Is this works:

......
LEFT JOIN (SELECT BayCode, WardCode, ReOpenedDate, ClosureDate
                  FROM BayClosure
                  INNER JOIN (SELECT BayCode,
                                     WardCode,
                                     MAX(ClosureDate) AS ClosureDate
                               FROM BayClosure
                               GROUP BY BayCode,
                                        WordCode) 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.ReOpenedDate IS NOT NULL -- If you keep empty dates as NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[agentwalker]

I think you've nearly got it mate, yep if theres no dates its Null.

just trying your code but i'm getting an error which reads

"The column 'WardCode' was specified multiple times for 'IBYC'."

 

[bborissov]

LEFT JOIN (SELECT BayClosure.BayCode,
                  BayClosure.WardCode,
                  BayClosure.ReOpenedDate,
                  BayClosure.ClosureDate
                  FROM BayClosure
                  INNER JOIN (SELECT BayCode,
                                     WardCode,
                                     MAX(ClosureDate) AS ClosureDate
                               FROM BayClosure
                               GROUP BY BayCode,
                                        WordCode) 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.ReOpenedDate IS NOT NULL -- If you keep empty dates as NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[agentwalker]

thanks, that cleared the errors up and seems to have sorted out the repeating entries caused by the two open bay entries however its still returning the bed info for that ward even though its closed, ummm

 

[bborissov]

OK, post some simple data from all tables and what you want from that data.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[agentwalker]

think I got it mate I just changed the
AND IBYC.ReOpenDate IS NOT NULL to AND IBYC.ReOpenDate IS NULL

with that it seems to be working fine on first glance, closed bays are no longer return any results and Open bays are returing bed entries fine.

Thanks again for the help mate saved me a hell of a lot of time and head scratching, will no doubt post again if after testing its playing up still.

cheers

 

[bborissov]

Glad you make it

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP