SQL Server Query statement

I tested Solution 1 and it does not return the result set that you required.

The Stored Procedure below has been tested and returns the result set that you showed in your example. Create this Stored Procedure in your SQL Server database and execute the Stored Procedure in your program. The Stored Procedure will return the rows as shown in your example.

There may be some fancy SQL statement that will return the same result but I wasn't able to figure it out. I read through my Jim Celko's SQL for Smarties book and this type of query was not in there. I welcome other contributors that may know SQL better than I to submit a solution that is done in a single SQL statement rather than a Stored Procedure algorithm.

USE [YourDatabaseNameGoesHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Mike Meinz
-- Create date: 26 January 2013
-- Description:	Summarizes Leave Rows
-- =============================================
CREATE PROCEDURE SUMMARIZE_LEAVE_ROWS
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @curPayCode int,@curLV_Type varchar(3),@curFrom_Date date,@curTo_Date date,@curLValue int
DECLARE @lastPayCode int,@lastLV_Type varchar(3),@lastFrom_Date date,@lastTo_Date date,@lastLValue int
DECLARE @SummarizedRowsTable TABLE (PayCode int,LV_Type varchar(3),From_date date,To_Date date,lvalue int) 
-- 
-- Select rows from tblLeave
Declare Summary_Leave_Cursor CURSOR FOR
	SELECT PayCode,LV_Type,From_Date,To_Date,lvalue from tblLeave order by PayCode, From_date, LV_Type
DECLARE @FIRST_TIME BIT
SET @FIRST_TIME=1
OPEN Summary_Leave_Cursor
-- Get first row
FETCH NEXT FROM Summary_Leave_Cursor INTO @curPayCode,@curLV_Type,@curFrom_date,@curTo_Date,@curLValue
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @FIRST_TIME=1
		BEGIN
		SET @FIRST_TIME=0
		-- Save the current row data 
		SET @lastPayCode=@curPayCode
		SET @lastLV_Type=@curLV_Type
		SET @lastFrom_Date=@curFrom_Date
		SET @lastTo_Date=@curTo_Date
		SET @lastLValue=@curLValue
		END
	ELSE
		BEGIN
		-- Check if same PayCode and leave type and current FromDate is one day later than last ToDate
		-- Use DATEDIFF and DATEADD to compare the dates
		-- If times need to be taken into consideration, then this will have to be changed
		IF @curPayCode=@lastPayCode AND @curLV_Type=@lastLV_Type AND DATEDIFF(day,@CurFrom_date,DATEADD(day,1,@lastTo_date))=0 
			BEGIN
			-- Accumulate the LValue
			SET @lastlvalue=@lastlvalue + @curlvalue
			-- Update the ToDate with the new ToDate
			SET @lastTo_Date=@curTo_Date
			END
		ELSE
			BEGIN
			-- Insert summarized data into the temporary table
			INSERT INTO @SummarizedRowsTable (PayCode,LV_Type,From_Date,To_Date,lvalue) Values (@lastPayCode, @lastLV_Type, @lastFrom_Date,@lastTo_Date,@lastLValue);
			-- Save the current row data 
			SET @lastPayCode=@curPayCode
			SET @lastLV_Type=@curLV_Type
			SET @lastFrom_Date=@curFrom_Date
			SET @lastTo_Date=@curTo_Date
			SET @lastLValue=@curLValue
			END
		END
	FETCH NEXT FROM Summary_Leave_Cursor INTO @curPayCode,@curLV_Type,@curFrom_date,@curTo_Date,@curLValue;
END
-- Insert the last summarized row into the temporary table
INSERT INTO @SummarizedRowsTable (PayCode,LV_Type,From_Date,To_Date,lvalue) Values (@lastPayCode, @lastLV_Type, @lastFrom_Date,@lastTo_Date,@lastLValue);
CLOSE Summary_Leave_Cursor;
DEALLOCATE Summary_Leave_Cursor;
--
-- Return the temporary table as the stored procedure result
SELECT PayCode,LV_Type,From_Date,To_Date,lvalue FROM @SummarizedRowsTable order by PayCode, From_Date, LV_Type;
END

This is the table design that I used to test the Stored Procedure:

CREATE TABLE [dbo].[tblLeave](
	[PayCode] [int] NOT NULL,
	[LV_Type] [varchar](3) NOT NULL,
	[From_date] [date] NOT NULL,
	[To_Date] [date] NOT NULL,
	[LValue] [int] NOT NULL
) ON [PRIMARY]