SELECT
loc.loc_pac as registro, datediff(dd, pac.pac_nasc, getdate()) as 'dias'
from loc with (nolock), pac with (nolock), str with (nolock)
where loc.loc_pac <> ''
and (loc.loc_pac = pac.pac_reg)
and (loc.loc_str = str.str_cod)
order by dias
Generating the following result:
How do I group by bands, type:
track | qtd
0 - 20 | 8
20 - 30 | 2
30 - 40 | 2
...
I recommend making a table of tracks or a view, where you set the intervals you want. Then just make a SELECT with COUNT grouping by the strip.
Example:
create table faixas
(
faixa varchar(10),
inicio integer,
fim integer
);
insert into faixas values ('0-10',0,10);
insert into faixas values ('11-20',11,20);
insert into faixas values ('21-30',21,30);
insert into faixas values ('31-40',31,40);
insert into faixas values ('41-50',41,50);
insert into faixas values ('51-60',51,60);
insert into faixas values ('61-70',61,70);
insert into faixas values ('71-80',71,80);
insert into faixas values ('81-90',81,90);
insert into faixas values ('91-200',91,200);
A query:
select
f.faixa,
count(r.codigo) as qtd
from faixas f
left outer join registros r on r.dias >= f.inicio and r.dias <= f.fim
group by f.faixa;
Result:
faixa qtd
0-10 5
11-20 5
21-30 5
31-40 0
41-50 2
51-60 1
61-70 0
71-80 0
81-90 1
91-200 5
I put it in SQLFiddle
Update
Now that you have been informed that you are using SQL Server 2008, and can not create a table for this, you can use expression With :
with faixas as (
select '0-10' as faixa, 0 as inicio ,10 as fim
union
select '11-20',11,20
union
select '21-30',21,30
union
select '31-40',31,40
union
select '41-50',41,50
union
select '51-60',51,60
union
select '61-70',61,70
union
select '71-80',71,80
union
select '81-90',81,90
union
select '91-200',91,200
)
select
f.faixa,
count(r.codigo) as qtd
from faixas f
left outer join registros r on r.dias >= f.inicio and r.dias <= f.fim
group by f.faixa;
I put it in SQLFiddle