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!
Course Of The Month
5 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL grouping
Posted on 2014-02-11
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.
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
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
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
[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
2
- +1
5 Comments
Active today
In both your examples, spell out which column that is not in the mockup data that we are to group the days.
What version of SQL Server are you running. There is functionality in 2012 that makes this much easier.
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.
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.
Maybe this example will help
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
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
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
Featured Post
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.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
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.
Suggested Courses
Course of the Month5 days, 1 hour left to enroll
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.