Rank & Miscellany

Posted on by
Reply

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

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

SQL time-ish stuff

Posted on by
Reply

Extract the time:

--get the time as an hour
DATEPART(hh,getdate())
Extract the dayname:
--get the day as a word (short)
UPPER(left(datename(dw,getdate()),3))
--get something nicely formatted.
substring(convert(char,getdate() ,120),12,5)

Create a table full of sequential date_time

USE tempdb
DECLARE @t int
DECLARE @inc_date datetime 
SET @inc_date = '2001-01-01 23:59:59'  
--CHANGE THIS TIME TO GET DIFFERENT SNAPSHOT TIME first date and time of temp table 

--if the temp table exists drop and recreate with the date time supplied above	
IF  (SELECT '1' as exist_ WHERE exists (select '1' from sysobjects  WHERE  name='##Temp_dates')) = '1' 
	BEGIN DROP TABLE  ##Temp_dates END
--create the table
CREATE TABLE ##Temp_dates(t_date datetime NOT NULL PRIMARY KEY) 
	SET NOCOUNT ON  
WHILE @inc_date <= '2012-12-31' 
	BEGIN 
	INSERT ##Temp_dates (t_date) VALUES (@inc_date) 
	SET @inc_date = @inc_date + 1 --or decimal value for hours 0.5 etc..
	END

SQL Date Scriptlets….

Posted on by
Reply

Convert calendar month number to financial year month number i.e. April = 1 rather than 4
In the following DTTM = Your date field

(MONTH([DTTM])+8)%12+1

Obtain Financial year from a date.

CASE WHEN MONTH(DTTM) IN (1,2,3) THEN CONVERT(CHAR(4),YEAR(DTTM)-1)+'/'+CONVERT(CHAR(4),(YEAR(DTTM))) 
ELSE CONVERT(CHAR(4),YEAR(DTTM)) +'/'+CONVERT(CHAR(4),YEAR(DTTM)+1) END as fyear

Some Automatic Dates:

--first day of current month
DATEADD(MM, DATEDIFF(M, 0, GETDATE()), 0)  

--first day of previous months
DATEADD(MM, DATEDIFF(M, 0, GETDATE())-1 , 0)  

--last day of previous month
DATEADD(ms,-2,DATEADD(MM, DATEDIFF(M, 0, GETDATE()) , 0))  

--tomorrow evening
DATEADD(ms,-2,DATEADD(dd, DATEDIFF(d, 0, GETDATE())+2 , 0))

…or swap the getdate() to a field to get the appropriate conversion from a field.

Select the first day of the current financial year…

case when month(DTTM) between 4 and 12 
then DATEADD(MM, DATEDIFF(M, 0, DTTM)-(month(DTTM)-4) , 0) 
else   DATEADD(MM, DATEDIFF(M, 0, DTTM)-(month(DTTM)+8) , 0) end