Solved

SQL grouping

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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