|
Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web. Basically, the calendar contains an entry for every day for the next several years that has a datetime field, fields for the day of the month, month of the year, the year, the week of the year, etc. I'm trying to write a query or set of queries to return the recurring dates if the user of a web app decides to schedule something on a recurring basis much like recurring appointments in Outlook Calendar.
The following query is what I am using to attempt to return the every 3rd Sunday starting on 01/01/08.
<br />
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W<br />
FROM dbo.Calendar c <br />
WHERE c.dayname IN ('Sunday')<br />
AND c.dt >= '01/01/08' <br />
AND c.Y IN (2008, 2009) <br />
AND 0 = ((SELECT COUNT(*) FROM <br />
dbo.Calendar c2 <br />
WHERE c.dt >= c2.dt<br />
AND c.dayname = c2.dayname) % 3) <br />
ORDER BY c.dt<br />
GO
The query is returning every 3 sunday's but it's starting with Jan. 13th (the 2nd sunday) instead of Jan. 20th (the 3rd Sunday). After that, it is returning every 3rd Sunday but it is off by 1 week because of where it's starting.
What am I doing wrong? Also, what kind of modifications might I need to make to this to support the other recurring event patterns that can be found in Outlook? I've searched the web a lot for recurring events and all I can seem to find is how to create the auxillary calendar table that I created but no info on how to retrieve the dates that a recurring event should fall on.
|
|
|
|
|
T-Smooth wrote: Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web.
Please post the script for creating the Calendar table.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I generated the calendar using scripts taken from this FAQ. Search the pdf for "calendar table" and it should take you to the right FAQ question. There are scripts to add in all of the holidays as well. You first need to create a numbers table with a sufficient amount of numbers in there as well.
http://www.aspfaq.com/downloads/ASPFAQ-2006-04-18.pdf[^]
Here's the scripts if you don't want to look at the pdf. It will give you a table consisting of 30 years starting with 01/01/2000:
<br />
CREATE TABLE dbo.Numbers<br />
(<br />
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED<br />
)<br />
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 20000<br />
BEGIN<br />
INSERT dbo.Numbers DEFAULT VALUES<br />
END<br />
GO<br />
<br />
<br />
<br />
CREATE TABLE dbo.Calendar<br />
(<br />
dt SMALLDATETIME NOT NULL<br />
PRIMARY KEY CLUSTERED,<br />
isWeekday BIT,<br />
isHoliday BIT,<br />
Y SMALLINT,<br />
FY SMALLINT,<br />
Q TINYINT,<br />
M TINYINT,<br />
D TINYINT,<br />
DW TINYINT,<br />
monthname VARCHAR(9),<br />
dayname VARCHAR(9),<br />
W TINYINT<br />
)<br />
GO<br />
<br />
INSERT Calendar(dt)<br />
SELECT DATEADD(DAY, Number, '20000101')<br />
FROM dbo.Numbers<br />
WHERE Number <= 10957<br />
ORDER BY Number<br />
GO<br />
<br />
UPDATE dbo.Calendar SET<br />
isWeekday = CASE<br />
WHEN DATEPART(DW, dt) IN (1,7)<br />
THEN 0<br />
ELSE 1 END,<br />
isHoliday = 0,<br />
Y = YEAR(dt),<br />
FY = YEAR(dt),<br />
<br />
Q = CASE<br />
WHEN MONTH(dt) <= 3 THEN 1<br />
WHEN MONTH(dt) <= 6 THEN 2<br />
WHEN MONTH(dt) <= 9 THEN 3<br />
ELSE 4 END,<br />
M = MONTH(dt),<br />
D = DAY(dt),<br />
DW = DATEPART(DW, dt),<br />
monthname = DATENAME(MONTH, dt),<br />
dayname = DATENAME(DW, dt),<br />
W = DATEPART(WK, dt)<br />
GO<br />
<br />
ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL<br />
GO<br />
<br />
SET NOCOUNT ON<br />
DECLARE @dt SMALLDATETIME<br />
DECLARE @offset TINYINT<br />
SET @offset = 5<br />
DECLARE c CURSOR<br />
LOCAL STATIC READ_ONLY FOR<br />
SELECT dt FROM dbo.Calendar ORDER BY dt<br />
OPEN c<br />
FETCH NEXT FROM c INTO @dt<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF DATENAME(dw, @dt)='Sunday'<br />
AND DATEPART(DAY, @dt) <= 7<br />
AND DATENAME(MONTH, @dt) = 'April'<br />
SET @offset = 4<br />
IF DATENAME(dw, @dt)='Sunday'<br />
AND DATEPART(DAY, @dt) >= 25<br />
AND DATENAME(MONTH, @dt) = 'October'<br />
SET @offset = 5<br />
UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt<br />
FETCH NEXT FROM c INTO @dt<br />
END<br />
CLOSE c<br />
DEALLOCATE c<br />
GO<br />
<br />
CREATE FUNCTION dbo.ISOWeek<br />
(<br />
@dt SMALLDATETIME<br />
)<br />
RETURNS TINYINT<br />
AS<br />
BEGIN<br />
DECLARE @ISOweek TINYINT<br />
SET @ISOweek = DATEPART(WEEK,@dt)+1<br />
-DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104')<br />
IF @ISOweek = 0<br />
BEGIN<br />
SET @ISOweek = dbo.ISOweek<br />
(<br />
RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt))<br />
) + 1<br />
END<br />
IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28<br />
BEGIN<br />
SET @ISOweek=1<br />
END<br />
RETURN(@ISOweek)<br />
END<br />
GO<br />
<br />
UPDATE Calendar SET W = dbo.ISOWeek(dt)<br />
GO<br />
<br />
ALTER TABLE Calendar ADD HolidayDescription VARCHAR(32)<br />
GO<br />
<br />
-- New Year's Day - easy<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'New Years'' Day'<br />
WHERE M = 1<br />
AND D = 1<br />
GO<br />
<br />
-- Memorial Day - last Monday in May<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Memorial Day'<br />
FROM Calendar c1<br />
WHERE M = 5<br />
AND DW = 2<br />
AND NOT EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 5 AND DW = 2<br />
AND c2.Y = c1.Y<br />
AND c2.dt > c1.dt)<br />
GO<br />
<br />
-- Labor Day - first Monday in September<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Labor Day'<br />
FROM Calendar c1<br />
WHERE M = 9<br />
AND DW = 2<br />
AND NOT EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 9 AND DW = 2<br />
AND c2.Y = c1.Y<br />
AND c2.dt < c1.dt)<br />
GO<br />
<br />
-- Thanksgiving Thursday - 4th Thursday in November<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Thanksgiving Thursday'<br />
FROM Calendar c1<br />
WHERE M = 11<br />
AND DW = 5<br />
AND (SELECT COUNT(*) FROM Calendar c2<br />
WHERE M = 11 AND DW = 5<br />
AND c2.Y = c1.Y<br />
AND c2.dt < c1.dt) = 3<br />
GO<br />
<br />
-- Traditionally, Thanksgiving Friday, as well<br />
-- as long as you haven't pre-configured any<br />
-- other Thursday in November to be isHoliday<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Thanksgiving Friday'<br />
FROM Calendar c1<br />
WHERE M = 11<br />
AND DW = 6<br />
AND EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 11 AND DW = 5<br />
AND c2.dt = (c1.dt - 1)<br />
AND c2.Y = c1.Y<br />
AND isHoliday = 1)<br />
GO<br />
<br />
-- Veterans' Day - easy<br />
-- however do this AFTER Thanksgiving calculation<br />
-- in case it happens to fall on a Thursday<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Veterans'' Day'<br />
WHERE M = 11 AND D = 11<br />
GO<br />
<br />
-- Christmas Day - easy<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Christmas Day'<br />
WHERE M = 12<br />
AND D = 25<br />
GO<br />
<br />
CREATE FUNCTION dbo.GetEasterSunday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
DECLARE @EpactCalc INT,<br />
@PaschalDaysCalc INT,<br />
@NumOfDaysToSunday INT,<br />
@EasterMonth INT,<br />
@EasterDay INT<br />
SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30<br />
SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28)<br />
SET @NumOfDaysToSunday = @PaschalDaysCalc - (<br />
(@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7<br />
)<br />
SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44<br />
SET @EasterDay = @NumOfDaysToSunday + 28 - (<br />
31 * (@EasterMonth / 4)<br />
)<br />
RETURN<br />
(<br />
SELECT CONVERT<br />
(<br />
SMALLDATETIME,<br />
RTRIM(@Y)<br />
+ RIGHT('0'+RTRIM(@EasterMonth), 2)<br />
+ RIGHT('0'+RTRIM(@EasterDay), 2)<br />
)<br />
)<br />
END<br />
GO<br />
<br />
CREATE FUNCTION dbo.GetEasterMonday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
RETURN (SELECT dbo.GetEasterSunday(@Y) + 1)<br />
END<br />
GO<br />
CREATE FUNCTION dbo.GetGoodFriday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
RETURN (SELECT dbo.GetEasterSunday(@Y) - 2)<br />
END<br />
GO<br />
<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Good Friday'<br />
WHERE dt = dbo.GetGoodFriday(Y)<br />
GO<br />
<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Easter Monday'<br />
WHERE dt = dbo.GetEasterMonday(Y)<br />
GO<br />
|
|
|
|
|
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c
WHERE c.dayname IN ('Sunday')
AND c.dt >= '01/01/08'
AND c.Y IN (2008, 2009)
AND 2 = (SELECT COUNT(*) FROM dbo.Calendar c2
WHERE c.y = c2.y
AND c.m = c2.m
AND c2.dt < c.dt
AND c.dayname = c2.dayname)
ORDER BY c.dt
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
This only gets me the 2nd sunday of each month, not necessarily every 2 weeks from a certain start date.
|
|
|
|
|
Oh, I guess I misunderstood. Sorry.
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c,
(select top 1 dt + 14 as dt
from calendar c3
where dayname = 'Sunday' and dt >= '01/08/08' order by dt) as c2
WHERE c.dt >= c2.dt
AND c.Y IN (2008, 2009)
AND datediff(dd,c2.dt,c.dt ) % 21 = 0
ORDER BY c.dt
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Not sure why you would want to set up a table containing each date when the calculations are just as easy to write.
Have you considered adding a few more fields to your table:
1) DayNameIndexForMonth (i.e. 1st Sunday, 2nd Sunday, 3rd Sunday)
2) DayOfYear (i.e. This is the 74th day of the year)
3) Quarter (i.e. This day is in the 3rd Quarter)
This might make your queries a lot easier.
On the other hand, you can always calculate on the fly. With only 365 iterations per year, loops should execute quickly enough. Knowing that the 3rd 'Day Name' of every month will fall between the 15th and 21st day you can code it like this:
CREATE PROCEDURE RecurringDates_ThirdSunday
(
@Start DATETIME,
@End DATETIME
)
AS
DECLARE @curDt DATETIME
SET @curDt = @Start
DECLARE @t TABLE (GoodDate DATETIME NOT NULL)
WHILE @curDt <= @End
BEGIN
IF DATEPART(dw,@curDt) = 1
BEGIN
IF DATEPART(dd,@curDt) BETWEEN 15 AND 21
BEGIN
INSERT INTO @t (GoodDate) VALUES (@curDt)
END
END
SET @curDt = DATEADD(dd,1,@CurDt)
END
SELECT *
FROM @t
|
|
|
|
|
I guess he wants every third Sunday from the start date, not every third Sunday of the month. I had the same misunderstanding.
But, I like your method better than creating a table with every date between now and some arbitrary future date.
CREATE PROCEDURE RecurringDates_ThirdSunday
(
@Start DATETIME,
@End DATETIME
)
AS
DECLARE @curDt DATETIME
SET @curDt = @Start
DECLARE @t TABLE (GoodDate DATETIME NOT NULL)
WHILE DATEPART(dw,@curDt) != 1
BEGIN
SET @curDt = DATEADD(dd,1,@CurDt)
END
SET @curDt = DATEADD(dd,14,@CurDt)
WHILE @curDt <= @End
BEGIN
INSERT INTO @t (GoodDate) VALUES (@curDt)
SET @curDt = DATEADD(dd,21,@CurDt)
END
SELECT *
FROM @t
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Is there anyway to take the whole condition part of WHERE clause as a parameter of a stored procedure ?
The result I want to gain is something that would be achieved by executing this stored procedure.Although this is wrong , I want to achieve the result.Is there anyway get this result ?
CREATE PROCEDURE sp_GetList
@condition varchar(50)
AS
SELECT * FROM Table_X WHERE @condition
-- modified at 5:53 Monday 11th September, 2006
|
|
|
|
|
Yor problem is not very clear what exactly you want? Do you want to fetch the recordset on basis of some condition, if so then that is where you can use "where" clause. Something like this:
select * from emp where empid = @EID
where @EID is the parameter to that stored procedure.
Best Regards,
Apurva Kaushal
|
|
|
|
|
No No no.
I want the condition to be like a variable for example this:
condition -> (column1='x' AND column2='y' OR column3='z')
I want the stored procedure to take the aformentioned condition as a varchar and use it in WHERE clause. To take the whole condition part of WHERE CLAUSE as parameter.
Is there any way
-- modified at 5:50 Monday 11th September, 2006
|
|
|
|
|
ya sure there is a way what you can do is create your where condition in front end, send that complete condition as string to the stored procedure. In stored procedure you take one variable(where the sql statement will be stored) and append the where string and then use sp_executesql to execute the complete string. 
Best Regards,
Apurva Kaushal
|
|
|
|
|
Thank you very much for your help.Could you please show me some basic code ?
|
|
|
|
|
Here a sample code which you can use in your stored procedure taken from sqlserver help:
DECLARE @IntVariable INT<br />
DECLARE @SQLString NVARCHAR(500)<br />
DECLARE @ParmDefinition NVARCHAR(500)<br />
<br />
<br />
SET @SQLString =<br />
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'<br />
SET @ParmDefinition = N'@level tinyint'<br />
<br />
SET @IntVariable = 35<br />
EXECUTE sp_executesql @SQLString, @ParmDefinition,<br />
@level = @IntVariable<br />
<br />
SET @IntVariable = 32<br />
EXECUTE sp_executesql @SQLString, @ParmDefinition,<br />
@level = @IntVariable<br />
Hope this will serve the purpose.
Best Regards,
Apurva Kaushal
|
|
|
|
|
CREATE PROCEDURE sp_GetList
@condition varchar(50)
AS
exec('SELECT * FROM Table_X WHERE ' + @condition)
Be sure to look at this:
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi Everybody!
I am using VB6.0 & SQL Server 7.0
Following is my database Connection code
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set db = New ADODB.Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "server=SUNANDA;uid=sa;pwd=ranjita1;database=crm"
db.Open
Set rs = New ADODB.Recordset
sql = "Select * from AccountHead"
Set rs.ActiveConnection = db
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open sql, , , , adCmdText
I have placed navigation buttons on my form, but the Next & Previous buttons are not working properly.
When I first time clicks the Next button , the recordset goes to the next record but when I click it second time, the record doesn’t get changed.
When I press the previous button, the recordset points to the first record, instead of pointing to the previous record.
Here is my code for navigational buttons :
Public Sub MoveFields()
txtAcId.Text = rs("AcHeadID")
txtAcName.Text = rs("AcHeadName")
txtAcMobile.Text = rs("Mobile")
txtAcEmail.Text = rs("EmailID")
End Sub
Private Sub cmdFirst_Click()
rs.MoveFirst
MoveFields
cmdPrev.Enabled = False
cmdNext.Enabled = True
End Sub
Private Sub cmdLast_Click()
rs.MoveLast
MoveFields
cmdNext.Enabled = False
cmdPrev.Enabled = True
End Sub
Private Sub cmdNext_Click()
rs.MoveNext
If rs.EOF Then
rs.MoveLast
cmdNext.Enabled = False
End If
MoveFields
cmdPrev.Enabled = True
End Sub
Private Sub cmdRemove_Click()
rs.Delete
rs.MoveNext
If rs.EOF Then
rs.MoveLast
End If
End Sub
Private Sub cmdPrev_Click()
rs.MovePrevious
If rs.BOF Then
rs.MoveFirst
cmdPrev.Enabled = False
End If
MoveFields
cmdNext.Enabled = True
End Sub
Pls. tell me, where I am wrong.
Thanks
|
|
|
|
|
Hi
Dear Please first check where you write the code for recordset and connection.
Then try following code for your record movement
Next record
if not rs.eof then
rs.movenext()
....
end if
previous record
if not rs.bof then
rs.moveprevious()
.....
end if
If the problem is there then please post new message with you complete form code so I can sort your issue.
Regards
Tushar kothari
|
|
|
|
|
I have written the code for recordset and connection in sub main()
I am calling the procedure in each click event.
|
|
|
|
|
Hi,
I am using ASp.NET 1.1
Now when I call a stored procedure from code behind file and the stored procedure raises an error how to catch this error in code behind file
Thanks and Regards,
Uma
|
|
|
|
|
Hi
you can catch exception by following
Catch ex As SqlClient.SqlException
Tushar kothari
|
|
|
|
|
You can use output paramter, return value or as mentioned SQlexception. I prefer return value as this help me to use customize message from resource file,
|
|
|
|
|
:(Hi Everybody!
I am using VB6.0 & SQL Server 7.0
Following is my database Connection code
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set db = New ADODB.Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "server=SUNANDA;uid=sa;pwd=ranjita1;database=crm"
db.Open
Set rs = New ADODB.Recordset
sql = "Select * from AccountHead"
Set rs.ActiveConnection = db
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open sql, , , , adCmdText
I have placed buttons on the form to Add, Delete, Edit and Update the records of the table. But in the click event of the Update button, the recordset is not supporting the Edit method. The only method it is showing in (VBA) the dropdown list is EditMode.
How will I then Edit the records?
Pls. help me out.
Thankyou.
|
|
|
|
|
Hi
Dear please concentrate on your following code
rs.CursorType = adOpenStatic
As you had open the cursor in Static mode there is no edit permission for this record set.
You gave to open the recordset in non static mode.
Regards
Tushar kothari
|
|
|
|
|
Hi! Tushar
I have tried with
rs.CursorType=adOpenDynamic
But of no use.
Pls. suggest.
regards
ranjita
|
|
|
|
|
Hi
If you want just to update the record in recordset then you have to set the fileds values of rs then call rs.update
If this method is not wirking the please relpy back then I will try some odd solution for you.
Regards
Tushar kothari
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
