June 26, 2018 at 6:04 am
Thank you all for your responses. I got my result from your suggestions.
June 26, 2018 at 6:06 am
Thom A - Tuesday, June 26, 2018 5:48 AMThis is one solution, however, I'm not a fan of it. A calendar table would be far better. Formatting courtesy of SSC's annoying text editor:
USE Sandbox;
GODECLARE @StartDate date = '20180101';
--This is a resursice CTE, however, the way it works is technically RBAR
WITH
Dates AS
(SELECT @StartDate AS CalendarDate
UNION ALL
SELECT DATEADD(DAY, 1, CalendarDate)
FROM Dates
WHERE DATEADD(DAY, 1, CalendarDate) < DATEADD(YEAR, 1, @StartDate))
--Relies on your language being English based
SELECT D.CalendarDate,
CASE DATENAME(WEEKDAY, V.NextDate)
WHEN 'Saturday' THEN DATEADD(DAY, 2, V.NextDate)
WHEN 'Sunday' THEN DATEADD(DAY, 1, V.NextDate)
ELSE V.NextDate
END AS NextDate
FROM Dates D
CROSS APPLY (VALUES (DATEADD(DAY, 3, D.CalendarDate))) V (NextDate)
OPTION (MAXRECURSION 366);
I don't see adding 30days as per requirement.
June 26, 2018 at 6:13 am
koti.raavi - Tuesday, June 26, 2018 6:06 AMThom A - Tuesday, June 26, 2018 5:48 AMThis is one solution, however, I'm not a fan of it. A calendar table would be far better. Formatting courtesy of SSC's annoying text editor:
USE Sandbox;
GODECLARE @StartDate date = '20180101';
--This is a resursice CTE, however, the way it works is technically RBAR
WITH
Dates AS
(SELECT @StartDate AS CalendarDate
UNION ALL
SELECT DATEADD(DAY, 1, CalendarDate)
FROM Dates
WHERE DATEADD(DAY, 1, CalendarDate) < DATEADD(YEAR, 1, @StartDate))
--Relies on your language being English based
SELECT D.CalendarDate,
CASE DATENAME(WEEKDAY, V.NextDate)
WHEN 'Saturday' THEN DATEADD(DAY, 2, V.NextDate)
WHEN 'Sunday' THEN DATEADD(DAY, 1, V.NextDate)
ELSE V.NextDate
END AS NextDate
FROM Dates D
CROSS APPLY (VALUES (DATEADD(DAY, 3, D.CalendarDate))) V (NextDate)
OPTION (MAXRECURSION 366);I don't see adding 30days as per requirement.
I guess we need to replace "3" with "30" in Cross Apply, if I'm not wrong.
June 26, 2018 at 6:32 am
koti.raavi - Tuesday, June 26, 2018 6:13 AMkoti.raavi - Tuesday, June 26, 2018 6:06 AMtype your messageI don't see adding 30days as per requirement.I guess we need to replace "3" with "30" in Cross Apply, if I'm not wrong.
Yes, I'd misread the OP's requirement, an easy enough fix, however. If you don't understand the SQL to make the change though, you shouldn't be using it. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 7:14 am
Thom A - Tuesday, June 26, 2018 5:48 AMThis is one solution, however, I'm not a fan of it. A calendar table would be far better. Formatting courtesy of SSC's annoying text editor:
USE Sandbox;
GODECLARE @StartDate date = '20180101';
--This is a resursice CTE, however, the way it works is technically RBAR
WITH
Dates AS
(SELECT @StartDate AS CalendarDate
UNION ALL
SELECT DATEADD(DAY, 1, CalendarDate)
FROM Dates
WHERE DATEADD(DAY, 1, CalendarDate) < DATEADD(YEAR, 1, @StartDate))
--Relies on your language being English based
SELECT D.CalendarDate,
CASE DATENAME(WEEKDAY, V.NextDate)
WHEN 'Saturday' THEN DATEADD(DAY, 2, V.NextDate)
WHEN 'Sunday' THEN DATEADD(DAY, 1, V.NextDate)
ELSE V.NextDate
END AS NextDate
FROM Dates D
CROSS APPLY (VALUES (DATEADD(DAY, 3, D.CalendarDate))) V (NextDate)
OPTION (MAXRECURSION 366);
@thom-2, you've been around long enough to know that you want to use a Tally table to build your calendar table rather than a recursive CTE.
WITH CTE AS
(
SELECT n
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) t(n)
)
, tally AS
(
SELECT ROW_NUMBER()(OVER(ORDER BY @@VERSION)) AS n
FROM CTE AS A, CTE AS B, CTE AS C
)
SELECT DATEADD(DAY, n, @StartDate)
FROM tally
Drew
PS: Untested, because I still don't have a SQL environment set up here.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2018 at 7:30 am
drew.allen - Tuesday, June 26, 2018 7:14 AM@thom-2, you've been around long enough to know that you want to use a Tally table to build your calendar table rather than a recursive CTE.
WITH CTE AS
(
SELECT n
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) t(n)
)
, tally AS
(
SELECT ROW_NUMBER()(OVER(ORDER BY @@VERSION)) AS n
FROM CTE AS A, CTE AS B, CTE AS C
)
SELECT DATEADD(DAY, n, @StartDate)
FROM tally
Drew
PS: Untested, because I still don't have a SQL environment set up here.
Exactly why my comment is there in my SQL; my method was RBAR. Although, if we're really talking semantics, what you have there isn't a Tally Table either. 😀
Considering, however, the OP has no interest in making a Calendar Table, I doubt they have any interest in making a true Tally Table either. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 8:09 am
Thom A - Tuesday, June 26, 2018 7:30 AMdrew.allen - Tuesday, June 26, 2018 7:14 AM@thom-2, you've been around long enough to know that you want to use a Tally table to build your calendar table rather than a recursive CTE.
WITH CTE AS
(
SELECT n
FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) t(n)
)
, tally AS
(
SELECT ROW_NUMBER()(OVER(ORDER BY @@VERSION)) AS n
FROM CTE AS A, CTE AS B, CTE AS C
)
SELECT DATEADD(DAY, n, @StartDate)
FROM tally
Drew
PS: Untested, because I still don't have a SQL environment set up here.
Exactly why my comment is there in my SQL; my method was RBAR. Although, if we're really talking semantics, what you have there isn't a Tally Table either. 😀
Considering, however, the OP has no interest in making a Calendar Table, I doubt they have any interest in making a true Tally Table either. 🙁
Yes, but why did you give a RBAR solution in the first place?
Is a virtual table not still a table? It performs exactly the same logical function as a "true" tally table, but is actually faster, because it requires no reads.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2018 at 8:22 am
drew.allen - Tuesday, June 26, 2018 8:09 AMYes, but why did you give a RBAR solution in the first place?Is a virtual table not still a table? It performs exactly the same logical function as a "true" tally table, but is actually faster, because it requires no reads.
Drew
Honestly, probably a lapse in judgement, I clearly knew better when i wrote the comment (but chose not to do anything about it).
It's only Tuesday and it's been a long week already. Doesn't help that a company I'm working with at the moment is under the belief that Email address is a unique identifier. They can't for the life of them figure out why Customer data inserts are failing and why things are going really wrong when a customer wants to change their email address when they don't have any kind of cascading enabled. /headdesk
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 11:31 am
KGNH - Tuesday, June 26, 2018 6:04 AMThank you all for your responses. I got my result from your suggestions.
Hopefully, it doesn't actually involve a WHILE loop. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2018 at 7:54 am
create table temp1 (column1 date,column2 date)
declare @startDate date
declare @endDate date
set @startDate = '2018-01-01'
set @endDate = '2018-12-31'
while (@startDate <= @endDate)
begin
insert into temp1 values(@startDate,null)
set @startDate = dateadd(dd,1,@startDate)
end
Update temp1 set column2 = case when datepart(weekday,dateadd(dd,30,column1)) = 7 then dateadd(dd,32,column1)
when datepart(weekday,dateadd(dd,30,column1)) = 1 then dateadd(dd,31,column1)
else dateadd(dd,30,column1) end
select * from temp1
June 27, 2018 at 8:14 am
Sudhakar Ramakrishnan - Wednesday, June 27, 2018 7:53 AMcreate table temp1 (column1 date,column2 date)
declare @startDate date
declare @endDate date
set @startDate = '2018-01-01'
set @endDate = '2018-12-31'
while (@startDate <= @endDate)
begin
insert into temp1 values(@startDate,null)
set @startDate = dateadd(dd,1,@startDate)
end
Update temp1 set column2 = case when datepart(weekday,dateadd(dd,30,column1)) = 7 then dateadd(dd,32,column1)
when datepart(weekday,dateadd(dd,30,column1)) = 1 then dateadd(dd,31,column1)
else dateadd(dd,30,column1) end
select * from temp1
That is a WHILE loop; exactly what we suggested the OP avoids.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 27, 2018 at 8:28 am
Thom A - Wednesday, June 27, 2018 8:14 AMSudhakar Ramakrishnan - Wednesday, June 27, 2018 7:53 AMcreate table temp1 (column1 date,column2 date)
declare @startDate date
declare @endDate date
set @startDate = '2018-01-01'
set @endDate = '2018-12-31'
while (@startDate <= @endDate)
begin
insert into temp1 values(@startDate,null)
set @startDate = dateadd(dd,1,@startDate)
end
Update temp1 set column2 = case when datepart(weekday,dateadd(dd,30,column1)) = 7 then dateadd(dd,32,column1)
when datepart(weekday,dateadd(dd,30,column1)) = 1 then dateadd(dd,31,column1)
else dateadd(dd,30,column1) end
select * from temp1
That is a WHILE loop; exactly what we suggested the OP avoids.
Furthermore, you are doing an INSERT followed by an UPDATE when you can just as easily do the calculations for the second column as part of the original INSERT.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply