A coding challenge from the blindman.

Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.

Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updated datetime)

insert into #blindman (pkey, fkey, updated)
select 1, 'A', '1/1/2006'
UNION
select 2, 'B', '1/1/2006'
UNION
select 3, 'A', '1/2/2006'
UNION
select 4, 'B', '1/2/2006'
UNION
select 5, 'A', '1/4/2006'
UNION
select 6, 'B', '1/2/2006'
UNION
select 7, 'A', '1/3/2006'
UNION
select 8, 'B', '1/3/2006'
UNION
select 9, 'A', '1/5/2006'
UNION
select 10, 'B', '1/5/2006'

drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.

Who's up for the challenge?Where's the challange?

SELECT *
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )|||Here's a quick and dirty solution that works. I'm not in your leaque, so I'm sure you can clean it up:
CREATE PROCEDURE [dbo].[procBlindman] AS

SELECT pkey, fkey, updated, IDENTITY(int, 1,1) AS Sequence
INTO #temp
FROM blindman Order BY fkey, updated

SELECT *,
(SELECT pkey FROM #temp AS Alias2 WHERE Sequence = (SELECT Min(Sequence) FROM #temp AS Alias
WHERE Alias.Sequence > #temp.Sequence
AND Alias.fkey = #temp.fkey)) AS NextPkey
FROM #temp
GO|||Does SQL2000 vs SQL 2005 matter? OR can you use bells and whistles from either?|||Brett, your solution does not give the correct output. It gives this:pkey fkey updated nextpkey
-- -- ---------------- ---
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULLThe nextpkey for fkey 'A' should be in the order 3, 7, 5, 9. Your solution has record 3 being followed by record 5, when record seven is the next to follow.

pbaldy, your solution may be the one I have to go with, either using a temp table or a hideously large subquery to assign ordinal value, but I'd rather avoid the first method because I want this as a view, and the second method I fear will be very slow.|||Dude,

I'm hurt and dismayed

All you need is an ORDER BY

SELECT a.pkey, a.fkey, a.updated, b.pkey
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )
ORDER BY a.fkey, a.updated

This Returns

pkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULL

(10 row(s) affected)

[code]|||Desired output:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9

Brett's outputpkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7

One of these things is not like the other.
One of these things is kinda-the-same.
One of these things is not like the other.
Can you tell me now before I finish my song?|||OK, so you want it by date...the comparison then just shift to look at that...

Let me fire up QA...but it's really not that big of a change|||Does this float your boat?SELECT a.pkey, a.fkey, a.updated, Coalesce(Min(b.pkey), Min(c.pkey)) AS nextpkey
FROM #blindman AS a
LEFT JOIN #blindman AS b
ON (b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
LEFT JOIN #blindman AS c
ON (c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))
GROUP BY a.fkey, a.updated, a.pkey
ORDER BY a.fkey, a.updated, a.pkey
-PatP|||A double outer-join with coalesce. Very creative. Definitely an "outside the box" solution. I will try applying it to my actual problem.

Thanks.|||As I suspected, there was a better solution (the plan scales much better for large result sets):SELECT a.pkey, a.fkey, a.updated, Coalesce(
(SELECT Min(b.pkey)
FROM #blindman AS b
WHERE b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
, (SELECT Min(c.pkey)
FROM #blindman AS c
WHERE c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))) AS nextpkey
FROM #blindman AS a
ORDER BY a.fkey, a.updated, a.pkey-PatP|||I missed the challenge(actually I tried ;) today,didnt got the solution).Anyway congratulation Pat.U deserve a :beer:|||OK, and the reason for this is?|||Its a homework assignment.

;)

Actually, the example given is a simplified version of a view I need to develop for a project.|||sounds like you need some table normalization|||Not a normalization issue. The problem is that the sequence of the surrogate key is not in sync with the order of the natural key. But since this is pulling from the source database, it's not a schema I could change anyway.|||And the coding challenge winner is....Pat!

Very impressive SQL, dude.

You should know that while both solutions produced correct results, your first solution generated an "internal sql server error" when added as a subquery. Apparently, too complex for the optimizer to handle.

The second version did run about twice as fast, and shoe-horned into my final code without error. And just so you know this WASN'T a homework assignment, here is the final code. See if you can find your logic in it!
select case ClaimReserve.ReserveType when 'med' then 'M' when 'comp' then 'I' end as LOSS_DETAIL_ID,
Convert(varchar(50), ClaimReserve.fkClaim) as CLAIM_ID,
replace(replace(replace(convert(char(16), ClaimReserve.DateLup, 120), '-', ''), ' ', ''), ':', '') + convert(varchar(20), ClaimReserve.pkClaimReserve) as ACTIVITY_SEQUENCE,
case when ClaimReserve.ReserveAmt = isnull(PTD1.MedicalPTD, 0) + isnull(PTD2.PharmacyPTD, 0) then 'CL'
when PriorClaimReserve.PriorpkClaimReserve is null then 'OC'
else 'RC'
end as ACTIVITY_TYPE_CODE,
ClaimReserve.DateLup as DATE_OF_TRANSACTION,
case when ClaimReserve.ReserveAmt = isnull(MedicalPTD, 0) + isnull(PharmacyPTD, 0) then 0 --CL
when PriorClaimReserve.PriorpkClaimReserve is null then ClaimReserve.ReserveAmt --OC
else ClaimReserve.ReserveAmt - coalesce(PriorClaimReserve.PriorReserveAmt, 0) --RC
end as RESERVE_CHANGE_AMOUNT,
null as TRANSACTION_TYPE_CODE,
null as PAYMENT_AMOUNT
from ClaimReserve
left outer join --PriorClaimReserve
(SELECT Coalesce(
(SELECT Min(b.pkClaimReserve)
FROM ClaimReserve AS b
WHERE b.fkClaim = a.fkClaim
AND b.DateLup = a.DateLup
AND a.pkClaimReserve < b.pkClaimReserve),
(SELECT Min(c.pkClaimReserve)
FROM ClaimReserve AS c
WHERE c.fkClaim = a.fkClaim
AND c.DateLup =
(SELECT Min(d.DateLup)
FROM ClaimReserve AS d
WHERE d.fkClaim = a.fkClaim
AND a.DateLup < d.DateLup))) AS pkClaimReserve,
a.pkClaimReserve as PriorpkClaimReserve,
a.ReserveAmt as PriorReserveAmt
FROM ClaimReserve AS a) PriorClaimReserve
on ClaimReserve.pkClaimReserve = PriorClaimReserve.pkClaimReserve
left outer join --PTD1
(Select CashFlow.fkClaim,
sum(Voucher.Amount) as MedicalPTD
from Voucher
inner join CashFlow on CashFlow.pkCashFlow = Voucher.fkCashFlow
Where Voucher.Status <> 'Reversed'
and Voucher.ParentType = 'BillHeader'
group by CashFlow.fkClaim) PTD1
on ClaimReserve.fkClaim = PTD1.fkClaim
left outer join --PTD2
(Select Cashflow.fkClaim,
isnull(sum(Voucher.Amount), 0) as PharmacyPTD
from Cashflow
inner join Voucher on Cashflow.pkCashFlow = Voucher.fkCashFlow
inner join ExpenseHeader
on Voucher.fkParent = ExpenseHeader.pkExpenseHeader
and Voucher.ParentType = 'ExpenseHeader'
Where ExpenseHeader.ExpenseType = 4
and Voucher.Status <> 'Reversed'
group by CashFlow.fkClaim) PTD2
on ClaimReserve.fkClaim = PTD2.fkClaim
where ClaimReserve.ReserveType not in ('exp', 'legal')