Help needed in Date manipulation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed in Date manipulation

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources