could you please help, i want to update 3days before and after holidaydate samed
-
could you please help, i want to update holidayAll column with same Holidaydate as 3days before and 3 days after holidaydate need to update same date in holidayAll.
Below is the example of sample source data, and expected data is as below higligted.
sample script:
CREATE TABLE [dbo].[festivaldates](
[createdate] [varchar](50) NULL,
[holiday] [varchar](50) NULL,
[holidaydate] [varchar](50) NULL,
[holidayall] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO VALUES ('2024-11-17','','','2024-11-17')
INSERT INTO VALUES ('2024-11-18','Diwali','2024-11-18','2024-11-18')
INSERT INTO VALUES ('2024-11-19','','','2024-11-19')
-
how about
go
Declare @Festivaldates TABLE
([createdate] date NULL
, [holiday] varchar(50) NULL
, [holidaydate] date NULL
, [holidayall] date NULL
)
;
INSERT INTO @Festivaldates VALUES ('2024-11-11','',NULL,'2024-11-11')
INSERT INTO @Festivaldates VALUES ('2024-11-12','',NULL,'2024-11-12')
INSERT INTO @Festivaldates VALUES ('2024-11-13','',NULL,'2024-11-13')
INSERT INTO @Festivaldates VALUES ('2024-11-14','',NULL,'2024-11-14')
INSERT INTO @Festivaldates VALUES ('2024-11-15','',NULL,'2024-11-15')
INSERT INTO @Festivaldates VALUES ('2024-11-16','',NULL,'2024-11-16')
INSERT INTO @Festivaldates VALUES ('2024-11-17','',NULL,'2024-11-17')
INSERT INTO @Festivaldates VALUES ('2024-11-18','Diwali','2024-11-18','2024-11-18')
INSERT INTO @Festivaldates VALUES ('2024-11-19','',NULL,'2024-11-19')
INSERT INTO @Festivaldates VALUES ('2024-11-20','',NULL,'2024-11-20')
INSERT INTO @Festivaldates VALUES ('2024-11-21','',NULL,'2024-11-21')
INSERT INTO @Festivaldates VALUES ('2024-11-22','',NULL,'2024-11-22')
INSERT INTO @Festivaldates VALUES ('2024-11-23','',NULL,'2024-11-23')
INSERT INTO @Festivaldates VALUES ('2024-12-11','',NULL,'2024-12-11')
INSERT INTO @Festivaldates VALUES ('2024-12-12','',NULL,'2024-12-12')
INSERT INTO @Festivaldates VALUES ('2024-12-13','',NULL,'2024-12-13')
INSERT INTO @Festivaldates VALUES ('2024-12-14','',NULL,'2024-12-14')
INSERT INTO @Festivaldates VALUES ('2024-12-15','',NULL,'2024-12-15')
INSERT INTO @Festivaldates VALUES ('2024-12-16','',NULL,'2024-12-16')
INSERT INTO @Festivaldates VALUES ('2024-12-17','',NULL,'2024-12-17')
INSERT INTO @Festivaldates VALUES ('2024-12-18','Holy','2024-12-18','2024-12-18')
INSERT INTO @Festivaldates VALUES ('2024-12-19','',NULL,'2024-12-19')
INSERT INTO @Festivaldates VALUES ('2024-12-20','',NULL,'2024-12-20')
INSERT INTO @Festivaldates VALUES ('2024-12-21','',NULL,'2024-12-21')
INSERT INTO @Festivaldates VALUES ('2024-12-22','',NULL,'2024-12-22')
INSERT INTO @Festivaldates VALUES ('2024-12-23','',NULL,'2024-12-23')
; with cteHolidays as (
Select *
from @Festivaldates
where holidaydate is not null
)
Select *
-- update F set holidayall = H.holidaydate
from @Festivaldates F
left join cteHolidays H
on F.createdate between dateadd( dd, -3, H.holidaydate ) and dateadd( dd, 3, H.holidaydate )
order by 1;
GOJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you, so much!! you are the Master.. Appreciate !!!!!
-
This solution gives similar results, but performs faster, because there are fewer reads.
SELECT f.createdate, f.holiday, f.holidaydate, f.holidayall, h.holiday, h.holidaydate
, MAX(h.holiday) OVER(ORDER BY f.holidayall ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS holiday_range
, MAX(h.holidaydate) OVER(ORDER BY f.holidayall ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS holidaydate_range
FROM @Festivaldates AS f
OUTER APPLY
(
SELECT f.holiday, f.holidaydate
WHERE f.holiday > ''
) AS h
ORDER BY f.holidayallDrew
PS: You don't say what you want to do when two different holidays overlap. For example, Passover and Easter. Johan's solution will produce two rows (one for each holiday) where they overlap. Mine could potentially take the name of one holiday and the date of the other, but there is a way to overcome that depending on how you want to handle this situation.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply