Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdset
Constraint Violating Yak Guru

310 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

310 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  
 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.06 seconds. Powered By: Snitz Forums 2000