Set based logic subquery roadblock 1

[jordanking]

Hello,

I will attempt to make this clear, forgive me if it is not.

I need to compare records in several joined tables against themselves. I have a table (residential) that tracks services provided to customers. There are two associated tables (ResidentialService and AddressLkup) that track what type of service (begin or complete) and the legal street address of the service. Once a service is started, a boolean field in the residential table is marked as true, once the service is complete a completed service record is entered into the residneital table and the bln value of the first record should be set to false, but the staff here often forget. SO I need to find all the residential records that are still "Active/true" that have another service record after the start date with a service complete type of service.

I tried to use a derived table but can not get the criteria logic to work the way I want it.

here is what I have

SELECT     Residential.intResidential, Residential.intCustomerID, Residential.blnStatus, AddressLkup.intAddressID, AddressLkup.intServiceID, 
					  ResidentialService.blnRemoveStatus, Residential.dtServiceDate
FROM         AddressLkup INNER JOIN
					  ResidentialService ON AddressLkup.intServiceID = ResidentialService.intServiceID RIGHT OUTER JOIN
					  Residential ON AddressLkup.intResidentialID = Residential.intResidential
		INNER JOIN
		(
		SELECT  Residential.intResidential, Residential.intCustomerID, Residential.blnStatus, AddressLkup.intAddressID, AddressLkup.intServiceID, 
							  ResidentialService.blnRemoveStatus, Residential.dtServiceDate
		FROM    AddressLkup INNER JOIN
							  ResidentialService ON AddressLkup.intServiceID = ResidentialService.intServiceID RIGHT OUTER JOIN
							  Residential ON AddressLkup.intResidentialID = Residential.intResidential
		) AS DerTable
		ON Residential.intResidential = DerTable.intResidential
		WHERE Residential.intCustomerID = DerTable.intCustomerID
		AND AddressLkup.intAddressID = DerTable.intAddressID

Basically I want to get all the residential records that are active/true and that have another residential record with the same residential.intCustomerID and AddressLkup.AddressID but a later Residential.ServiceDate and a ResidentialService.blnRemoveStatus of true

.....
I'd rather be surfing

 

[r937]

SELECT one.* 
  FROM (
       SELECT Residential.intResidential
            , Residential.intCustomerID
            , Residential.blnStatus
            , AddressLkup.intAddressID
            , AddressLkup.intServiceID
            , ResidentialService.blnRemoveStatus
            , Residential.dtServiceDate
         FROM Residential
       INNER
         JOIN AddressLkup 
           ON AddressLkup.intResidentialID = Residential.intResidential
       INNER 
         JOIN ResidentialService 
           ON ResidentialService.intServiceID = AddressLkup.intServiceID
        WHERE Residential.blnStatus IS TRUE
       ) AS one
INNER 
  JOIN (
       SELECT Residential.intResidential
            , Residential.intCustomerID
            , Residential.blnStatus
            , AddressLkup.intAddressID
            , AddressLkup.intServiceID
            , ResidentialService.blnRemoveStatus
            , Residential.dtServiceDate
         FROM Residential
       INNER
         JOIN AddressLkup 
           ON AddressLkup.intResidentialID = Residential.intResidential
       INNER 
         JOIN ResidentialService 
           ON ResidentialService.intServiceID = AddressLkup.intServiceID
        WHERE Residential.blnStatus IS TRUE
       ) AS two
    ON two.intResidential = one.intResidential
   AND two.intCustomerID  = one.intCustomerID
   AND two.intAddressID   = one.intAddressID
   AND two.dtServiceDate  > one.dtServiceDate

you might have to adjust

WHERE Residential.blnStatus IS TRUE

in both places, to test for 0 or 1 or however the true status is recorded

r937.com | rudy.ca

 

[jordanking]

thanks for the reply r937,

But it still does not work. The problem ,I think, is that the residential table is joined on intResidentialID which is a unique primary key, so the criteria logic at the bottom of the script is only evaluating itself against the same record. I need to evaluate that logic against other records in the residential table. Is there a way to run this comparison wihtout joining on the primary key?

.....
I'd rather be surfing

 

[r937]

sure, just remove that part of the join

   ON two.intCustomerID  = one.intCustomerID
  AND two.intAddressID   = one.intAddressID
  AND two.dtServiceDate  > one.dtServiceDate

r937.com | rudy.ca

 

[jordanking]

thanks, it works now

.....
I'd rather be surfing