cursor - query in procedure to get maxdate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cursor - query in procedure to get maxdate

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-02-29 : 12:50:25

i m new in sql...and i have this procedure..which have cursor inside..

1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table

but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...

create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON

DECLARE @MaxDate datetime
DECLARE @Date datetime

select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general

where name = @name AND dated = @MaxDate

DECLARE CUR1 CURSOR FOR

SELECT @DATE FROM #tempt

OPEN CUR1

FETCH NEXT FROM CUR1 INTO @Date

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1

--DROP TABLE #Date1

FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-29 : 13:11:45

This sounds like it can be done easily without using a cursor. Please provide a sample of your data and the expected results.

mkool
Starting Member

25 Posts

Posted - 2008-02-29 : 13:20:41

i have so many columns in general table..from which i want only
id,title,dated columns

just simple select id,title,dated from general..i m getting more than 8000 rows
i want max id for each maxdate.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-29 : 13:36:12

If Im understanding you correctly the following select statement should provide you with your desired results:

SELECT  g1.id,
        g1.title,
        g1.dated
FROM    general g1
        JOIN (SELECT    title,
                        max(dated) AS maxdated
              FROM      general
              GROUP BY  title) g2
        ON g1.title = g2.title
           AND g1.dated = g2.maxdated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources