December 21, 2013 at 9:35 am
Hi, all
I can't understand why my code does't work, I want to leave only one entry with min Exec order, I thought that it should filter out with my max(ExecOrder) selection but nothing works,
without last line it brings 2 rows, with last zero. I need one row for give time period and min ExecOrder= 2.
declare @dd date = '2013-4-4'
;with PL as ( -- Policy List
select 1000 PolicyID, '2011-1-1' DateFrom, '2010-12-12' DateTo, 'Cust01' CustID union
select 2000 PolicyID, '2013-2-2' DateFrom, '2013-5-5' DateTo, 'Cust01' CustID union
select 3000 PolicyID, '2013-3-3' DateFrom, '2013-12-12' DateTo, 'Cust01' CustID ),
EO as ( -- Policy Exec Order
select 1000 PolicyID, 1 ExecOrder, 'Cust01' CustID union
select 2000 PolicyID, 2 ExecOrder, 'Cust01' CustID union
select 3000 PolicyID, 3 ExecOrder, 'Cust01' CustID )
select pl.PolicyID
,pl.CustID
,eo.ExecOrder -- ,pl.DateFrom, pl.DateTo
from PL
join EO ON EO.PolicyId = PL.PolicyID
where @dd between pl.DateFrom and pl.DateTo
-- and eo.ExecOrder = (select min(ExecOrder) from EO EO2 where eo2.custId = eo.custId)
--EO2.PolicyID = EO.PolicyID)
Thanks
mario
December 21, 2013 at 11:14 am
It doesn't work without the subquery because you have nothing to give the min
It doesn't work with the subquery because you take the min exec order over cust_id regardless of date, which is 1, which is out of the date range thanks to policy join.
You can fix this by making your subquery select both tables with a date range:
declare @dd date = '2013-4-4'
;with PL as ( -- Policy List
select 1000 PolicyID, '2011-1-1' DateFrom, '2010-12-12' DateTo, 'Cust01' CustID union
select 2000 PolicyID, '2013-2-2' DateFrom, '2013-5-5' DateTo, 'Cust01' CustID union
select 3000 PolicyID, '2013-3-3' DateFrom, '2013-12-12' DateTo, 'Cust01' CustID ),
EO as ( -- Policy Exec Order
select 1000 PolicyID, 1 ExecOrder, 'Cust01' CustID union
select 2000 PolicyID, 2 ExecOrder, 'Cust01' CustID union
select 3000 PolicyID, 3 ExecOrder, 'Cust01' CustID )
select eo.PolicyID
,eo.CustID
,eo.ExecOrder
from EO
where eo.ExecOrder = (select min(ExecOrder) from EO EO2
JOIN PL ON PL.PolicyID = EO2.PolicyID
WHERE eo2.custId = eo.custId
AND @dd BETWEEN PL.DateFrom AND PL.DateTo)
I took out PL from outer query as you did not need it for your uncommented select list, but it can go back in if you need it (just keep alias different from subquery, and only correlate on custid)
Or a slightly more elegant and potentially faster solution:
declare @dd date = '2013-4-4'
;with PL as ( -- Policy List
select 1000 PolicyID, '2011-1-1' DateFrom, '2010-12-12' DateTo, 'Cust01' CustID union
select 2000 PolicyID, '2013-2-2' DateFrom, '2013-5-5' DateTo, 'Cust01' CustID union
select 3000 PolicyID, '2013-3-3' DateFrom, '2013-12-12' DateTo, 'Cust01' CustID ),
EO as ( -- Policy Exec Order
select 1000 PolicyID, 1 ExecOrder, 'Cust01' CustID union
select 2000 PolicyID, 2 ExecOrder, 'Cust01' CustID union
select 3000 PolicyID, 3 ExecOrder, 'Cust01' CustID )
, cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY ExecOrder ASC) rn,
PL.PolicyId,
PL.CustID,
EO.ExecOrder
FROM PL
JOIN EO
ON PL.PolicyID = EO.PolicyID
WHERE @dd BETWEEN PL.DateFrom AND PL.DateTo)
SELECT PolicyID,CustID,ExecOrder
FROM cte
WHERE rn = 1
NOTE: no sql at this machine, so these are not tested.
December 21, 2013 at 1:09 pm
Thanks, SSC/A
You solutions works, I thought that mine will do the same like in your #1, as it's already JONed in prev step , but not without actual JOIN. I thought I should work without JOIN, and that's I did for simpler cases.
So those section are not the same, though case b binded OK
--a
..where eo.ExecOrder = (select min(ExecOrder) from EO EO2
JOIN PL ON PL.PolicyID = EO2.PolicyID
WHERE @dd BETWEEN PL.DateFrom AND PL.DateTo)
--b
..where eo.ExecOrder = (select min(ExecOrder) from EO EO2
WHERE PL.PolicyID = EO2.PolicyID
AND @dd BETWEEN PL.DateFrom AND PL.DateTo)
Tx
M
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply