Should I use LOOP?

Question

Post reply

Should I use LOOP?

tgonzalez

SSC Enthusiast

Points: 182
More actions
June 2, 2008 at 12:44 pm

#380895

If anyone can help me with this I'll be extremely grateful...
I need to create a count of each appointment for each member. Eh member can have one or more appointments on the same day.This is what I have right now
NAME APPT DATE APPT NBR
JOHN CAMP, 5/29/2008, 67556
JOHN CAMP, 5/29/2008, 67557
ELMER SMITH, 5/29/2008, 77655
MARY FOX, 5/29/2008, 88855
I need it to look like this:
COUNT NAME APPT DATE APPT NBR
1, JOHN CAMP, 5/29/2008, 67556
2, JOHN CAMP, 5/29/2008, 67557
1, ELMER SMITH, 5/29/2008, 77655
1, MARY FOX, 5/29/2008, 88855
Can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Since you're using SQL Server 2005 - use the ROW_NUMBER() function.

With some assumptions as to your column and table names, it would look like:

select Row_Number() over (partition by memberID order by apptNBR) ApptDaySeq,

name,

apptDate,

apptNBR

from tblAppointment

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

tgonzalez SSC Enthusiast Points: 182 More actions · Answer 2

Thanks!!! That worked like a charm. I wish everything I'm trying to learn in SQL was that easy!

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

As long as you keey on posting on this forum, these pros will sure go a long way towards making it easier than before...

suresh_vc_mfc-784576 SSC Rookie Points: 44 More actions · Answer 4

How can we achieve the same in SQL 2000 where there is no Row_Number()

Thanks

Suresh

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 5

Suresh,

here is one way of doing it in SQL2000

create table appointment (

[name] varchar(50),

appt_date datetime,

appt_nbr int

)

insert into appointment ([name], appt_date, appt_nbr)

select 'JOHN CAMP','5/29/2008',67556

insert into appointment ([name], appt_date, appt_nbr)

select 'JOHN CAMP','5/29/2008',67557

insert into appointment ([name], appt_date, appt_nbr)

select 'ELMER SMITH','5/29/2008',77655

insert into appointment ([name], appt_date, appt_nbr)

select 'MARY FOX','5/29/2008',88855

select

count(*), a1.name, a1.appt_date, a1.appt_nbr

from appointment a1

join appointment a2 on a1.name = a2.name

where a1.appt_nbr >= a2.appt_nbr

group by

a1.name, a1.appt_date, a1.appt_nbr

drop table appointment

essentially it joins the table to itself and counts the number of appointments that are less than or equal to the current appointment number.

However this is a form of RBAR!!!! Jeff Moden will probably tear his hair out! 😀

Performance may well suffer if the appointment table is large........

Kev