Solved

SQL grouping

Posted on 2014-02-11
5
226 Views
Last Modified: 2014-05-27
I have a query which lists staff holidays.

What i would like however is to somehow group and count consecutive days together.    In the Screen shot attached the first two days are 19/09/2013 - 20/09/2013.   So instead of showing each individual day separate I could could these as two.  



Example 1 -  


Change this
------------------------
FROM                  TO                        Days
19/09/2013            19/09/2013            1
20/09/2013            20/09/2013            1

To this
FROM                  TO                        Days
19/09/2013            20/09/2013            2




Example 2 -  


Change this
FROM                  TO                        Days
17/07/2013            17/07/2013            1
18/07/2013            18/07/2013            1
19/07/2013            19/07/2013            1


To this
FROM                  TO                        Days
17/07/2013            19/07/2013            3







ALTER PROCEDURE [dbo].[sp_GetIndividualHolidayReport]
(
@Username   varchar(50)
)
AS
DECLARE @StartingHolidays  decimal(4,2)
DECLARE @StartingDate  date
 
SELECT @StartingHolidays  = (Select Top 1 Days FROM dbo.StartingHolidays WHERE Username = @Username)
SELECT @StartingDate  = (SELECT [StartDate]   FROM [Timesheets].[dbo].[Periods] WHERe IsCurrent = 1)
 
 
SELECT    
TIMESHEET.DATE,
1 AS Days,
@StartingHolidays AS StartingHolidays,
ApprovedHolidays.TimeSheetID,
ApprovedHolidays.DateAproved,
ApprovedHolidays.ApprovedBy,
ApprovedHolidays.Notes,
ApprovedHolidays.UserName
FROM         TIMESHEET Full Outer JOIN
  ApprovedHolidays ON TIMESHEET.RefId = ApprovedHolidays.TimeSheetID
WHERE     (TIMESHEET.IdVolNow = @Username) AND (TIMESHEET.ContractId = 136)
AND @StartingDate >= @StartingDate

Open in new window

Holiday.PNG
0
Comment
Question by:Kevin Robinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39850326
In both your examples, spell out which column that is not in the mockup data that we are to group the days.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39850445
What version of SQL Server are you running.  There is functionality in 2012 that makes this much easier.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39850449
Jim, I cannot look at this right now; however, I interpret the request as finding the consecutive records in the database then doing a date difference (or count of the rows that are consecutive).

In the example, there are three records for 2013-07-17, 2013-07-18, and 2013-07-19 and the sample result shows a from as min date and to as max date with count 3.  Essentially cluster date ranges together.

--Kevin

P.S. the new windowing functions within SQL 2012 will help as pointed out above, so one solution would be to install a copy of SQL 2012 (even the free version) and run this query through the newer engine.  I meant to mention that as it first came to mind, but I saw the question had SQL 2008 associated to it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39850944
Can you post the query that lists the staff holidays?
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39851259
Maybe this example will help

CREATE TABLE #Holiday
(
	DateFrom	DATETIME,
	DateTo		DATETIME
)

INSERT INTO #Holiday (DateFrom, DateTo)
VALUES ('9/19/2013', '9/19/2013'),
	('9/20/2013', '9/20/2013'),
	('9/23/2013', '9/23/2013'),
	('7/17/2013', '7/17/2013'),
	('7/18/2013', '7/18/2013'),
	('7/19/2013', '7/19/2013'),
	('7/22/2013', '7/22/2013'),
	('7/23/2013', '7/23/2013'),
	('7/24/2013', '7/24/2013'),
	('7/25/2013', '7/25/2013'),
	('7/26/2013', '7/26/2013');

WITH cte
AS
(
	SELECT DateFrom, DateTo,
		DATEADD(DAY, -ROW_NUMBER() OVER(ORDER BY DateFrom), DateFrom) AS DateGroup
	FROM #Holiday
)
SELECT MIN(DateFrom) AS HolidayStart,
	MAX(DateFrom) AS HolidayEnd,
	COUNT(DateFrom) AS HolidayLength
FROM cte
GROUP BY DateGroup

Open in new window


Results...

HolidayStart                              HolidayEnd                              HolidayLength
2013-07-17 00:00:00.000      2013-07-19 00:00:00.000      3
2013-07-22 00:00:00.000      2013-07-26 00:00:00.000      5
2013-09-19 00:00:00.000      2013-09-20 00:00:00.000      2
2013-09-23 00:00:00.000      2013-09-23 00:00:00.000      1
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question