Help - SQL Server Forums

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

Help

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-22 : 17:48:24

How can I check for station_nbr > 1 in this cursor? I need to find all station_nbr that have more than 1 record for the same station_nbr. Inside this if statement.

--------check for Group Code with N or W-------
	If (@begdate <> '1900-01-01')  
        Select @Records = Count(*) 
        From TIES_Temp.dbo.station_group a,TIES_Temp.dbo.station_group b
        Where (a.group_code like '%W')
        and (a.station_nbr = @stationnumber)
        and (b.group_code like '%N')
	and (a.station_nbr=b.station_nbr)
	and ( @current_date between  @begdate and @endeffdate)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-22 : 17:53:08

quote:

Originally posted by osirisa

How can I check for station_nbr > 1 in this cursor? I need to find all station_nbr that have more than 1 record for the same station_nbr. Inside this if statement.

--------check for Group Code with N or W-------
	If (@begdate <> '1900-01-01')  
        Select @Records = Count(*) 
        From TIES_Temp.dbo.station_group a,TIES_Temp.dbo.station_group b
        Where (a.group_code like '%W')
        and (a.station_nbr = @stationnumber)
        and (b.group_code like '%N')
	and (a.station_nbr=b.station_nbr)
	and ( @current_date between  @begdate and @endeffdate)

Just quoting this now so that we have a record of it in case you delete it like you did your other posts.

You mention a cursor, but I don't see one. Could you elaborate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-22 : 17:56:06

quote:
Originally posted by tkizer
.......
Just quoting this now so that we have a record of it in case you delete it like you did your other posts.

You mention a cursor, but I don't see one. Could you elaborate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

You dont leave a chance do you...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-22 : 18:12:10

[code]OPEN OverlappingDate_cursor

FETCH NEXT FROM OverlappingDate_cursor INTO @groupcode, @stationnumber, @begdate, @endeffdate, @groupcode, @stationnumber
WHILE @@FETCH_STATUS = 0
BEGIN

-- Begin cursor and set the @record_found_flag
SELECT @record_found_flag = 'N'

Set @current_date = ()****This part is correct******

print '1. Station No.='+ @stationnumber
--------check for Group Code with N or W-------
If (@begdate <> '1900-01-01')
Select @Records = Count(*)
From TIES_Temp.dbo.station_group a,TIES_Temp.dbo.station_group b
Where (a.group_code like '%W')
and (a.station_nbr = @stationnumber)
and (b.group_code like '%N')
---and (a.Beg_eff_date between @begdate and @endeffdate) or (b.End_eff_date between @begdate and @endeffdate)
and (a.station_nbr=b.station_nbr)
and ( @current_date between @begdate and @endeffdate)

------Print Records for the Report------------

If (@Records > 0)
Begin
print '3'
INSERT INTO #TP_TIES_OVerlapping_Dates(group_code,station_nbr, beg_eff_date, end_eff_date,current_month)
values (@groupcode,@stationnumber,@begdate, @endeffdate,@current_date )
End

FETCH NEXT FROM OverlappingDate_cursor INTO @groupcode, @stationnumber, @begdate, @endeffdate, @groupcode, @stationnumber [/code]

All I need is to be able to only pick up those records that have the same station_nbr twice. Ex. station_nbr >1 and put into the TEMP. Table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-22 : 18:33:36

I'm very confused by your code as you've posted partial code so far, but I think you can resolve this by using HAVING COUNT(*) >= 2.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-22 : 19:15:19

You might want to do something like this:


Select b.station_nbr, count(*)
From TIES_Temp.dbo.station_group a
JOIN TIES_Temp.dbo.station_group b ON a.station_nbr=b.station_nbr
WHERE (a.group_code like '%W')
	AND (a.station_nbr = @stationnumber)
	AND (b.group_code like '%N')
	---and (a.Beg_eff_date between @begdate and @endeffdate) or (b.End_eff_date between @begdate and @endeffdate)
	AND ( @current_date between  @begdate and @endeffdate)
GROUP BY b.station_nbr
HAVING COUNT(*) > 1

Notice I changed your way of joining the tables to ANSI standard way.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-22 : 21:50:03

Thank You all for the help, I will try the Having Clause at the end. Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 01:47:59

I don't think you need a cursor at all.
Did you try my suggestion posted here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91025#341879

E 12°55'05.25"
N 56°04'39.16"

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-23 : 09:35:53

OPEN OverlappingDate_cursor

FETCH NEXT FROM OverlappingDate_cursor INTO @groupcode, @stationnumber, @begdate, @endeffdate, @groupcode, @stationnumber
WHILE @@FETCH_STATUS = 0
BEGIN

-- Begin cursor and set the @record_found_flag
SELECT @record_found_flag = 'N'

Set @current_date = (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0))

print '1. Station No.='+ @stationnumber
--------check for Group Code with N or W-------
If (@begdate <> '1900-01-01')
Select @Records = Count(*), b.station_nbr, count(*)
From TIES_Temp.dbo.station_group a
JOIN TIES_Temp.dbo.station_group b ON a.station_nbr=b.station_nbr
Where (a.group_code like '%W')
and (a.station_nbr = @stationnumber)
and (b.group_code like '%N')
---and (a.Beg_eff_date between @begdate and @endeffdate) or (b.End_eff_date between @begdate and @endeffdate)
and (a.station_nbr=b.station_nbr)
and ( @current_date between @begdate and @endeffdate)
GROUP BY b.station_nbr
HAVING COUNT(*) > 1

I AM RECEIVING THE FOLLOWING ERROR:

"A select statement that assigns a value to a variable must not be combined with data-retrieval operations.

I need the cursor because, I have to read line by line finding only the records that meets the IF statement inside the cursor.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 09:43:45

Well, since you not longer answer follow-up questions, I will let you go...
Never ever have you explained the purpose of the cursor and if the sugestions made are suitable.

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-23 : 11:24:22

Nor have you actually poseted the cursor

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-23 : 12:01:26

quote:
Originally posted by osirisa

I AM RECEIVING THE FOLLOWING ERROR:

"A select statement that assigns a value to a variable must not be combined with data-retrieval operations.

I need the cursor because, I have to read line by line finding only the records that meets the IF statement inside the cursor.

Adding the HAVING clause did not cause the error. You are attempting to do too much in one query. You can assign variables and also return data in the same query. They must be split up.

Here's where the issue is: Select @Records = Count(*), b.station_nbr, count(*)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-23 : 13:23:21

quote:

Adding the HAVING clause did not cause the error. You are attempting to do too much in one query. You can assign variables and also return data in the same query. They must be split up.

Here's where the issue is: Select @Records = Count(*), b.station_nbr, count(*)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

How can I split up? Where inside the if statement can I put the second count?

Thanks Tara!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-23 : 13:30:45

You'll need to do you variable assignments in one statements and your returning of data in another statement.

select @...

select b.station_nbr, count(*)...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources