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.
COURSE of the MONTH
8 days, 21 hours 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.
Active 6 days ago
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…
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
Course of the Month8 days, 21 hours left to enroll
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.