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)
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. ...