Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL grouping

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

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

579 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