SP NEEDS CHANGE - SQL Server Forums

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)

SP NEEDS CHANGE

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2014-03-06 : 22:42:19

Hi Patrons,

I am unable to find how to change the Code in the lines

- EXPECTED CHANGE HERE 1

TOP 1 then date would be tomorrow

Here I am expecting the code when TOP 1 is changed to TOP 2 then I will be getting the Date to go forward 2 days.

- EXPECTED CHANGE HERE 2

Here I am expecting not only changing date to 2 days ahead but it should also change date on checking if it is weekend date like falling on 'FRIDAY' then it should add 2 more days

Can anyone please suggest as to how this can be changed below of Stored Procedure where I have COMMENTED as EXPECTED CHANGE HERE 1 and EXPECTED CHANGE HERE 2.

Many thanks for your expertise solution.

USE [DBREM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_CR8DBREM]

AS
DECLARE @dnxtapptdt1 nvarchar(50),
@dnxtapptdt2 nvarchar(50),
--@cSQL nvarchar(2000)--,
--@ServerName NVARCHAR(50)
BEGIN

--Checks to see if it is a public holiday as it is scheduled to run only Monday to Friday
--Only runs the process when it isn't a public holiday
--Made this change so that when it runs automatically, the CSV file is only created on a
-- day that isn't a public holiday or weekend.
if (Select Convert(Int, Is_PublicHoliday)
From dbo.NxtApptDtLkup
Where Convert(Varchar,LkUpdt,112) = Convert(Varchar,getdate(),112)) = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- E X P E C T I N G C H A N G E H E R E 1

-- Set the waitlist date and calculate the number of days in the waitlist date's month
SET @dnxtapptdt1 = (Select top 1 CONVERT(varchar,LkUpdt,121) AS LkUpdt1
From NxtApptDtLkup
Where LkUpdt > GetDate() and Is_WeekEnd = 0 -- Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LkUpdt)

-- E X P E C T I N G C H A N G E H E R E 2

SET @dnxtapptdt2 = (Select Case When Upper(DateName(weekday, GetDate())) = 'FRIDAY' Then
(Select top 1 CONVERT(varchar,LkUpdt,121)
From (Select top 2 CONVERT(varchar,LkUpdt,121) AS LkUpdt
From NxtApptDtLkup
Where LkUpdt > GetDate() and Is_WeekEnd = 0 and Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LkUpdt) As NewApptDtLkup
Order by LkUpdt Desc)
Else
(Select top 1 CONVERT(varchar,LkUpdt,121)
From (Select top 2 CONVERT(varchar,LkUpdt,121) AS LkUpdt
From tblNextAppointmentDateLookup
Where LkUpdt > GetDate() and Is_WeekEnd = 0 and Is_PublicHoliday = 0 --Not a Weekend or Public Holiday
Order by LkUpdt) As LkUpdtNewApptDtLkup
Order by LkUpdt) End As LkUpdt2)

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2014-03-07 : 03:51:29

Can anyone please help me to address the required change at

-EXPECTED CHANGE HERE 1

and

EXPECTED CHANGE HERE 2

as to how I can change the DATE from existing 1 day to 2 days.

Many thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources