Financial Year Diff

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FN_YEARDIFF]
(@STD AS DATETIME,
@FND AS DATETIME)
RETURNS VARCHAR(16)
AS
BEGIN
            declare @SD     AS INT
            declare @SM    AS INT
            declare @SY     AS INT
            declare @FD     AS INT
            declare @FM     AS INT
            declare @FY     AS INT
            DECLARE @YVAR        AS INT
            DECLARE @MVAR       AS INT
            DECLARE @DIFF          AS VARCHAR(16)
            DECLARE @EM            AS INT
            DECLARE @DVAR        AS INT

            set @SD=DAY(@STD)
            set @SM=MONTH(@STD)
            set @SY=YEAR(@STD)
           
            set @FD=DAY(@FND)
            set @FM=MONTH(@FND)
            set @FY=YEAR(@FND)
           
            SET @YVAR = @FY - @SY
            select @EM=0

            IF @SY < @FY
            BEGIN
                        SET @YVAR = @YVAR - 1
                        SET @EM=12-@SM

                        SET @MVAR =@EM+@FM

                        IF @MVAR=12
                        BEGIN
                                    SET @DVAR = @FD - @SD
                                    IF @DVAR < 0
                                                SET @MVAR = @MVAR - 1
                                    ELSE
                                    BEGIN
                                                SET @YVAR=@YVAR+1
                                                SET @MVAR=@MVAR-12
                                    END
                        END
                        ELSE
                        BEGIN
                                    IF @MVAR>12
                                    BEGIN
                                                SET @YVAR=@YVAR+1
                                                SET @MVAR=@MVAR-12
                                               
                                                SET @DVAR = @FD - @SD
                                                IF @DVAR < 0
                                                            SET @MVAR = @MVAR - 1
                                    END
                                    ELSE
                                    BEGIN
                                                SET @DVAR = @FD - @SD
                                                IF @DVAR < 0
                                                            SET @MVAR = @MVAR - 1                  
                                    END
                        END
            END
            ELSE
            BEGIN
                        IF @SY=@FY
                        BEGIN

                                    SET @YVAR=0
                                    SET @MVAR=@FM-@SM
                                    SET @DVAR = @FD - @SD    
                                    IF @DVAR < 0
                                                SET @MVAR = @MVAR - 1
                        END
                        ELSE
                                    SELECT @YVAR=0,@MVAR=0
            END
DECLARE @MINYEAR DECIMAL (4,2)
--select right(cast(2.0/12.0 AS decimal(4,2)),2)
 IF @MVAR <> 0
SET @MINYEAR = @MVAR/12.0
 ELSE
SET @MINYEAR = @MVAR      

       SET @DIFF=CAST(@YVAR AS VARCHAR(4)) + '.' + right(CAST(@MINYEAR  AS VARCHAR(50)),2)
--            SELECT @DIFF
            RETURN (@DIFF)
END

Comments

Post a Comment

Popular posts from this blog

Who Deleted and Drop the record in table

CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] (     [Sr.No] INT IDENTITY,     [Date] DATETIME DEFAULT GETDATE (),     [City] CHAR (25) DEFAULT 'Bangalore');           select * from [Location]       INSERT INTO Location DEFAULT VALUES ; GO 100 DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO SELECT     [Transaction ID],     Operation,     Context,     AllocUnitName   FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'       SELECT     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = '0000:000002ef' AND     [Operat...
Read more

Generate Password

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...
Read more

Key Board Shortcuts

Image
100 KEYBOARD SHORTCUTS CTRL + C (Copy) CTRL + X (Cut) CTRL + V (Paste) CTRL + Z (Undo) DELETE (Delete) SHIFT + DELETE (Delete the selected item permanently without placing the item in the Recycle Bin) CTRL while dragging an item (Copy the selected item) CTRL + SHIFT while dragging an item (Create a shortcut to the selected item) F2 KEY (Rename the selected item) CTRL + RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL + LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL + DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL + UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL + SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document) CTRL + A (Select all) F3 KEY (Search for a file or a folder) ALT...
Read more