SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP NEEDS CHANGE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdset
Constraint Violating Yak Guru

304 Posts

Posted - 03/06/2014 :  22:42:19  Show Profile  Reply with Quote
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

304 Posts

Posted - 03/07/2014 :  03:51:29  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000