Using SQL Server-2005
Here, I want to generate reports for reminders in Automobiles Industries for post follow-ups.
Reminders will generate in slabs, such as on Invoice Date
1st Reminder will be between 15-30 Days.
2nd Reminder – 105-120 Days
3rd Reminder – 195-210 Days
4th Reminder – 285-300 Days
---STORE PROCEDURE FOR 1ST FREE SERVICE REMINDER
CREATE PROCEDURE spFS1
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
select *,
dateadd(day,datediff(day,0,InvDate)+30,0) as FIRST_SERVICE_DUE_TO
from pfs_tbl
where InvDate between
@FROMDATE AND @TODATE
ORDER BY InvNo
END
---STORE PROCEDURE FOR 2ND FREE SERVICE REMINDER
CREATE PROCEDURE spFS2
@FROMDATE DATETIME,
@TODATE DATETIME
AS
BEGIN
select *,
dateadd(day,datediff(day,0,InvDate)+120,0) as SECOND_SERVICE_DUE_TO
from pfs_tbl
where InvDate between
@FROMDATE AND @TODATE
ORDER BY InvNo
END
--- how to write query in single Store procedure for using more than one DateAdd function?
Vilas GitePosted Aug 9, 2011, 12:50 AM
I will try it :)
Amit ChoudharyPosted Aug 8, 2011, 2:22 AM
you can use temp table or cursors to iterate through records which falls in the current date notification time. create a nested if else statement to check the reminder of 1st, 2nd, 3rd.. etc.
if condition is true then perform watever you want..
hope this was helpful..