Rank & Miscellany

Rank things in the table…..very usefull for more complex stuff

SELECT
    --your select bits here
,(Select count(*)	FROM YOUR_TABLE_NAME as internal
WHERE internal.refrl_refno=external.refrl_refno
AND internal.start_dttm<external.start_dttm
--be sure to have the same criteria applied to the internal as to the external versions of the table
) as drank

Age groups…
Slot these into a CASE END Sandwich

When age between  0 and  4 then '00-04'
When age between  5 and  9 then '05-09'
When age between 10 and 14 then '10-14'
When age between 15 and 19 then '15-19'
When age between 20 and 24 then '20-24'
When age between 25 and 29 then '25-29'
When age between 30 and 34 then '30-34'
When age between 35 and 39 then '35-39'
When age between 40 and 44 then '40-44'
When age between 45 and 49 then '45-49'
When age between 50 and 54 then '50-54'
When age between 55 and 59 then '55-59'
When age between 60 and 64 then '60-64'
When age between 65 and 69 then '65-69'
When age between 70 and 74 then '70-74'
When age between 75 and 79 then '75-79'
When age between 80 and 84 then '80-84'
When age between 85 and 89 then '85-89'
When age between 90 and 94 then '90-94'
When age between 95 and 99 then '95-99'

Create a table variable…

DECLARE @sw  table
(
field_refno numeric
, var_field varchar(10)
)
INSERT INTO @sw VALUES(1,'Hudgle')

Using HAVING

SELECT
	count(prvsp_refno) as N
	, patnt_refno
FROM 	provider_spells
WHERE
	inmgt_refno=200870
	and admit_dttm > '2008-01-01'
GROUP BY patnt_refno
HAVING
	count(prvsp_refno)>10