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 Client开发者_JAVA百科ID 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!
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
The question isn't clear, but as I interpret it, perhaps something like:
DECLARE @MasterExpiry smalldatetime, @ConsolExpiry smalldatetime
SELECT @MasterExpiry = ExpiryDate FROM #ProductSub WHERE ClientID = @MasterClient
SELECT @ConsolExpiry = ExpiryDate FROM #ProductSub WHERE ClientID = @ConsolClient
SELECT CASE
WHEN @MasterExpiry IS NULL AND @ConsolExpiry IS NULL THEN @MasterClient
WHEN @MasterExpiry IS NULL THEN @MasterClient
WHEN @ConsolExpiry IS NULL THEN @ConsolClient
WHEN @MasterExpiry >= @ConsolExpiry THEN @MasterClient
ELSE @ConsolClient END AS [Client]
If you need the row data, then select that into a variable and do a separate SELECT
?
DECLARE @FinalClient int
SELECT @FinalClient = CASE
WHEN @MasterExpiry IS NULL AND @ConsolExpiry IS NULL THEN @MasterClient
WHEN @MasterExpiry IS NULL THEN @MasterClient
WHEN @ConsolExpiry IS NULL THEN @ConsolClient
WHEN @MasterExpiry >= @ConsolExpiry THEN @MasterClient
ELSE @ConsolClient END
SELECT * FROM #ProductSub WHERE ClientID = @FinalClient
精彩评论