Feeds:
Posts
Comments

Posts Tagged ‘number’

The different options available to eliminate/handle decimals

declare @value as float = 123.9876
select ceiling(@value), floor(@value) -- eliminate decimals 
select round(@value,2)

Find triggers inside a database (Courtesy to Harry Lee)

select st.name, strig.name, st.name
from sys.triggers strig inner join sys.tables st
	on strig.parent_id = st.object_id inner join sys.schemas sch
	on st.schema_id = sch.schema_id

Format number as currency

SELECT CONVERT(varchar, CAST(987654321 AS money), 1) 

Find column names in a given table

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'dbo.Foo' --you can change text to other dataTypes
ORDER BY 1--,c.OBJECT_ID;

Note on – pivoting accuracy with numbers
————————————
I have seen from experience that when Pivoting its better to use float or decimal (if you results can contain fractional values) and not integers to get more accurate results. If integer is required then the numbers need to be converted after the Pivotting.

Check IF Table is empty
—————————

To chekc if a record or a certain record exists

if exists(select top 1 * from TableName) — use the where clause to check for a certain record

The if exists checks whether any rows were returned. If I use top 1 in the selected statement that supposedly sohuld be more optimized because it does not need to show the showl table.
The star is used so that it can just be copied and reused by just changing the table name.

Generate Scripts (Backup scripts)
———————————–
Right click on a database
> Task
> Generate Scripts

Select the type of objects
Click the Advances button to see what how you want the script to be produced.

This way you would have all or your selected object include tables and its data all scripted out in a file.

Read Full Post »


-- How to convert n number of columns to just two:

--Problem: There were n number of unknown columns with known data Types. And two definite columns lets call them col 1 and col 2 of which I knew the name

--I just needed one of the unknown column and one definite column 

	
		declare @FinalWithOnlyTotal table
		(
			serial int identity, -- if sorting is required 
			unpivotedcolumn varchar(20),
			Total float
		)	
		if (select count(1) from #ResultsGrouped2) = 1 -- When just one new type is selected. Also tells me the number of unknown columns returned  
		begin 
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end 
		else
		begin
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end
		
		-- The below produces data for the graph 
		select	unpivotedcolumn as [Label],
				Total as [Results] 
		from @FinalWithOnlyTotal
		where unpivotedcolumn  <> 'Total'




Read Full Post »

Rank using row_number


/*

select distinct  top 5  [inj_code], [desc], sum(value1) Val1 
from dbo.project_tbl
where EndDate between '01/01/2009' and '31/05/2012'
	and client = 'Cadbury'
	and inj_code <> 'A00' -- No Injuries 
group by [inj_code],[desc] 
order by 3 desc 


select distinct Field1 
from dbo.project_tbl

*/

--select * from #test 

/* My explation 

Think of partition as the number of groups you want to want the ranking for. 
So if you just have one column there it will count each and every row
If you have two columns it will count the ranks for two groups.

Let me explain:
Say you have a table with two fields.
In there you store names of fruits in one column and the colour in another:

Mango orange
Mango green
Mango yello

Melon orange
Melon greeen
Melon yellow

If you just use any one of the column to partintion it will give your the rank from 1 -6 (total number of rows = 6).
If you put both the names of the columnns then you have rank for both the groups from (1-3) 

*/

with myCTE as 
(
	select top 100 Field1 , Sub, [desc], sum(value1) Val1 
	--- if Field1 is not put in that row number will get the actual rownumber from the table 
	, row_number()over (
		partition by  Field1, Sub 
		order by sum(value1) desc) RowNumber
	from dbo.project_tbl
		where EndDate between '01/01/2009' and '31/05/2012' 
		 and client = 'Cadbury'
		 --and Field1 in ('Thompsons Solicitors', 'Inwin Mitchell')
	group by Field1 ,  Sub , [desc]
	order by 1,2 desc

)
select c.Field1
from myCTE c

Read Full Post »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

Read Full Post »

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! -- There are two SPs here
--First option:    Returns all month name and number
--==================================================
--create proc dbo.usp_getMonthNameAndNumber
--as
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)
DECLARE @count INT

SET @count = 0

WHILE (@count & lt;12)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1

--Second option:    Returns Month name and number upto the passed month number
--    (rows are restricted to the passed month)
--==================================================
--create proc dbo.usp_getMonth (@RestrictToMonthNumber int = null)
--as
---------====== Test Values ======
DECLARE @RestrictToMonthNumber INT

SET @RestrictToMonthNumber = 6

-------=============================
-- Note: If null is sent then all months are returned
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)

IF @RestrictToMonthNumber IS NULL
	SET @RestrictToMonthNumber = 12

DECLARE @count INT

SET @count = 0

WHILE (@count & lt;@RestrictToMonthNumber)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1 

Related post ( used IN SSRS ) :
http: / / wp.me / pAchH – 4 A

Read Full Post »

--create function dbo.udf_ExtractNumbersToFloat (@String varchar(max))
--returns float
--begin
------ ==== Test =======
DECLARE @String VARCHAR(1000)

SET @String = 'I can count 1 and 2 and 3 even 4.5';

------ ==== Test =======
WHILE PATINDEX('%[^0-9]%.%', @String) & gt;0
	SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

DECLARE @Float FLOAT

SET @Float = convert(FLOAT, @String)

PRINT @Float -- test
	--    return  @Float
	--end

Read Full Post »

CREATE FUNCTION dbo.udf_ExtractNumbers (@String VARCHAR(max))
RETURNS INT

BEGIN
 -------- ==== Test =======
 --DECLARE @String varchar(1000)
 --SET @String = 'I can count 1 and 2 and 3 but what is 4.5';
 -------- ==== Test =======
 WHILE PATINDEX('%[^0-9]%', @String) > 0
  SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

 DECLARE @Integer INT

 SET @Integer = convert(INT, @String)

 -- PRINT @Integer -- test
 RETURN @Integer
END
 -- select * from udf_ExtractNumbers ('I can count 1 and 2 and 3 but what is 4.5')

Result:

Read Full Post »

Older Posts »