?
Solved

SQL grouping

Posted on 2014-02-11
5
Medium Priority
?
229 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 66

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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

765 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