December 9, 2016 at 8:56 am
Hi All,
I am working on a query where i had to find the output based on the conditional date logic. Below is the description of the problem.
What i want is that order should be greater then or equal to effective date and if there are 2 effective date greater then or equal to order date then pick the minimum effective date record.
Please guide me what would be best approach to handle this scenario .
--- Test data Script-------
IF OBJECT_ID('tempdb..#Customer', 'U') IS NOT NULL
DROP TABLE #Customer;
CREATE TABLE #Customer (
CustomerKey int,
OrderDate Date
);
INSERT #Customer (CustomerKey,OrderDate) VALUES
(1,'11/10/2016'),
(2,'11/26/2016'),
(3,'11/15/2016'),
(4,'11/20/2016')
IF OBJECT_ID('tempdb..#CustomerRef', 'U') IS NOT NULL
DROP TABLE #CustomerRef;
CREATE TABLE #CustomerRef (
CustomerKey INT,
CustomerKeyRef INT,
EffectiveDate DATE
);
INSERT #CustomerRef (CustomerKey,CustomerKeyRef,EffectiveDate) VALUES
(1,101,'11/1/2016'),
(1,101,'11/15/2016'),
(2,201,'11/26/2016'),
(2,201,'11/28/2016'),
(3,301,'11/15/2016'),
(4,401,'11/15/2016')
---- Output Script -----------------
SELECT 1 as CustomerKey,101 as CustomerKeyRef , '11/1/2016' as EffectiveDate
union
SELECT 2 as CustomerKey,201 as CustomerKeyRef , '11/26/2016' as EffectiveDate
union
SELECT 3 as CustomerKey,301 as CustomerKeyRef , '11/15/2016' as EffectiveDate
Any help will be greatly appreciated.
thanks
December 9, 2016 at 9:14 am
Try to keep your code, when posting, in IFCode tags, it makes it much more readable for other users.
I've also changed your date formatting, as for me '11/20/2016' would mean the 11th day of the 20th month This resulted in me getting [font="Courier New"]Conversion failed when converting date and/or time from character string.[/font]. Try to ensure that when using dates, they can be derived in what ever your language setting is (for example yyyy-mm-dd), otherwise it could cause you a lot of headaches.
Otherwise, thank you for providing some quite concise details. DDL, sample data and expected output! 😀
IF OBJECT_ID('tempdb..#Customer', 'U') IS NOT NULL
DROP TABLE #Customer;
CREATE TABLE #Customer (
CustomerKey int,
OrderDate Date
);
INSERT #Customer (CustomerKey,OrderDate) VALUES
(1,'10-Nov-2016'),
(2,'26-Nov-2016'),
(3,'15-Nov-2016'),
(4,'20-Nov-2016')
IF OBJECT_ID('tempdb..#CustomerRef', 'U') IS NOT NULL
DROP TABLE #CustomerRef;
CREATE TABLE #CustomerRef (
CustomerKey INT,
CustomerKeyRef INT,
EffectiveDate DATE
);
INSERT #CustomerRef (CustomerKey,CustomerKeyRef,EffectiveDate) VALUES
(1,101,'1-Nov-2016'),
(1,101,'15-Nov-2016'),
(2,201,'26-Nov-2016'),
(2,201,'28-Nov-2016'),
(3,301,'15-Nov-2016'),
(4,401,'15-Nov-2016')
---- Output Script -----------------
SELECT 1 as CustomerKey,101 as CustomerKeyRef , CAST('1-Nov-2016' AS DATE) as EffectiveDate
union
SELECT 2 as CustomerKey,201 as CustomerKeyRef , CAST('26-Nov-2016' AS DATE) as EffectiveDate
union
SELECT 3 as CustomerKey,301 as CustomerKeyRef , CAST('15-Nov-2016' AS DATE) as EffectiveDate
I believe this, however, is the answer you are looking for:
SELECT C.CustomerKey,
CR.CustomerKeyRef,
CR.EffectiveDate
FROM #Customer C
CROSS APPLY(SELECT TOP 1
ca.CustomerKeyRef,
ca.EffectiveDate
FROM #CustomerRef ca
WHERE ca.CustomerKey = C.CustomerKey
AND ca.EffectiveDate <= C.OrderDate
ORDER BY ca.EffectiveDate ASC) CR
Edit: Had my Less Than as a Greater Than.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 9:25 am
First, I added some additional rows to cover your stated logic:
INSERT #CustomerRef (CustomerKey,CustomerKeyRef,EffectiveDate) VALUES
(1,101,'11/1/2016'),
(1,101,'11/15/2016'),
(2,201,'11/26/2016'),
(2,201,'11/28/2016'),
(3,301,'11/15/2016'),
(4,401,'11/15/2016'),
--important to prove results
(1,101,'11/2/2016'),
(1,101,'11/5/2016'),
(1,101,'11/20/2016')
Now there are 3 rows > orderdate and 2 < order date for 101. You should also add some more test data to cover equal date scenarios too probably.
--you can pick columns
select c.*, cr.*
from #customer c inner join #customerref cr on c.customerkey = cr.customerkey and c.Orderdate >= cr.effectivedate
And cr.effectivedate = (SELECT MIN(cr2.effectivedate) from #customerRef cr2 WHERE cr2.customerkey = cr.customerkey and cr2.customerkeyref = cr.customerkeyref AND cr2.effectiveDate <= c.OrderDate)
Note the other code given seems to provide the wrong date for 101.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 9, 2016 at 9:38 am
Thanks for the help i noticed that part 🙂
December 9, 2016 at 10:27 am
Just in case anyone else happens upon this and is temporarily confused as I was, the accepted solutions do include a row for CustomerKey=4 in their results, which is in line with the stated requirements, but not with the provided desired output.
I imagine that row was just accidentally omitted from the desired results.
Cheers!
December 9, 2016 at 11:05 am
Jacob Wilkins (12/9/2016)
Just in case anyone else happens upon this and is temporarily confused as I was, the accepted solutions do include a row for CustomerKey=4 in their results, which is in line with the stated requirements, but not with the provided desired output.I imagine that row was just accidentally omitted from the desired results.
Cheers!
I did in fact make that assumption.
To the OP: you may also need to redo the query if you always want ALL Customers from the main table even if they don't have any matching Refs. A LEFT JOIN would be the key in that case. I made my logic fit that use case by including all of the logic on the INNER JOIN as opposed to making a WHERE clause (which could get you the wrong answer when you shifted to a LEFT JOIN).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply