July 21, 2009 at 12:43 pm
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_MONTH
from 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) p
on 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
July 21, 2009 at 12:57 pm
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.
July 21, 2009 at 1:42 pm
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.
July 21, 2009 at 2:53 pm
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
July 21, 2009 at 3:20 pm
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
July 21, 2009 at 5:09 pm
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 speed
SET NOCOUNT ON
--===== If temp testing table exists, drop it
IF OBJECT_ID('TEMPDB..#MyTemp') IS NOT NULL
DROP TABLE #MyTemp
--===== Create the temp testing table
CREATE 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 answer
SELECT T1.[Name],
T1.[Date]
FROM #MyTemp T1
WHERE T1.[Date] IN
(
SELECT TOP 2 --Change this number to vary # output
T2.[Date]
FROM #MyTemp T2
WHERE T2.[Name] = T1.[Name]
ORDER BY T2.[Date] DESC
)
ORDER BY T1.[Name],
T1.[Date] DESC
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2009 at 11:14 am
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.
July 22, 2009 at 11:42 am
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!
July 22, 2009 at 10:45 pm
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
Change is inevitable... Change for the better is not.
July 24, 2009 at 12:36 pm
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