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]