Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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.
 
Please help me.
 
Thanks,
 
Anil Kumar
 
[edit]Code block added - OriginalGriff[/edit]
Posted 25-Jan-13 20:56pm
Edited 25-Jan-13 21:05pm
v2
Comments
Sandeep Mewara at 26-Jan-13 7:14am
   
What have you tried so far?
Rate this: bad
good
Please Sign up or sign in to vote.

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]
  Permalink  
v3
Comments
anil_kumar_bhakta at 26-Jan-13 12:01pm
   
Hi Mike, It's working like charm! Thanks a lot.
Rate this: bad
good
Please Sign up or sign in to vote.

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
Please try following
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
  Permalink  
v2
Comments
anil_kumar_bhakta at 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.
Tharaka MTR at 26-Jan-13 10:56am
   
Agreed, I just realize that.
Tharaka MTR at 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 at 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 at 26-Jan-13 12:01pm
   
Solution 2 is working fine for me. But your query is not giving the desired result. thanks for trying.
Tharaka MTR at 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.
Mike Meinz at 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.
Tharaka MTR at 26-Jan-13 12:49pm
   
Agreed. But my point is, I wrote the query for the inserted data and for the expected result set.
Mike Meinz at 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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 730
1 OriginalGriff 503
2 Abhinav S 278
3 thatraja 245
4 Emre Ataseven 190
0 Sergey Alexandrovich Kryukov 8,322
1 OriginalGriff 4,790
2 Peter Leow 3,784
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 26 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid