Create FUNCTION TO find TAX in sqlserver

CREATE FUNCTION F_Get_IncommTaxx(@sal money) -- 2500000 RETURNS money -- SELECT DBO.F_Get_IncommTaxx(300000) AS BEGIN       DECLARE @R_sal MONEY=@sal-150000 -- 350000       DECLARE @tax MONEY =0        IF(@R_sal>0 AND @R_sal<=100000)        BEGIN             SET @tax=(@R_sal *10)/100             SET @R_sal=@R_sal-100000        END        ELSE IF(@R_SAL>0)        BEGIN             SET @tax=(100000 *10)/100 -- 10000             set @R_sal=@R_sal-100000  -- 250000        END        -- 20% slab        if(@R_sal>0 AND @R_sal<=200000)        BEGIN             SET @tax=@tax+((@R_sal *20)/100)             SET @R_sal=@R_sal-200000        END        ELSE IF(@R_SAL>0)        BEGIN             SET @tax=@tax+((200000 *20)/100) -- 50000             SET @R_sal=@R_sal-200000          END                -- 30% slab        IF(@R_sal>0 )        BEGIN             SET @tax=@tax+((@R_sal *30)/100) -- 65500        END                      RETURN @tax; END

SET NUMBER of RECORDS per PAGE in SQLSERVER

PAGING in SQL SERVER

USE AdventureWorksDW
GO
CREATE PROC usp_paging(@NOOFRECORDS INT,@PAGENO INT)
AS
--EXEC usp_paging 10,2
BEGIN

DECLARE @STING INT=((@PAGENO-1)*@NOOFRECORDS)+1
DECLARE @ENDING INT=@NOOFRECORDS*@PAGENO
PRINT @STING
PRINT @ENDING
;WITH X AS(
SELECT *,DENSE_RANK() OVER (ORDER BY TIMEKEY)AS DENSERANK
,RANK() OVER (ORDER BY TIMEKEY) AS RANK,
ROW_NUMBER() OVER(PARTITION BY TIMEKEY ORDER BY TIMEKEY)AS ROWNUMBER  FROM FactFinance)
SELECT * FROM X WHERE ROWNUMBER=1 AND DENSERANK BETWEEN @STING AND @ENDING
ORDER BY TimeKey
END

Results:

IGNORE Duplicates while inserting NEW Records

SQL Constraint IGNORE_DUP_KEY on Update

The below example gives you a better identification on IGNORE_DUP_KEY.

      Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

IMPORTANT Note:

         IGNORE_DUP_KEY cannot be set to ON  for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY)
GO
-- Start with values 1 & 5 in the table

INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY) WITH (IGNORE_DUP_KEY = ON));
GO
-- Start with values 1 & 5 in the table


INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

If we want to add IGNORE_DUP_KEY on primary key existing, first drop the CONSTRAINT then recreate along with WITH(IGNORE_DUP_KEY=ON)
Drop and alter of a table are the examples shown below

CREATE TABLE #Big (pk int not null CONSTRAINT pk_key primary key WITH(IGNORE_DUP_KEY=ON))
INSERT INTO #Big VALUES(1),(2),(3)
INSERT INTO #Big VALUES(2),(5),(6)
ALTER TABLE #BIG DROP CONSTRAINT PK_KEY
alter table #big add constraint pk_key primary key(PK) WITH(IGNORE_DUP_KEY=ON) 

Ranking Functions & Aggregate Functions & Analytic Functions

• Ranking window functions

1. Row_Number()
2. Rank()
3. Dense_Rank()
4. Ntile()

• Aggregate window functions
  • MIN, MAX, AVG, SUM
  • COUNT, COUNT_BIG
  • CHECKSUM_AGG
  • STDEV, STDEVP, VAR, VARP
• Analytic window functions
  • LAG, LEAD
  • FIRST_VALUE, LAST_VALUE
  • PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST

Ranking Functions

use AdventureWorksDW

go

SELECT *,

  ROW_NUMBER() OVER(ORDER BY salesreasonreasontype DESC) AS RowNumber,

  RANK() OVER(ORDER BY salesreasonreasontype DESC) AS BasicRank,

  DENSE_RANK() OVER(ORDER BY salesreasonreasontype DESC) AS DenseRank,

  NTILE(3) OVER(ORDER BY salesreasonreasontype DESC) AS NTileRank

FROM

  DimSalesReason

 results returned by the SELECT statement.

Aggregate Functions

use AdventureWorksDW

go

SELECT

  title,departmentname,

  COUNT(baserate) OVER(PARTITION BY departmentname) AS CountryCount,

  SUM(baserate) OVER(PARTITION BY departmentname) AS TotalSales,

  AVG(baserate) OVER(PARTITION BY departmentname) AS AverageSales

FROM

  DimEmployee

ORDER BY

  title,departmentname DESC;

RESULT:

Analytic Functions

SELECT

  SalesGroup,

  Country,

  AnnualSales,

  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup

    ORDER BY AnnualSales DESC) AS HighestSales,

  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup

    ORDER BY AnnualSales DESC) AS LowestSales

FROM

  RegionalSales;

OUTPUT:

SalesGroup	Country	AnnualSales	HighestSales	LowestSales
Europe	United Kingdom	32000	32000	32000
Europe	Germany	22000	32000	22000
Europe	France	19000	32000	19000
Europe	Italy	18000	32000	18000
Europe	Greece	16000	32000	16000
Europe	Spain	16000	32000	16000
North America	Canada	32000	32000	32000
North America	Mexico	28000	32000	28000
North America	United States	22000	32000	22000
Pacific	China	28000	28000	28000
Pacific	Japan	22000	28000	22000
Pacific	Singapore	21000	28000	21000
Pacific	Malaysia	19000	28000	19000
Pacific	Australia	18000	28000	18000
Pacific	New Zealand	18000	28000	18000
Pacific	Thailand	17000	28000	17000

DateTime Functions

• DateTime Function in SQL Server

• GETDATE()

• DATEPART()

• DATEDIFF()

• DATENAME()

• DAY()

• MONTH()

• YEAR()

GETDATE():

 This value is derived from the operating system of the computer on which the instance of SQL Server is running.

SELECT GETDATE() as TODAY

Ans:

DATEPART():

It returns the part of the date like YEAR,MONTH,WEEK,DAY,QUARTER,DAYOFYEAR,WEEKDAY,HOUR,MINUTE
,SECOND and MILLISECOND



 DATEDIFF():

Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

ex:

SELECT DATEDIFF(YEAR,HIREDATE,GETDATE())AS DIFFERENCE FROM EMP

DATENAME():

SELECT DATENAME(DAY, '2007-06-01')as dayinmonth,DATENAME(DY, '2007-06-01')as dayinyear,DATENAME(WEEKDAY, '2007-06-01')as weekday;

ans:

DAY():

Returns an integer representing the day (day of the month) of the specified date.

Ex:

SELECT DAY(GETDATE())AS DAYOFMONTH

MONTH():

SELECT MONTH(GETDATE())AS MONTHOFYEAR

YEAR():

SELECT YEAR(GETDATE())AS YEAR

CONVERSIONS

note:conversions are collected from different websites
declare @date datetime

set @date='2012-03-16 '

SELECT convert(varchar, @date, 103)
 

for further reference below you can find all the possible format for a date

100 – mon dd yyyy hh:mmAM (or PM)
    – Oct  2 2008 11:01AM         
101 – mm/dd/yyyy - 10/02/2008                 
102 – yyyy.mm.dd – 2008.10.02          
103 – dd/mm/yyyy
104 – dd.mm.yyyy
105 – dd-mm-yyyy
106 – dd mon yyyy
107 – mon dd, yyyy
108 – hh:mm:ss
109 – mon dd yyyy hh:mm:ss:mmmAM (or PM)
    – Oct  2 2008 11:02:44:013AM  
110 – mm-dd-yyyy
111 – yyyy/mm/dd
112 – yyyymmdd
113 – dd mon yyyy hh:mm:ss:mmm
    – 02 Oct 2008 11:02:07:577    
114 – hh:mm:ss:mmm(24h)
120 – yyyy-mm-dd hh:mm:ss(24h)
121 – yyyy-mm-dd hh:mm:ss.mmm
126 – yyyy-mm-ddThh:mm:ss.mmm
     – 2008-10-02T10:52:47.513

 Remaining date functions in sql2012

SELECT Datefromparts(2010, 5, 14) AS [DateParts]

SELECT Dateadd(mm, 2, Getdate()) AS [DateAdd]

SELECT Datediff(mm, '2014-01-01', Getdate()) AS [DateDiff]

SELECT Datename(day, Getdate()) AS [dayname]

SELECT Datepart(mm, Getdate()) AS [DATEPART]

SELECT Datetime2fromparts(2014, 12, 30, 4, 5, 6, 7, 1) AS [DATETIME2FROMPARTS]

SELECT Datetimefromparts(2010, 10, 25, 10, 11, 22, 9) AS[DATETIMEFROMPARTS]

SELECT Switchoffset(Sysdatetimeoffset(), '+06:30') AS [SWITCHOFFSET]

SELECT Datetimeoffsetfromparts(2014, 08, 25, 10, 10, 20, 1, 5, 30, 6)

AS [DATETIMEOFFSETFROMPARTS]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Getdate() AS [GETDATE]

SELECT Getutcdate() AS [GETUTCDATE]

SELECT Isdate('2014-06-20') AS [ISDATE]

SELECT Eventdata() AS [EVENTDATA]

SELECT Objidupdate(1) AS [OBJIDUPDATE]

SELECT Datalength('2014-05-25') AS [DATALENGTH]

SELECT Stats_date(2, 12563) AS [STATS_DATE]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Sysutcdatetime() AS [SYSUTCDATETIME]

SELECT Todatetimeoffset('2014-06-03', 500) AS [TODATETIMEOFFSET]

STRING FUNCTIONS

T-SQL String Functions

1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.

Example:
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2000')
Result: 6

2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.

Example:
Select LEFT ('MICROSOFT SQL SERVER 2000',4)
Result: MICR

3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.

Example:
Select RIGHT ('MICROSOFT SQL SERVER 2000',4)
Result: 2000

4. LEN string function takes 1 argument as string value and returns the length of entered string.

Example:
Select LEN ('MICROSOFT SQL SERVER 2000')
Result: 25

5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.

Example:
Select REPLACE ('MICROSOFT SQL SERVER 2000','MICROSOFT','MS')
Result: MS SQL SERVER 2000

6. STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.

Example:
Select STUFF ('MICROSOFT SQL SERVER 2000', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2000

7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length

Example:
Select SUBSTRING ('MICROSOFT SQL SERVER 2000', 11, 3)
Result: SQL

8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.

Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE

10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.

Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA

11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select LTRIM (‘ ASP ’)
Result: ASP-----
blanks at the right side not removed.

12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.

Example:
select RTRIM (‘ ASP ’)
Result: -----ASP
blanks at the left side not removed.

13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.

Example:
select PATINDEX('%RO%','MICROSOFT')
Results: 4

14. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.

Example:
select STR(140.15, 6, 1)
Result: 140.2

15. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.

Example:
select ASCII('A')
Result: 65