I'm trying to get the LastcallResult = 'Not Home' result that happen more than once but that happen in consecutive order. This cursor displays the records but doesn't grab the occurance one after the other , consecutive order.
If there's a way I can modify this cursor to get the records I want or If there's another process to get the records that have the "not home' filter more than once in consecutive order .
Thanks for any suggestions in advance....
DECLARE get_lastCallresult CURSOR FOR
select count(*), lastCallresult, fn, ln from contacts
where lastCallresult = 'Not Home'
and contactType = 'referral'
and system = 'lead'
--and callback ='Not Home'
group by lastCallresult,fn, ln
having COUNT(*) > 2
OPEN get_lastCallresult
FETCH NEXT FROM get_lastCallresult
WHILE @.@.FETCH_STATUS = 0
FETCH NEXT FROM get_lastCallresult
CLOSE get_lastCallresult
DEALLOCATE get_lastCallresult
Try adding identity() to your select
Code Snippet
select count(*), lastCallresult, fn, ln from contacts, IDENTITY(int, 1,1) as seq
where lastCallresult = 'Not Home'
and contactType = 'referral'
and system = 'lead'
--and callback ='Not Home'
group by lastCallresult,fn, ln
having COUNT(*) > 2
|||
For this kind of questions, it is better to post some DDL, including constraints and indexes, sample data and expected result.
AMB
|||Are you sure that you require a CURSOR based operation?
I suggest that you consider dropping the CURSOR and just create a #temp TABLE with the query results. Then you can cycle through the temp table if your needs require rowwise operations.
However, if you provided the code that you use on the cursor, we might be able to help you revise the code to do it all in one operation. SQL
Server is best when used for SET based operations -not 'recordset' type operations.
|||Try adding an ORDER BY clause to your statement. Cursor operations are ordered, but you have to specify the order by for ordering to be guaranteed.
Without more info, no idea if you actually need a cursor, but look into the ROW_NUMBER() function if this is SQL Server 2005. It will let you do a lot of cool things in regards to gettting the last row.
DaleJ, this isn't valid syntax, certainly not in the FROM clause, and not in the SELECT clause without and INTO:
select count(*), lastCallresult, fn, ln
from contacts, IDENTITY(int, 1,1) as seq
where lastCallresult = 'Not Home'
and contactType = 'referral'
and system = 'lead'
--and callback ='Not Home'
group by lastCallresult,fn, ln
having COUNT(*) > 2
|||Oops, yup, thanks Louis.
I didn't think that all the way through along with not paying attention to where I pasted...
|||The only reason the I'm trying a cursor is because I was trying to scan thru the rows and find all those rows that are repeated and happen one after the other
i.e. ( this is not the actual table, is something to demostrate)
Name Address Phone DateCalled CalledReason
John 123 Main St 123-1234 01/02/06 Survey
John 123 Main St 123-1234 02/25/06 Survey
The column the has the called reason is the one that I want, is repeated and happen one after the oher.
|||druiz,
The solution would be finding existing groups, of consecutive rows, with more than one element. If you are using SQL Server 2005, then you can try using the new ranking function ROW_NUMBER.
Code Snippet
create table dbo.t1 (
[Name] varchar(50),
[Address] varchar(50),
Phone char(8),
DateCalled datetime,
CalledReason varchar(25)
)
go
insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/01/06', 'Reason 1')
insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/02/06', 'Survey')
insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/25/06', 'Survey')
insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/26/06', 'Reason 2')
insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/27/06', 'Reason 3')
insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/28/06', 'Reason 3')
insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/02/06', 'Reason 1')
insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/03/06', 'Reason 2')
go
;with cte
as
(
select
[name], [address], phone, datecalled, calledreason,
row_number() over(partition by [name], [address], phone order by datecalled) -
row_number() over(partition by [name], [address], phone, calledreason order by datecalled) as grp
from
dbo.t1
)
select
[name],
[address],
phone,
calledreason,
min(datecalled) as min_datecalled,
max(datecalled) as max_datecalled
from
cte
group by
[name], [address], phone, calledreason, grp
having
count(*) > 1
go
drop table dbo.t1
go
AMB
|||This snippet has been really helpful. I tried this same way and it gave the results I'm looking for, I'm just going to make a couple mod's to fit my tables.
Thanks
No comments:
Post a Comment