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!