13,550,215 members
See more:
Hi Experts,

I have some data like below in SQL Server Table 'tblLeave': -

```PAYCODE	LV_TYPE	FROM_DATE	TO_DATE	        LVALUE
5023	SL    	14/12/2012 0:00	14/12/2012 0:00	1
5023	SL    	15/12/2012 0:00	15/12/2012 0:00	1
5023	COF   	16/12/2012 0:00	16/12/2012 0:00	1
5023	SL    	19/12/2012 0:00	19/12/2012 0:00	1
5023	SL    	22/12/2012 0:00	22/12/2012 0:00	1
5023	SL    	23/12/2012 0:00	23/12/2012 0:00	1
5023	SL    	24/12/2012 0:00	24/12/2012 0:00	1
5023	PL    	28/12/2012 0:00	28/12/2012 0:00	1
5023	PL    	29/12/2012 0:00	29/12/2012 0:00	1
5023	PL    	30/12/2012 0:00	30/12/2012 0:00	1
5023	PL    	31/12/2012 0:00	31/12/2012 0:00	1```

Query result must be as below: -

```PAYCODE LV_TYPE FROM_DATE       TO_DATE         LVALUE
5023    SL      14/12/2012 0:00 15/12/2012 0:00 2
5023    COF     16/12/2012 0:00 16/12/2012 0:00 1
5023    SL      19/12/2012 0:00 19/12/2012 0:00 1
5023    SL      22/12/2012 0:00 24/12/2012 0:00 3
5023    PL      28/12/2012 0:00 31/12/2012 0:00 4```

If same type of leave taken continously,it shoul be merged in one row mentioning from_date to to_date.

Thanks,

Anil Kumar

Posted 25-Jan-13 20:56pm
Updated 25-Jan-13 21:05pm
v2
Sandeep-Mewara 26-Jan-13 7:14am

What have you tried so far?

## Solution 1

Check this.
```SELECT LV_TYPE,LV_TYPE,MIN(FROM_DATE) AS FROM_DATE ,MAX(TO_DATE) AS TO_DATE, COUNT(LVALUEP) AS LVALUE
FROM tblLeave
GROUP BY PAYCODE,LV_TYPE```

```SELECT PAYCODE,LV_TYPE, MIN(FROM_DATE) AS FROM_DATE,
MAX(FROM_DATE) AS TO_DATE, COUNT('A') AS LVALUE
FROM (
SELECT PAYCODE,LV_TYPE,FROM_DATE,
DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS DtRange
FROM tblLeave) AS dt
GROUP BY PAYCODE,LV_TYPE,DtRange
ORDER BY FROM_DATE```

```PAYCODE     LV_TYPE FROM_DATE  TO_DATE    LVALUE
----------- ------- ---------- ---------- -----------
5023        SL      2012-12-14 2012-12-15 2
5023        COF     2012-12-16 2012-12-16 1
5023        SL      2012-12-19 2012-12-19 1
5023        SL      2012-12-22 2012-12-24 3
5023        PL      2012-12-28 2012-12-31 4```
v2
anil_kumar_bhakta 26-Jan-13 10:47am

Thanks for trying. But this is not the correct answer. Please see the result again. It's fine for "COF" and "PL" because "from_date" is consecutive. But in case of "SL" this is not consecutive.
tharakamtr 26-Jan-13 10:56am

Agreed, I just realize that.
tharakamtr 26-Jan-13 11:21am

ok. I have modified the query. Please check and let me know whether it is ok or not.
anil_kumar_bhakta 26-Jan-13 12:49pm

Thanks a lot, Tharak MTR! I have again tried it and it's also working like charm!
anil_kumar_bhakta 26-Jan-13 12:01pm

Solution 2 is working fine for me. But your query is not giving the desired result. thanks for trying.
tharakamtr 26-Jan-13 12:24pm

Could you please bit explain me, what is the problem with my updated query? I think my output is exactly match to your output and fully compatible with SQL 2008.
MikeMeinz 26-Jan-13 12:35pm

Hello Tharaka MTR,

Thank your for posting your query. It is very clever. I learned some things from it.

I agree that your result set matches the results set in the question. However, it looks to me that it does not handle the case where one of the original "LValue"s contains other than the value 1. For example, should a from date be 14/12/2012 and a to date be 15/12/2012, then LValue would be 2.
tharakamtr 26-Jan-13 12:49pm

Agreed. But my point is, I wrote the query for the inserted data and for the expected result set.
MikeMeinz 26-Jan-13 13:05pm

I know what you mean. Unfortunately, his test data does not cover all of the possibilities. I notice, in many of these questions, we only get partial information.

## Solution 2

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]```
v3
anil_kumar_bhakta 26-Jan-13 12:01pm

Hi Mike,

It's working like charm! Thanks a lot.

Top Experts
Last 24hrsThis month
 OriginalGriff 250 Richard MacCutchan 120 ppolymorphe 110 Alan N 70 BillWoodruff 60
 OriginalGriff 4,920 ppolymorphe 2,107 Richard MacCutchan 1,981 Wendelius 1,865 Maciej Los 1,515

Web02 | 2.8.180515.1 | Last Updated 26 Jan 2013