Help in writing query for update

Question

Post reply

Help in writing query for update

KGNH

Ten Centuries

Points: 1087
More actions
June 26, 2018 at 2:54 am

#370315

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.

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Do you have a calendar table already?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

Use while loop to generate dates and as Thomas mentioned good to use calendar table.
Here is the query

Thom A SSC Guru Points: 99130 More actions · Answer 3

Misunderstood last post.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

KGNH Ten Centuries Points: 1087 More actions · Answer 4

Thom A - Tuesday, June 26, 2018 4:47 AM
Do 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

Thom A SSC Guru Points: 99130 More actions · Answer 5

KGNH - Tuesday, June 26, 2018 5:24 AM
Thom A - Tuesday, June 26, 2018 4:47 AM
Do 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

KGNH Ten Centuries Points: 1087 More actions · Answer 6

koti.raavi - Tuesday, June 26, 2018 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

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.

KGNH Ten Centuries Points: 1087 More actions · Answer 7

Thom A - Tuesday, June 26, 2018 5:26 AM
KGNH - Tuesday, June 26, 2018 5:24 AM
Thom A - Tuesday, June 26, 2018 4:47 AM
Do 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.

koti.raavi SSCrazy Points: 2756 More actions · Answer 8

Thom A - Tuesday, June 26, 2018 5:24 AM
Misunderstood last post.

Yeah! Overlap date and month num 🙂

Thom A SSC Guru Points: 99130 More actions · Answer 9

KGNH - Tuesday, June 26, 2018 5:31 AM
Thom A - Tuesday, June 26, 2018 5:26 AM
KGNH - Tuesday, June 26, 2018 5:24 AM
Thom A - Tuesday, June 26, 2018 4:47 AM
Do 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

koti.raavi SSCrazy Points: 2756 More actions · Answer 10

KGNH - Tuesday, June 26, 2018 5:28 AM
koti.raavi - Tuesday, June 26, 2018 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
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?

KGNH Ten Centuries Points: 1087 More actions · Answer 11

Thom A - Tuesday, June 26, 2018 5:33 AM
KGNH - Tuesday, June 26, 2018 5:31 AM
Thom A - Tuesday, June 26, 2018 5:26 AM
KGNH - Tuesday, June 26, 2018 5:24 AM
Thom A - Tuesday, June 26, 2018 4:47 AM
Do 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.

Thom A SSC Guru Points: 99130 More actions · Answer 12

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

george_at_sql SSCommitted Points: 1967 More actions · Answer 13

Thom A - Tuesday, June 26, 2018 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);

The formatting problems can get fixed if a monospaced font is used such as courier new 🙂

Thom A SSC Guru Points: 99130 More actions · Answer 14

george_at_sql - Tuesday, June 26, 2018 6:00 AM
Thom A - Tuesday, June 26, 2018 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);
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