Feeds:
Posts
Comments

Posts Tagged ‘sample’


-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null
) 


--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'


/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL

Advertisements

Read Full Post »


-- The objective is to find the second occurence of space and insert a carriage return. 

-- First Method:
----------------

DECLARE @passed VARCHAR(max)
SET @passed = 'First Second Third Fourth'

-- Extract all text until the second occurence 
select substring(@passed, 1,
		charindex(' ', @passed, charindex(' ', @passed) + 1)-1
		) [Space Occured First]


union  all 
select CHAR(13) + CHAR(10)
union 

-- Extract all text after the second occurence 
select substring(@passed
			, charindex(' ', @passed, charindex(' ', @passed) + 1)
			, len(@passed)
		) [Space Occured Second]
		
		
-- Second Method:
--------------------

declare @tbl_Foo table 
(
	FiledToChange varchar(50)
)

insert into @tbl_Foo values -- inserting this way can only be from SQL server 2008 and above 
('a b c')
,('1 2 3 4')
,('Single')

select FiledToChange as [Before] from @tbl_Foo

update @tbl_Foo
	set FiledToChange = ISNULL(
		case when charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1 > 0 then -- Look for a second space 
			substring(FiledToChange, 1, -- This will get two words 
				charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1)
			+ 
			CHAR(13) + CHAR(10) -- add the carriage return and line feed 
			+ 
			substring(FiledToChange -- add the rest of the text 
				, charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)
				, len(FiledToChange))
			end 
		,FiledToChange)
			
select FiledToChange as [After] from @tbl_Foo
-- Since SQL does not support displaying multiple lines, you will notice that instead there is an extra space. Applications supporting multiple lines can read this and conver it to multiple lines

Read Full Post »

Assume the below scenario:
—————————-

A system checks a stock level for a grocery store that sells 3 items and whenever a certain item runs out it flags up a message.

Say the 3 items are:
Mango
Orange
Apple

To make use of binary masking (also known as bitwise) to be able to track which needs to be highlighted, we need to do the following:

1) We will assign a binary number to each one of the item, 1 for denoting we need to flag a message and 0 f0r doing nothing. Each of the the items in turn will have a fixed position. So in this instance there are three position for three items and we can assign it as below:

Item Position Binary Value
—– ——— ————-
Mango 0 1
Orange 1 0
Apple 2 1

2) Now, we need a single decimal number that represents which items jave a binary 1 to flag a message and which don’t. To do that we use the Position value and treat that as the power of 2.
So, we use Position 1 and 3 (because those have the binary value 1 binded) and it should equate to:

(2^0) = 1
+ (2^2) = 4
————-
Total = 5
————-

3) The value 5 can then be sent to the section of the system which flags messages. It should then have a script to reverse it back to a binary number which is 101.

The position number is fixed and therefore can be easily be deduced as to which binary number is binded to whcih item.

This way by only sending an integer number we can for a group of item attached and interchagne flag data.

>Returns the binary representation of a number:
———————————————–

-- courtesy to a script somewhere in the internet
CREATE FUNCTION [csrt].[udf_ReturnBinaryPattern] 
(
	@Byte INT
) 

RETURNS CHAR(8) 
AS 
BEGIN 

    DECLARE  @Pattern CHAR(8) 
    SET @Pattern = '' 

    SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) + 
    convert(VARCHAR,(@Byte & 2) / 2) + 
    convert(VARCHAR,(@Byte & 4) / 4) + 
    convert(VARCHAR,(@Byte & 8) / 8) + 
    convert(VARCHAR,(@Byte & 16) / 16) + 
    convert(VARCHAR,(@Byte & 32) / 32) + 
    convert(VARCHAR,(@Byte & 64) / 64) + 
    convert(VARCHAR,(@Byte & 128) / 128) 

    RETURN (@Pattern) 

END 
GO

>Optional – Explaning & bitwise AND operator:
————————————-
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.

SELECT 170 & 75

Result is 10 (thisis a decimal number)

The & operator tranforms number or charters into binary and then adds them up:

170 = 0000 0000 1010 1010
75= 0000 0000 0100 1011
10 = 0000 0000 0000 1010

Another artice: http://sqlfool.com/2009/02/bitwise-operations/

Read Full Post »

-- Unpivot statement 

select * from @FruitSales

FruitName		SellingPrice	CostPrice
----------  		--------		-----------
Mango			296.23			26.09
Apple			259.45			19.04



Select *
from (select * from @FruitSales) original 
unpivot 
(
	CostFigures for 
	PriceType in (SellingPrice, CostPrice)
) as unpvt


FruitName		CostFigures		PriceType
-----------		------			----------
Mango			296.23			SellingPrice
Mango			26.09			CostPrice
Apple			259.45			SellingPrice
Apple			19.04			CostPrice

Read Full Post »

Below are three samples (from the same script) of how columns can be dynamically selected.

Simple :


/*
Dynamic column selection 

In Thing x number of columns need to be displayed and it change each time – a solution could be:
If the names of columns can be captured in a table then the below code could be used to build a dynamic sql:

store all the fields names in the variable:

*/

SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')


-- Sample:

set @pivotedfields = 'Select ' 
	SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')
						+ '')
	FROM    TableContainingColumnNames
	set @pivotedfields = @pivotedfields + ' from tableName'




-- Example of how to format any one type of column (taken from project_reports_cba)


			set @strSQL3 = ' select [Month],  dbo.udf_FormatToCurrency ([' + @Drill  
				+ '], ''%'', 2, ''false'', ''false'') [' +  @Drill
				+ '] from #tmpFinalnew order by closed_month' -- UMA (3/2/2012) Added square brackets 
			execute (@strSQL3 )

Complex:

	
	-------------- ================== UMA 17/4/2012  - Making columns dynamic ====================== ---------------- 
	 
	    -- select top 10 * from #tmpSavings_res1 -- test `  -- common table containing the fields 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Motor' -- for all and casualty 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

Extensive:

-------------- ================== Making columns dynamic ====================== ---------------- 
	 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Apple' 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 --select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

						declare @totalPT float 
						set @totalPT = (select sum(value) from #tmpSavings_res1	 where Bar = 'Total')
						
						INSERT INTO #tmpSavings_res1 (Bar, closed_month_txt, value, closed_month) -- no Drills
						SELECT b.Bar	 [Fruit],
								'Percentage' [Month],
								(select (sum(a.value)/@totalPT) * 100 
									from #tmpSavings_res1 a	
									where a.Bar = b.Bar
									group by a.Bar 
								), 
								getdate() 
						FROM   #tmpSavings_res1 b 
									--where category <> 'Total'  -- Need to show the Total for this one 
						GROUP BY b.Bar	
						
						--select * from #tmpSavings_res1 -- test ` 
							
							-- insert into #tmpFinal excluding the above 
						set @MySQL = 
							'select [Month], ' + 
								@listedFields +
							' into ##tmpFinal_Revamped
							from 
							(		    
								select	Bar [Fruit],
										closed_month_txt [Month], 
										cast(round(sum([value]),0) as int) [Value],
										closed_month
								from #tmpSavings_res1
								group by	Bar ,
											closed_month,
											closed_month_txt 

										
							)	as sourcetable
							PIVOT
							( 
							sum([Value])
							FOR [Fruit] in (' + 
								@listedFields + 
								 ')
							) as Final' 
							
							--print @MySQL -- test `
							exec (@MySQL) 
							
							select * from ##tmpFinal_Revamped -- test ` 


Read Full Post »

create function dbo.joinValues (@passed varchar(max))
returns varchar(max) -- if the variable to be returned is not mentioned then any varchar can be returned
as

-- add the below at the end
return @VarTypeVarChar --[place whatever yo want to return here from the function] -- this is where the variable name (of type varchar as specified above i.e. return varchar(max)) comes if not mentioned above

-- Invoke the function
SELECT dbo.joinValues ('eg.1') , dbo.joinValues (''what ever')

Read Full Post »

-- source http://searchsqlserver.techtarget.com/tip/Writing-T-SQL-functions-to-SQL-Server-system-schema

-- EXAMPLE 1

-- create a matematical function 
-- for example : solution for the parabolic equation
-- aX^2 + bX + c = 0
-- function gets A,B,C coefficients and the sign '+' or '-' to give
--  the first or second solution.
-- if  A=0 or no solution exists (B^2 &lt; 4AC)  NULL  is returned

CREATE FUNCTION system_function_schema.fn_qsol 

      (@a real, @b  real, @c  real, @sign char(1))
    RETURNS real 
AS
BEGIN
    Declare @sgn real;                           
    IF ( ((@b * @b - 4*@a*@c) &lt; 0) or (@a = 0) ) 
		RETURN (null)
    else
      begin
        if (@sign = '+') set @sgn = +1.0
        else set @sgn = -1.0
      end
      return ((-1 * @b + @sgn * sqrt (@b * @b - 4*@a*@c)) /  (2*@a))   
END
Go

--Example of a call to the function

-- Invoke the function
USE pubs
GO
SELECT fn_qsol (1,-1,-1,'+') , fn_qsol (1,-1,-1,'-')

Read Full Post »

Older Posts »