Using Count() function? [STUMPED]

[yeewaitu]

Hi,

Got a SQL question for you experts out here. I would like to generate a count field based on the table below. Basically the count would reflect the number of records with the dates lesser than it and of the same ID.

Source Table
============

ID DATE
=== =======
123 01/01/05
123 01/01/04
123 01/01/03
124 01/01/05


Result Table
============

ID DATE Count
=== ======= ======
123 01/01/05 2
123 01/01/04 1
123 01/01/03 0
124 01/01/05 0

Any ideas anyone???? I am stumped.

 

[vongrunt]

This is row numbering partitioned by ID - trivial in SQL2005, requires temp table or self-join in SQL2000. Choose one

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[yeewaitu]

can you show me how to do this in SQL 2000?

 

[vongrunt]

Self-join then:

select A.ID, A.Date, count(B.Date)
from SourceTable A
left outer join SourceTable B on A.ID=B.ID and A.Date > B.Date
group by A.ID, A.Date
order by A.ID, A.Date desc

Note this will work well only if there are no duplicate dates per ID. Otherwise temp table must be used.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[yeewaitu]

But what if there are duplicate dates per ID? How can you use temp table then?

 

[vongrunt]

Based on sample data I assumed there are no duplicate dates per ID.

Do you have 'em or not?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[yeewaitu]

Yes. I wld have duplicate IDs... sadly. =(

Source Table
============

ID DATE
=== =======
123 01/01/05
123 01/01/05
123 01/01/04
123 01/01/03
124 01/01/05


Result Table
============

ID DATE Count
=== ======= ======
123 01/01/05 2
123 01/01/05 2
123 01/01/04 1
123 01/01/03 0
124 01/01/05 0

 

[vongrunt]

OK then... what is primary key in SourceTable?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[yeewaitu]

PID as shown below...

Source Table
============

PID ID DATE
=== === =======
1 123 01/01/05
2 123 01/01/05
3 123 01/01/04
4 123 01/01/03
5 124 01/01/05

Result Table
============

PID ID DATE Count
=== === ======= ======
1 123 01/01/05 2
2 123 01/01/05 2
3 123 01/01/04 1
4 123 01/01/03 0
5 124 01/01/05 0

 

[vongrunt]

Try this:

select A.ID, A.Date, A.PID, count(distinct B.Date)-1
from SourceTable A
left outer join SourceTable B on A.ID=B.ID and A.Date >= B.Date
group by A.ID, A.Date, A.PID
order by A.ID, A.Date desc

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[yeewaitu]

thanks for your help. =)