| Author |
Topic |
|
sqllover
Constraint Violating Yak Guru
338 Posts |
 Posted - 2015-01-30 : 17:19:45
|
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
sample row wise data :
2/2/2015 - 2/8/2015 member1
2/9/2015 - 2/15/2015 member2
2/16/2015 - 2/22/2015 member3
2/23/2015-3/01/2015 member4
3/02/2015 - 3/08/2015 member5
3/09/2015 - 3/15/2015 member6
3/16/2015 - 3/22/2015 member1
3/23/2015 - 3/29/2015 member2
.
.
.
.
.
also i need this data as column wise
any sample query please |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2015-02-02 : 14:53:58
|
This is not the answer but it might help you
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';
with dates (Mon, Sun) AS (
select DATEADD(day, 7*(T.Nval-1), '2/2/2015'), DATEADD(day, 6*T.Nval, '2/2/2015')
from Temp_DJJ.dbo.NumberTable T
WHERE DATEADD(day, 7*T.Nval, '2/2/2015') < '12/31/2015'
)
select U.Name, D.Mon, D.Sun
from dates D
cross apply @users U
;
djj |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-02-03 : 05:29:47
|
[code]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 @StartDate DATETIME = '20150202',
@EndDate DATETIME = '20151231',
@Items INT = (SELECT COUNT(*) FROM @Users);
-- SwePeso
WITH cteDates(UserID, StartDate, EndDate)
AS (
SELECT 0 AS UserID,
DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101') AS StartDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000107') AS EndDate
UNION ALL
SELECT CASE
WHEN UserID >= @Items - 1 THEN 0
ELSE d.UserID + 1
END AS UserID,
DATEADD(DAY, 7, d.StartDate) AS StartDate,
DATEADD(DAY, 7, d.EndDate) AS EndDate
FROM cteDates AS d
WHERE DATEADD(DAY, 7, d.StartDate) < @EndDate
)
SELECT d.StartDate,
CASE
WHEN d.EndDate > @EndDate THEN @EndDate
ELSE d.EndDate
END AS EndDate,
u.Name
FROM cteDates AS d
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY UserID) - 1 AS UserID,
Name
FROM @Users
) AS u ON u.UserID = d.UserID
ORDER BY d.StartDate;[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2015-02-03 : 07:28:34
|
Hi Peso,
Thank you for your nice sample. worked. |
|
|
|
|
|
|
Help needed in Date manipulation