June 26, 2018 at 2:54 am
Can some one please help in writing query. My requirement is I am having 2 columns where I want to load the first column with dates continuously starting from the first day of the year to the last day. The second column should be updated with 30 days after the date of first column and if that date is Saturday or Sunday then it should get updated with the date of the next Monday.
Column1 Column2
01-01-2018 31-01-2018
02-01-2018 01-02-2018
03-01-2018 02-02-2018
04-01-2018 05-02-2018
05-01-2018 05-02-2018
06-01-2018 05-02-2018
Thanks in advance.
June 26, 2018 at 5:15 am
Use while loop to generate dates and as Thomas mentioned good to use calendar table.
Here is the query
CREATE TABLE #TEMP
(
Column1 DATE,
Column2 DATE
)
INSERT INTO #TEMP (Column1)
VALUES ('01-01-2018'),('02-01-2018'),('03-01-2018'),('04-01-2018'),('05-01-2018'),('06-01-2018')
-----Select Statement------------
SELECT Column1,
CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END AS Column2
FROM #TEMP
---UPDATE STATEMENT-------
UPDATE A
SET Column1= CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END
FROM #TEMP A
DROP TABLE #TEMP
June 26, 2018 at 5:24 am
Thom A - Tuesday, June 26, 2018 4:47 AMDo you have a calendar table already?
I don't have a calendar table. I am planning to insert dates in a sequential order first and then apply the logic
June 26, 2018 at 5:26 am
KGNH - Tuesday, June 26, 2018 5:24 AMThom A - Tuesday, June 26, 2018 4:47 AMDo you have a calendar table already?I don't have a calendar table. I am planning to insert dates in a sequential order first and then apply the logic
First things first then, create a Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/. This task becomes infinitely easier then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 5:28 am
koti.raavi - Tuesday, June 26, 2018 5:15 AMUse while loop to generate dates and as Thomas mentioned good to use calendar table.
Here is the query
CREATE TABLE #TEMP
(
Column1 DATE,
Column2 DATE
)
INSERT INTO #TEMP (Column1)
VALUES ('01-01-2018'),('02-01-2018'),('03-01-2018'),('04-01-2018'),('05-01-2018'),('06-01-2018')
-----Select Statement------------
SELECT Column1,
CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END AS Column2
FROM #TEMP
---UPDATE STATEMENT-------
UPDATE A
SET Column1= CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END
FROM #TEMP A
DROP TABLE #TEMP
Thanks for your reply. But a small correction in my post. Actually while loading 2nd column it has to check the weekday with that day it self and if it comes to Saturday or sunday it should take the coming Monday.
June 26, 2018 at 5:31 am
Thom A - Tuesday, June 26, 2018 5:26 AMKGNH - Tuesday, June 26, 2018 5:24 AMThom A - Tuesday, June 26, 2018 4:47 AMDo you have a calendar table already?I don't have a calendar table. I am planning to insert dates in a sequential order first and then apply the logic
First things first then, create a Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/. This task becomes infinitely easier then.
Actually our project don't want to create an additional table since the insert will happen only once a year and no other tables will using that one.
June 26, 2018 at 5:31 am
Thom A - Tuesday, June 26, 2018 5:24 AMMisunderstood last post.
Yeah! Overlap date and month num 🙂
June 26, 2018 at 5:33 am
KGNH - Tuesday, June 26, 2018 5:31 AMThom A - Tuesday, June 26, 2018 5:26 AMKGNH - Tuesday, June 26, 2018 5:24 AMThom A - Tuesday, June 26, 2018 4:47 AMDo you have a calendar table already?I don't have a calendar table. I am planning to insert dates in a sequential order first and then apply the logic
First things first then, create a Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/. This task becomes infinitely easier then.
Actually our project don't want to create an additional table since the insert will happen only once a year and no other tables will using that one.
Why wouldn't you want a calendar table? They're useful for all sorts of tasks, not just this one. You'll probably end up using it for other problems as well.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 5:33 am
KGNH - Tuesday, June 26, 2018 5:28 AMkoti.raavi - Tuesday, June 26, 2018 5:15 AMUse while loop to generate dates and as Thomas mentioned good to use calendar table.
Here is the query
CREATE TABLE #TEMP
(
Column1 DATE,
Column2 DATE
)
INSERT INTO #TEMP (Column1)
VALUES ('01-01-2018'),('02-01-2018'),('03-01-2018'),('04-01-2018'),('05-01-2018'),('06-01-2018')
-----Select Statement------------
SELECT Column1,
CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END AS Column2
FROM #TEMP
---UPDATE STATEMENT-------
UPDATE A
SET Column1= CASE WHEN DATEPART(DW,DATEADD(DD,30,Column1))=7 THEN DATEADD(DD,32,Column1)
WHEN DATEPART(DW,DATEADD(DD,30,Column1))=1 THEN DATEADD(DD,31,Column1)
ELSE DATEADD(DD,30,Column1) END
FROM #TEMP A
DROP TABLE #TEMP
Thanks for your reply. But a small correction in my post. Actually while loading 2nd column it has to check the weekday with that day it self and if it comes to Saturday or sunday it should take the coming Monday.
how u loading data in table?
June 26, 2018 at 5:34 am
Thom A - Tuesday, June 26, 2018 5:33 AMKGNH - Tuesday, June 26, 2018 5:31 AMThom A - Tuesday, June 26, 2018 5:26 AMKGNH - Tuesday, June 26, 2018 5:24 AMThom A - Tuesday, June 26, 2018 4:47 AMDo you have a calendar table already?I don't have a calendar table. I am planning to insert dates in a sequential order first and then apply the logic
First things first then, create a Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/. This task becomes infinitely easier then.
Actually our project don't want to create an additional table since the insert will happen only once a year and no other tables will using that one.
Why wouldn't you want a calendar table? They're useful for all sorts of tasks, not just this one. You'll probably end up using it for other problems as well.
That's true but we are using this logic only in one table and hence not preferred to have additional table.
June 26, 2018 at 5:48 am
This 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;
GO
DECLARE @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~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2018 at 6:00 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);
The formatting problems can get fixed if a monospaced font is used such as courier new 🙂
June 26, 2018 at 6:04 am
george_at_sql - Tuesday, June 26, 2018 6:00 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);The formatting problems can get fixed if a monospaced font is used such as courier new 🙂
That text is monospaced. The problem is SSC change 2 spaces (or more) into 1, ruining any tabbing. It's not a font choice issue; especially when i'm pasting directly from SSMS (which uses a fixed width font).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply