Dense_Rank with Gaps and Islands Problem–Another Solution


 CREATE TABLE #CustomerOffer ( id INT, qid INT, Referralid INT, Offerid INT, OfferAmount INT)

INSERT INTO #CustomerOffer VALUES ( 1,421,513452,101,10000)
INSERT INTO #CustomerOffer VALUES ( 2,421,513452,102,10001)
INSERT INTO #CustomerOffer VALUES ( 3,421,513452,103,10002)
INSERT INTO #CustomerOffer VALUES ( 4,421,513452,104,10003)
INSERT INTO #CustomerOffer VALUES ( 5,421,513452,105,10004)

INSERT INTO #CustomerOffer VALUES ( 6,421,5290373,3234,10010)
INSERT INTO #CustomerOffer VALUES ( 7,421,5290373,3284,100011)
INSERT INTO #CustomerOffer VALUES ( 8,421,5290373,3923,100022)

INSERT INTO #CustomerOffer VALUES ( 9,422,513454,1111,100101)
INSERT INTO #CustomerOffer VALUES ( 10,422,513454,1112,1000111)
INSERT INTO #CustomerOffer VALUES ( 11,422,513454,1113,1000222)

select Id, qid,ReferralId, OfferId, OfferAmount, 
dense_rank() over(order by Cast(qid as binary(4))+ Cast(ReferralId as binary(4))) as ReferralRnk 
,ROW_NUMBER() over (partition by qid, referralId order by OfferId) as PgmNbr

From #CustomerOffer
order by Id

drop table #CustomerOffer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s