using DateDiff to run weekly or bimonthly reports

[rds80]

I have to run a report every thursday. Is the best way to do this is

SELECT FIELDS
FROM table
WHERE DATEDIFF(d, N.NoteDate, getDate()) = 7

Also, I have to run anoter report report bi-monthly. Could I just do:

WHERE DATEDIFF(d, N.NoteDate, getDate()) = 15

 

[harebrain]

Why not use SQL Server Agent to schedule your reporting jobs?

 

[JohnBates]

harebrain is correct. In Enterprise Manager, just expand the Management folder and create a new job. You can schedule the bi-monthly report to run on the 1st day of the month. Add a second schedule for the 15th day of the month.
These instructions are for SQL Server 2000. Best to be connected as 'sa' so you have the necessary permissions to create a Job.

hope this helps you get started.
John

 

[rds80]

i'm passing a sql query into ActiveReports to generate the records.

For one report I have to show all the new results going back 7 days and for another report I have to show all the new results bi-monthly.

dont think is a sql server agent issue.

 

[dbomrrsm]

SELECT FIELDS
FROM table
WHERE NoteDate > (getDate() - 7) 

SELECT FIELDS
FROM table
WHERE NoteDate > (getDate() - 14)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[rds80]

doesn't the datediff do the same thing?

 

[dbomrrsm]

Yep - if you want to be exact and only have data that wasnt included in previous eports you should have a control table that inserts te maximum datetime fo thereport when run - then when the reports are run the following week you can do:

SELECT FIELDS
FROM table
WHERE NoteDate > (select date_field from control_table where field_name = 'Name_of_Report')

This assumes you insert into the control table field_name the name of the report wth the date_field being the maximum date of he data included in the previous run of the report.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]