January 30, 2015 at 3:49 pm
I need to build maintanance calender,
The maintanance will be from monday - sunday.
declare @users(UserID int, Name varchar(50))
insert into @users
select 1,'Member1' union all
select 2,'Member2' union all
select 3,'Member3' union all
select 4,'Member4' union all
select 5,'Member5' union all
select 6,'Member6'
Lets assume the maintanance start on 02/02/2015.
Sample Output
select 'Member1' as Member,'02/02/2015 - 02/08/2015' as Support1,'03/16/2015 - 03/22/2015' as support2,
'04/27/2015 - 05/03/2015' as support3.....
select 'Member2' as Member,'02/09/2015 - 02/15/2015' as Support1,'03/23/2015 - 03/29/2015' as support2,
'05/04/2015 - 05/10/2015' as support3.....
I need to build the calender till dec 31st 0215. Also, i need to build for the 6 members
any sample query please
January 30, 2015 at 4:25 pm
Not enough information to really do anything. Not sure how to get from your sample to the sample output.
January 30, 2015 at 4:37 pm
Hi Lynn,
thanks for your reply and basically in am trying to build a on call maintenance calender. the start date of the call should be as input to the logic.
for example, the member1 will be on call for the below dates.
02/02/2015 - 02/08/2015 as Support1
03/16/2015 - 03/22/2015 as support2,
04/27/2015 - 05/03/2015 as support3.....
like this, i need to build the calender for each 6 members for year 2015.
is my requirement clear now?
January 30, 2015 at 4:45 pm
if still not clear, below are the sample with rows/columns
2/2/2015 - 2/8/2015member1
2/9/2015 - 2/15/2015member2
2/16/2015 - 2/22/2015member3
2/23/2015-3/01/2015member4
3/02/2015 - 3/08/2015member5
3/09/2015 - 3/15/2015member6
3/16/2015 - 3/22/2015member1
3/23/2015 - 3/29/2015member2
.
.
.
.
.
also i need to bring the data into column wise.
member1 support1 support2 support3 suppport4 support5....
member2 support1 support2 support3 support4 support5...
member3 support1 support2 support3 support4 support5..
January 30, 2015 at 4:49 pm
If I understand your question correctly (and I'm not sure I do), you have a certain number of users, and you want to, presumably on some sort of round-robin basis, assign them to a week in the year? Your "wide" result set with a dedicated column for each maintenance window, is bad design, so I'll skip that. If you're hellbent on having it that way, look into dynamic pivots.
But back to the issue at hand. The first step I'd take is to build a calendar table, if you don't already, build one. I've built one inline below just for simplicity. Then get the number for every week in the year. Again, for simplicity, I've done this over only a 1 year period. If you needed it to span years, it could be tweaked to do so.
Then, your members table MUST BE 1-INCREMENTED INTEGERS. If your user ids are NOT sequential, spoof it by using a row_number over the users. If you don't, when you do the join magic to make this all work, it wont work right.
FINALLY, the grand finale, join from the calendar table to the surrogate user id using the modulo operator to roll each user into the next week. Also note i set DATEFIRST 1. This makes it so that a week starts on Monday.
set datefirst 1
;with nums as
(
select num = 1 union all select 1 union all select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1 union all select 1
), cal as
(
select
theDate = cast(row_number() over (order by (select null)) + 42000 as datetime),
theWeek = datepart(week, cast(row_number() over (order by (select null)) + 42000 as datetime))
from nums a, nums b, nums c
), members as
(
--THESE MUST BE INCREMENTED BY 1. IF THEY'RE NOT, BUILD SURROGATE IDs USING ROW_NUMBER.
select id = 1 union all
select id = 2 union all
select id = 3 union all
select id = 4 union all
select id = 5 union all
select id = 6
)
select
c.theWeek,
StartDate = min(theDate),
EndDate = max(theDate),
Id = max(m.Id)
from cal c
inner join members m
on c.TheWeek % 6 = m.Id --6 is the number of memebers possible. If you had 200, you'd do this mod-200
where datepart(year, theDate) = 2015
group by c.TheWeek
(phew). Got all that?
January 30, 2015 at 4:59 pm
Hi Jee,
Thanks for your reply and your understanding is correct. it's round robin basis.
i ran your query and sorry it gives wrong result, if i have my start date as 02/02/2015,
2/2/2015 - 2/8/2015member1
2/9/2015 - 2/15/2015member2
2/16/2015 - 2/22/2015member3
2/23/2015-3/01/2015member4
3/02/2015 - 3/08/2015member5
3/09/2015 - 3/15/2015member6
3/16/2015 - 3/22/2015member1
3/23/2015 - 3/29/2015member2
.
.
.
.
.
any sample please
January 30, 2015 at 5:07 pm
I have an idea, show us what you have tried.
January 30, 2015 at 5:17 pm
Hi Lyn,
this is what i hav etried, but getting wrong reult
DECLARE @users TABLE(UserID int, Name varchar(50))
insert into @Users
select 1,'Member1' union all
select 2,'Member2' union all
select 3,'Member3' union all
select 4,'Member4' union all
select 5,'Member5' union all
select 6,'Member6'
DECLARE @Calendar TABLE
(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)
Declare @beginDate Date, @endDate Date
Select @beginDate = '02/01/2015', @endDate = '12/31/2015'
While @beginDate <= @endDate
Begin
Insert Into @Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)
Select
@beginDate As CalendarDate
,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
,DATEPART(Year, @beginDate) As YearNo
,DATEPART(QUARTER, @beginDate) As QuarterNo
,DATEPART(MONTH, @beginDate) As MonthNo
,DATEPART(DayOfYear, @beginDate) As DayOfYearNo
,DATEPART(Day, @beginDate) As DayNo
,DATEPART(Week, @beginDate) As WeekNo
,DATEPART(WEEKDAY, @beginDate) As WeekDayNo
Set @beginDate = DateAdd(Day, 1, @beginDate)
End
;WITH CTE
AS
(
SELECT
CalendarDate
,WeekNo
,Row_Number() OVER(PARTITION BY WeekNo ORDER BY CalendarDate ASC) As FirstDayOfWeek
,Row_Number() OVER(PARTITION BY WeekNo ORDER BY CalendarDate DESC) As LastDayOfWeek
FROM
@Calendar
)
SELECT
U.UserID
,U.Name
,WeekNo
,MAX(CASE WHEN FirstDayOfWeek = 1 THEN CalendarDate ELSE NULL END) As FirstDayOfWeek
,MAX(CASE WHEN LastDayOfWeek = 1 THEN CalendarDate ELSE NULL END) As LastDayOfWeek
FROM
@Users U
CROSS JOIN CTE
GROUP BY
U.UserID
,U.Name
,WeekNo
also i am not sure how the performance would be as i using while loop. please show me your idea with sample query.
thanks
January 30, 2015 at 8:01 pm
Below my working example,
DECLARE @users TABLE (
UserID INT
,NAME VARCHAR(50)
);
INSERT INTO @users
SELECT 1
,'Member1'
UNION ALL
SELECT 2
,'Member2'
UNION ALL
SELECT 3
,'Member3'
UNION ALL
SELECT 4
,'Member4'
UNION ALL
SELECT 5
,'Member5'
UNION ALL
SELECT 6
,'Member6';
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
DECLARE @DateStart DATE = '20150202';
DECLARE @DateEnd DATE = '20151231';
DECLARE @QtyUsers INT = (
SELECT count(*)
FROM @users
);
WITH CTE_Dates
AS (
SELECT @DateStart AS DateStart
,DATEADD(DAY, 6, @DateStart) AS DateEnd
,1 AS UserId
UNION ALL
SELECT DATEADD(DAY, 7, DateStart)
,CASE
WHEN DATEADD(DAY, 7, DateEnd) > @DateEnd
THEN @DateEnd
ELSE DATEADD(DAY, 7, DateEnd)
END
,CASE
WHEN UserId < @QtyUsers
THEN UserId + 1
ELSE 1
END
FROM CTE_Dates
WHERE DateEnd < @DateEnd
)
--select * from CTE_Dates
SELECT UPPEr(u.NAME) AS NAME
,d.DateStart
,d.DateEnd
,d.UserId
FROM CTE_Dates AS d
INNER JOIN @Users AS u ON u.UserId = d.UserId
where u.userid = 1
ORDER BY d.DateStart
Is there any way to convert this rows to columns.
sample output:
select 'MEMBER1' as Name, '2015-02-02-2015-02-08' as support1,'2015-03-16-2015-03-22' as support2,
'2015-04-27-2015-05-03' as Support3,'2015-06-08-2015-06-14' support4,'2015-07-202015-07-26' as support5,
'2015-08-31-2015-09-06' as support7,'2015-10-12-2015-10-18' as support8.
any help please
January 30, 2015 at 8:48 pm
Is there any way to convert this rows to columns.
Yep. PIVOT or use a report with a matrix
January 30, 2015 at 11:04 pm
First, don't ever use either a While Loop or a Recursive CTE that counts-up for something like this. Both are horribly slow and it's not a habit you should get into. Here's an article on why rCTEs are so bad. And, no... even though they look like it, they're not set based. They're actually RBAR on steroids.
[/http://www.sqlservercentral.com/articles/T-SQL/74118
So, where to get the numbered rows to create the date from? Rather than constantly embedding code, create this function and embed a call to it, instead.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN
;
Now, using the following "realistic" name data for your User table...
CREATE TABLE #User
(
UserID INT
,NAME VARCHAR(50)
)
;
INSERT INTO #User VALUES
(12,'Charlie')
,(2 ,'Zed')
,(8 ,'Amber')
,(4 ,'Sally')
,(15,'Steve')
,(1 ,'Sue')
;
... and a method from the following article (http://www.sqlservercentral.com/articles/T-SQL/63681/) known as a "CROSS TAB" along with the fnTally function we just made...
--===== Declare and assign the desired date range
DECLARE @DateStart DATE = '20150202'
,@DateEnd DATE = '20151231'
;
--===== Figure out how many weeks that is
DECLARE @Weeks INT = DATEDIFF(dd,@DateStart,@DateEnd)/7
;
--===== Create the pivoted report of supoort dates
WITH
cteGenWeeks AS
( --=== Generate weekly schedule along with the numeric rotation values.
SELECT UserSetNum = t.N/6+1
,UserRowNum = t.N%6+1
,WeekStart = DATEADD(dd,t.N*7 ,@DateStart)
,SupportSpan = CONVERT(CHAR(11),DATEADD(dd,t.N*7,@DateStart),113)
+ ' THRU '
+ CONVERT(CHAR(11),DATEADD(dd,t.N*7+6,@DateStart),113)
FROM dbo.fnTally(0,@Weeks)t
)
,cteEnumerateUsers AS
( --=== Enumerate the user information with rotation values.
SELECT u.UserID
,u.Name
,UserRowNum = ROW_NUMBER() OVER (ORDER BY u.Name)
FROM #User u
) --=== Join the two CTEs on the user rotation values using a CROSS TAB to pivot the ouput.
SELECT u.UserID
,u.Name
,Support1 = MAX(CASE WHEN w.UserSetNum = 1 THEN SupportSpan ELSE '' END)
,Support2 = MAX(CASE WHEN w.UserSetNum = 2 THEN SupportSpan ELSE '' END)
,Support3 = MAX(CASE WHEN w.UserSetNum = 3 THEN SupportSpan ELSE '' END)
,Support4 = MAX(CASE WHEN w.UserSetNum = 4 THEN SupportSpan ELSE '' END)
,Support5 = MAX(CASE WHEN w.UserSetNum = 5 THEN SupportSpan ELSE '' END)
,Support6 = MAX(CASE WHEN w.UserSetNum = 6 THEN SupportSpan ELSE '' END)
,Support7 = MAX(CASE WHEN w.UserSetNum = 7 THEN SupportSpan ELSE '' END)
,Support8 = MAX(CASE WHEN w.UserSetNum = 8 THEN SupportSpan ELSE '' END)
FROM cteGenWeeks w
JOIN cteEnumerateUsers u
ON w.UserRowNum = u.UserRowNum
GROUP BY u.UserID, u.Name
ORDER BY MAX(CASE WHEN w.UserSetNum = 1 THEN w.WeekStart ELSE '' END) --Sorting Magic
;
The report ends up looking like this...
UserID Name Support1 Support2 Support3 Support4 Support5 Support6 Support7 Support8
------ ------- ---------------------------- ---------------------------- ---------------------------- ---------------------------- ---------------------------- ---------------------------- ---------------------------- ----------------------------
8 Amber 02 Feb 2015 THRU 08 Feb 2015 16 Mar 2015 THRU 22 Mar 2015 27 Apr 2015 THRU 03 May 2015 08 Jun 2015 THRU 14 Jun 2015 20 Jul 2015 THRU 26 Jul 2015 31 Aug 2015 THRU 06 Sep 2015 12 Oct 2015 THRU 18 Oct 2015 23 Nov 2015 THRU 29 Nov 2015
12 Charlie 09 Feb 2015 THRU 15 Feb 2015 23 Mar 2015 THRU 29 Mar 2015 04 May 2015 THRU 10 May 2015 15 Jun 2015 THRU 21 Jun 2015 27 Jul 2015 THRU 02 Aug 2015 07 Sep 2015 THRU 13 Sep 2015 19 Oct 2015 THRU 25 Oct 2015 30 Nov 2015 THRU 06 Dec 2015
4 Sally 16 Feb 2015 THRU 22 Feb 2015 30 Mar 2015 THRU 05 Apr 2015 11 May 2015 THRU 17 May 2015 22 Jun 2015 THRU 28 Jun 2015 03 Aug 2015 THRU 09 Aug 2015 14 Sep 2015 THRU 20 Sep 2015 26 Oct 2015 THRU 01 Nov 2015 07 Dec 2015 THRU 13 Dec 2015
15 Steve 23 Feb 2015 THRU 01 Mar 2015 06 Apr 2015 THRU 12 Apr 2015 18 May 2015 THRU 24 May 2015 29 Jun 2015 THRU 05 Jul 2015 10 Aug 2015 THRU 16 Aug 2015 21 Sep 2015 THRU 27 Sep 2015 02 Nov 2015 THRU 08 Nov 2015 14 Dec 2015 THRU 20 Dec 2015
1 Sue 02 Mar 2015 THRU 08 Mar 2015 13 Apr 2015 THRU 19 Apr 2015 25 May 2015 THRU 31 May 2015 06 Jul 2015 THRU 12 Jul 2015 17 Aug 2015 THRU 23 Aug 2015 28 Sep 2015 THRU 04 Oct 2015 09 Nov 2015 THRU 15 Nov 2015 21 Dec 2015 THRU 27 Dec 2015
2 Zed 09 Mar 2015 THRU 15 Mar 2015 20 Apr 2015 THRU 26 Apr 2015 01 Jun 2015 THRU 07 Jun 2015 13 Jul 2015 THRU 19 Jul 2015 24 Aug 2015 THRU 30 Aug 2015 05 Oct 2015 THRU 11 Oct 2015 16 Nov 2015 THRU 22 Nov 2015 28 Dec 2015 THRU 03 Jan 2016
If you want to make it totally dynamic, you can do that using the method in the following article that will allow you to convert the code above in a fairly easy manner.
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2015 at 8:20 am
Hi Jeff,
I gone through each step to understand this hygienic code. Really leaned new formula's. I know about tally table concept even you advised me to learn about this in my old post. But this tally table function i didn't think about it. Really very useful and greatest code you shared.
thank you so much. Also i learned to avoid recursive CTE and while loop. your precious time on this post was most appreciated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply