Need some Cursor help...well I think it's a cursor I need
-
In a time crunch! It's been forever since I used a Cursor
I need to populate a table with the top 10 records for each DOCID from the following query...
select
x.DOCID,o.OPTY_ID, o.OPPTY_NAME, o.SUM_WIN_PROB, o.COMMIT_FLAG,
isnull(p.TOT_MRV, 0) TOT_MRV, isnull(p.TOT_NRV, 0) TOT_NRV,
cast(year(o.EXPECT_CLS_DT) as char(4)) + '/' +
case when month(o.EXPECT_CLS_DT) < 10 then '0'
else '' end + cast(month(o.EXPECT_CLS_DT) as char(2)) OPTY_CLS_MONTHfrom dbo.STGFOpportunity o
join dbo.STAccountProfile x
on o.NASP_ID=x.NASPID
and o.Region=x.Region
left outer join
(select OPTY_ID,
sum(case
when X_QUOTA_CAT is null then MRV
when rtrim(X_QUOTA_CAT) = '' then MRV
when X_QUOTA_CAT = 'MRV' then MRV
else 0 end) TOT_MRV,
sum(case
when X_QUOTA_CAT is null then NRR
when rtrim(X_QUOTA_CAT) = '' then NRR
when X_QUOTA_CAT = 'NRV' then NRR
else 0 end) TOT_NRV
from dbo.STGFOpportunityProduct
group by OPTY_ID) pon o.OPTY_ID = p.OPTY_ID
where o.CONSOLIDATED_SALES_STG_NAME not in ('Lost','Disqualified','Won')
and len(o.NASP_ID) = 6
and datediff(dd, getdate(),o.EXPECT_CLS_DT) > -60
order by 1,p.TOT_MRV + (p.TOT_NRV/12) desc
-
What is the structure of the table that you need to populate? What do you need in the table, just the DOCID and a Sequence (1-10) number?
By the way, you'll not need a cursor for this.
-
Not sure, but I think a paren may be missing. Could you also format your code so that it is easier to read. White space is your friend.
-
I need all the data, for instance a docid may have 74 records but I need only the top 10
Hope this is easier...
For each DOCID...
select top 10
x.DocID,o.OPTY_ID, o.OPPTY_NAME, o.SUM_WIN_PROB, o.COMMIT_FLAG,
isnull(p.TOT_MRV, 0) TOT_MRV, isnull(p.TOT_NRV, 0) TOT_NRV,
cast(year(o.EXPECT_CLS_DT) as char(4)) + '/' +
case when month(o.EXPECT_CLS_DT) -60
order by p.TOT_MRV + (p.TOT_NRV/12) desc
-
Ah, my bad. I didn't see that you wanted just the top 10 for each one. I was thinking you wanted to generate 10 rows for each row.
Anyhow, you still don't need a cursor for this. Does your query produce the correct recordset that you want to insert?
If so, then it's pretty simple:
INSERT INTO DestinationTable(ColumnList)SELECT TOP 10....your query -
Heh... ROW_NUMBER from SQL Server 2005+ would certainly make this easy. But, this is 2k so we have to go back to brute force every once in a while.
I don't have your data to test with so I'll give you a working example to study from and then you can try it on your own with your data...
/**************************************************************Select the top x from each group**************************************************************/--===== Suppress auto-display of line counts for speedSET NOCOUNT ON--===== If temp testing table exists, drop itIF OBJECT_ID('TEMPDB..#MyTemp') IS NOT NULLDROP TABLE #MyTemp--===== Create the temp testing tableCREATE TABLE #MyTemp([Name] VARCHAR(10),[Date] DateTime DEFAULT GETDATE())INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040302')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040310')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040312')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Arley','20040301')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Frank','20040310')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040317')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040317')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040316')INSERT INTO #MyTemp ([Name], [Date]) VALUES('Ben','20040315')--===== Demo the answerSELECT T1.[Name],T1.[Date]FROM #MyTemp T1WHERE T1.[Date] IN(SELECT TOP 2 --Change this number to vary # outputT2.[Date]FROM #MyTemp T2WHERE T2.[Name] = T1.[Name]ORDER BY T2.[Date] DESC)ORDER BY T1.[Name],T1.[Date] DESC--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This isn't going to work because it's going to generate the Top 10 for all records, I need the Top 10 for each ID. I think Jeff's solution may work, going to try that now.
-
Yep, I didn't catch that part. Jeff's solution will give you the top 10 for each ID.
As a side note, any time my answers differ from Jeff's -----go with Jeffs!
-
John Rowan (7/22/2009)
Yep, I didn't catch that part. Jeff's solution will give you the top 10 for each ID.As a side note, any time my answers differ from Jeff's -----go with Jeffs!
Heh... I appreciate the confidence but I'm still learning stuff, too! I make mistrakes
just like everyone else does. That's why I always post code... so people can double check me. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/21/2009)
Heh... ROW_NUMBER from SQL Server 2005+ would certainly make this easy. But, this is 2k so we have to go back to brute force every once in a while.OK, I am new to SS2K5, so... how would you use ROW_NUMBER() ?
(Now, this being an SS2K forum, maybe this is not the place for SS2K5 code ...)
Regards
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply