Search code examples
sqlsql-serversql-server-7

Help with a complex self referency query accross multiple colums


I am having difficulties with a complicated (for me any way) query.

The table I'm querying has 3 colums, ClientID (int Not Null), ProductID (int Not Null) and ExpiryDate (smalldatetime nullable)

Given two client ID's Master and Consolidated I need to perform the following business logic to return a single data set:

Select the ClientID with the greater expiry date for a product where expiry dates for both clientIDs are not null

Select the ClientID with a null expiry date for a product where one expiry is null and the other not null

Select the MasterID for a product where both expiry dates are null or both expiry dates are the same.

I have tried the following, but get stuck...

Create Table #ProductSub (ClientID int NOT NULL, 
                          ProductID int NOT NULL, 
                          ExpiryDate smalldatetime)  

/* In real life there is a Clustered Primary Key On ClientID and ProductID
   Load Up Some Test Data */  

  Insert into #ProductSub  Values (1, 100, null)
  Insert into #ProductSub  Values (2, 100, null)
  Insert into #ProductSub  Values (1, 101, null)
  Insert into #ProductSub  Values (2, 102, null)
  Insert into #ProductSub  Values (1, 200, null)
  Insert into #ProductSub  Values (2, 200, '2009-01-01')
  Insert into #ProductSub  Values (1, 300, '2009-01-01')
  Insert into #ProductSub  Values (2, 300, null)
  Insert into #ProductSub  Values (1, 400, '2009-01-01')
  Insert into #ProductSub  Values (2, 400, '2008-01-01')
  Insert into #ProductSub  Values (1, 500, '2008-01-01')
  Insert into #ProductSub  Values (2, 500, '2009-01-01')
  Insert into #ProductSub  Values (1, 600, '2009-01-01')
  Insert into #ProductSub  Values (2, 600, '2009-01-01')  

 --Select * from #ProductSub  

  Declare @MasterClient int,
          @ConsolClient int

  Select @MasterClient = 1, @ConsolClient = 2  


Select * from #ProductSub t1
  /* Use Master Client ID When Expiry Date is Null) */
  Where (ClientID = @MasterClient and ExpiryDate is null)
  /* Use Consol ClientID if Expiry Date is null nut Expiry Date for Master Client ID is not */
  OR    (ClientID = @ConsolClient and ExpiryDate is null and ProductID not in (
            Select ProductID from #ProductSub t2
            Where (ClientID = @MasterClient and ExpiryDate is null))
        ) 
  OR   -- OH NO my head exploded
/*  OR EXISTS (Select 1
             from #ProductSub t3
            )*/

Drop Table #ProductSub   

/**********  Expected  Output  ************************
ClientID     ProductID     ExpiryDate
1            100           NULL
1            101           NULL
2            102           NULL
1            200           NULL
2            300           NULL
1            400           2009-01-01 00:00:00
2            500           2009-01-01 00:00:00
1            600           2009-01-01 00:00:00

Any and all help greatly appreciated

EDIT: Although it sounds like it, this is not homework but a real life problem I am hoping to find a real life solution to, I could do this myself, but all my solutions are leading down the path to temp tables. I should point out the production environment is SQLServer 7!


Solution

  • Here I've moved the conditions to a subquery. The subquery joins the rows for Consol and Master, so you can access columns from both rows. The condition is still a little complex because either row can be missing.

    select ps.*
    from @ProductSub ps
    inner join (
        select     
          CASE 
            WHEN c.ClientID is null THEN m.ClientID
            WHEN m.ClientID is null THEN c.ClientID
            WHEN m.ExpiryDate is not null and c.ExpiryDate is not null THEN
              CASE 
                WHEN c.ExpiryDate > m.ExpiryDate THEN c.ClientID
                ELSE m.ClientID
              END
            WHEN m.ExpiryDate is null THEN m.ClientID
            WHEN c.ExpiryDate is null THEN c.ClientID
            ELSE m.ClientID
          END as ClientId,
          COALESCE(m.ProductId, c.ProductId) as ProductId
        from       @ProductSub m
        full outer join  @ProductSub c
        on         m.ProductID = c.ProductID
        and        m.ClientID <> c.ClientID
        where      IsNull(m.clientid,@MasterClient) = @MasterClient
        and        IsNull(c.clientid,@ConsolClient) = @ConsolClient
    ) filter
    on filter.clientid = ps.clientid
    and filter.productid = ps.productid
    order by ps.ProductId