Last Weekday n In T-SQL

T-SQL query:

SELECT DATEADD(D, n-DATEPART(DW, GETDATE()), DATEDIFF(DD, 0, GETDATE()))

can be used to get last weekday n in T-SQL. Following is the value of parameter n:

At Least Hours Ago	Mon	Tue	Wed	Thu	Fri	Sat	Sun
0	2	3	-3	-2	-1	0	1
24	2	-4	-3	-2	-1	0	1
48	-5	-4	-3	-2	-1	0	1

The result is that weekday at 0:00:00 timestamp.

However, if this is to be used as parameter in SSRS, it would be:

=DATEADD("D", n-WEEKDAY(TODAY())), TODAY())

This is because the syntax are different in parameter of SSRS and T-SQL.

Many people would prefer predict in T-SQL as follows:

WHERE PERIOD >= STARTDATE AND PERIOD < ENDDATE

However, in day-to-day life, people prefer both dates be to inclusive. In this case, the predict should be:

WHERE PERIOD >= STARTDATE AND PERIOD <= ENDDATE

Therefore, the n parameter should be changed accordingly.


http://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60289.aspx

Schedule a Job in SQL Server Management Studio

To create and attach a schedule to a job

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6. For Schedule Type, select one of the following:

* Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
* Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
* Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
* Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

To attach a schedule to a job

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3. Select the Schedules page, and then click Pick.
4. Select the schedule that you want to attach, and then click OK.
5. In the Job Properties dialog box, double-click the attached schedule.
6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7. In the Job Properties dialog box, click OK.

Where is SQL Server Agent? By default, it is tuned off.

To start the SQL Server Agent service

1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In SQL Server Configuration Manager, expand Services, and then click SQL Agent.
3.In the results pane, right-click any instance, and then click Start. A green arrow on the icon next to the SQL Server Agent and on the toolbar indicates that SQL Server Agent started successfully.
4.Click OK.

Can't do it? You might be using express version. Only in full version you can turn it on.


http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191454.aspx