Solved

SQL grouping

Posted on 2014-02-11
5
213 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
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 59

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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now