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
|
Sunday, 15 December 2013
Create FUNCTION TO find TAX in sqlserver
SET NUMBER of RECORDS per PAGE in SQLSERVER
PAGING in SQL SERVER
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:
Saturday, 14 December 2013
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.
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
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:
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)
Saturday, 30 November 2013
Ranking Functions & Aggregate Functions & Analytic Functions
Ranking Functions & Aggregate Functions & Analytic Functions
- Ranking window functions
- Row_Number()
- Rank()
- Dense_Rank()
- 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
MIN
,MAX
,AVG
,SUM
COUNT
,COUNT_BIG
- CHECKSUM_AGG
STDEV
,STDEVP
,VAR
,VARP
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
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
|
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
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)
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]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 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]
Subscribe to:
Posts (Atom)
EMP, DEPT Sample script
/****** Object: Table [dbo].[DEPT] Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...
-
TRUNCATE Data From All TABLES of a DataBase in SQL Server Here the following script help full to you to truncate data from all tables in...
-
Convert Delimited XML value in a column into Column (More than 8000 characters) To handle more than 8000 characters, convert that data in...
-
Different types of temp tables Step 1: We have different types of temporary data storage objects, probably we call as temp tables. ...