edit

i'm slowly getting there 🙂

this is part of what i have at the moment

declare acursor cursor for

select id from accidentriddorcategories

open acursor

fetch next from acursor into @catID

while (@@FETCH_STATUS <> -1)

begin

set

OK. i think i see where your going with this. so i could do it like this?

select * from

(SELECT c.categoryID

,count(c.CategoryID) as catcount

FROM...

hi. i'm afraid not 🙂

i should have put more test data in.

i wantto get a count of how many people fall into category 1. and how many people fall into...

ok. that seems the most succinct way of doing it. the select statement will only ever bring back zero or 1 record so it works perfect.

thanks for your help. its...

aah sorry 🙂 variables.

i'll post abit more of my code so that you can see what it does. this is still a stripped down version to keep it looking clean.

IF...

it does fall down if your birthday is this year but hasn't happened yet

select DATEDIFF(year,'1981-12-04',getdate())

will say 28 when they will still be 27

admittedly, its probably safe to say that the...

i'd do my dates as :

between '2009-06-01' and '2009-06-30'

or

'01 jun 2009' and '30 jun 2009'

not

'01/06/2009' and '30/06/2009'

or

'01/06/09' and '30/06/09'

select * from table1 where SomeDate between '2009-01-01 00:00:00' and '2009-08-28 23:59:59'

is something like this what your looking for?

of course. just cast the id. why didn't i think of that? 😀

i've just tried JTSs example.

it works to a degree. but for some reason you cant do the case in the order by clause with different datatypes?

i tried

select id,firstname,surname from footballers

order...

i'd have them in one table and self reference as a son could also be a father. presumably.

not to worry. i find out a work around.

for those of you who are interested its from this link

http://www.codeproject.com/KB/database/Pivot2Columns.aspx

hi all,

i'm back 😀

my initial sql query works fine now.

i've used the pivot function and its great. but now i need to add an extra bit which leads me to...

thanks for the replys.

i finally got GilaMonster PIVOT suggestion to work. i hadn't fully understood the example in BOL.

all works great now. thanks!