Scheduling - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Scheduling

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-09-04 : 12:55:54

I have 2 tables below. Need some help with the SQL. Basically if I have a report that runs once a month on the 1st Monday. Might need a field or 2.

tblReportScheduleFormat
ID - INT
StartDate DATE
EndDate - DATE
Monday - BIT
Tuesday - BIT
Wednesday - BIT
Thursday - BIT
Friday – BIT
FrequencyID - FK INT
Data - VARCHAR(50)
Record Example
1,09/04/2013, True, False, False, False, False,3,’DATA’

tblScheuduleFrequency
ID – INT
NAME – VARCHAR(50)
Data:
1, Daily
2, Weekly
3, Monthly

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-04 : 12:57:56

quote:
Originally posted by helixpoint

I have 2 tables below. Need some help with the SQL. Basically if I have a report that runs once a month on the 1st Monday. Might need a field or 2.

tblReportScheduleFormat
ID - INT
StartDate DATE
EndDate - DATE
Monday - BIT
Tuesday - BIT
Wednesday - BIT
Thursday - BIT
Friday – BIT
FrequencyID - FK INT
Data - VARCHAR(50)
Record Example
1,09/04/2013, True, False, False, False, False,3,’DATA’

tblScheuduleFrequency
ID – INT
NAME – VARCHAR(50)
Data:
1, Daily
2, Weekly
3, Monthly

Dave
Helixpoint Web Development
http://www.helixpoint.com

Is there a question?

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-09-04 : 13:35:44

I am not sure how to do this SQL

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-04 : 14:00:48

Do what?

Here are some links that can help you form your question so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-04 : 14:22:24

At a minimum, you need a DayNumber column in ReportScheduleFormat to indicate on which relative day of the month a "MONTHLY" (or "QUARTERLY" or "YEARLY") job should run. For more sophisticated scheduling options (first/last Monday of month, etc.), see msdb..sysschedules.

Then, to determine which job(s) need to run on a given day, something like this:



DECLARE @date_to_check datetime
SET @date_to_check = GETDATE()


--DECLARE csrReportsToRunOnThisDate CURSOR FAST_FORWARD FOR
SELECT ID
FROM dbo.tblReportScheduleFormat rsf
INNER JOIN dbo.tblScheduleFrequency sf ON
    sf.ID = rsf.FrequencyID
WHERE
    (rsf.FrequencyID = 3 AND rsf.DayNumber = DAY(@date_to_check)) OR
    (rsf.FrequencyID IN (1, 2) AND (
        (DATEDIFF(DAY, 0, @date_to_check) = 0 AND Monday = 1) OR
        (DATEDIFF(DAY, 0, @date_to_check) = 1 AND Tuesday = 1) OR
        (DATEDIFF(DAY, 0, @date_to_check) = 2 AND Wednesday = 1) OR
        (DATEDIFF(DAY, 0, @date_to_check) = 3 AND Thursday = 1) OR
        (DATEDIFF(DAY, 0, @date_to_check) = 4 AND Friday = 1)))

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-09-04 : 15:26:42

HEy Scott. Thankyou for the help. I am not sure what this part is......AND rsf.DayNumber

Dave
Helixpoint Web Development
http://www.helixpoint.com

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-04 : 15:53:54

That's a column I added to the table to be used to specify on which day of the month a "MONTHLY" report is supposed to be run. Otherwise, how do you know when to run a "MONTHLY" report?

1,09/04/2013, False, False, False, False, False,3,’DATA’,17
--run this "MONTHLY" report on the 17th of the month

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-09-04 : 15:59:26

ahh

Dave
Helixpoint Web Development
http://www.helixpoint.com

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-04 : 18:31:25

As I noted above, for more complex MONTHLY scheduling -- for example, the first Monday of every month -- you may need additional column(s). The table msdb..sysschedules has one design to handle more complex scheduling (see "sysschedules" in Books Online).

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-09-05 : 11:42:00

Ok Scott. You gave me some great direction. Here is what I have so far
If the recurrance is 2 and the frequency is 3. It is every 2 months.

CREATE TABLE [dbo].[tblReportScheduleFormat](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmailId] [int] NOT NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[Data] [varchar](max) NOT NULL,
[Sunday] [bit] NOT NULL,
[Monday] [bit] NOT NULL,
[Tuesday] [bit] NOT NULL,
[Wednesday] [bit] NOT NULL,
[Thursday] [bit] NOT NULL,
[Friday] [bit] NOT NULL,
[Saturday] [bit] NOT NULL,
[DayNumber] [int] NULL,
[FrequencyId] [int] NOT NULL,
[Recurrence] [int] NOT NULL,
CONSTRAINT [PK_tblReportScheduleFormat] PRIMARY KEY CLUSTERED

4,1,2013-09-03,NULL,Blaaaa,0,0,0,0,1,0,0,NULL,1,2,PDF,blaa
6,1,2013-09-04,NULL,blaaa,0,0,0,0,1,0,0,NULL,2,3,PDF,blaa
8,2,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,5,3,2,PDF,Blaa
10,3,2013-09-05,NULL,Blaaa,0,0,0,0,0,0,0,NULL,4,1,PDF,Blaa

CREATE TABLE [dbo].[tblScheuduleFrequency](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_tblScheuduleFrequency] PRIMARY KEY CLUSTERED

1,Daily
2,Weekly
3,Monthly
4,Yearly

DECLARE @date_to_check datetime
SET @date_to_check = GETDATE()
--SET @date_to_check = '2013-09-02'
SELECT rsf.ID, rsf.ReportFormat, rsf.ReportTemplate

FROM tblReportScheduleFormat rsf
INNER JOIN tblScheuduleFrequency sf ON
sf.ID = rsf.FrequencyID
WHERE
@date_to_check between rsf.StartDate and Coalesce(rsf.EndDate, @date_to_check) AND
(rsf.FrequencyID = 3 AND rsf.DayNumber = DAY(@date_to_check)) OR
(rsf.FrequencyID = 2 AND (
(DATEPART(weekday, @date_to_check) = 1 AND Sunday = 1) OR
(DATEPART(weekday, @date_to_check) = 2 AND Monday = 1) OR
(DATEPART(weekday, @date_to_check) = 3 AND Tuesday = 1) OR
(DATEPART(weekday, @date_to_check) = 4 AND Wednesday = 1) OR
(DATEPART(weekday, @date_to_check) = 5 AND Thursday = 1) OR
(DATEPART(weekday, @date_to_check) = 6 AND Friday = 1) OR
(DATEPART(weekday, @date_to_check) = 7 AND Saturday = 1))) OR
(rsf.FrequencyID = 4) AND
(DATEPART(MONTH, @date_to_check) = DATEPART(MONTH, rsf.StartDate)) AND
(DATEPART(DAY, @date_to_check) = DATEPART(DAY, rsf.StartDate))

Dave
Helixpoint Web Development
http://www.helixpoint.com

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-05 : 12:18:26

[code]

SELECT rsf.ID, rsf.ReportFormat, rsf.ReportTemplate
FROM tblReportScheduleFormat rsf
INNER JOIN tblScheuduleFrequency sf ON
sf.ID = rsf.FrequencyID
WHERE
@date_to_check between rsf.StartDate and Coalesce(rsf.EndDate, '20790601') AND
--FrequencyID :: 1=Daily; 2=Weekly; 3=Monthly; 4=Yearly.
((rsf.FrequencyID = 2 AND (
(DATEPART(weekday, @date_to_check) = 1 AND Sunday = 1) OR
(DATEPART(weekday, @date_to_check) = 2 AND Monday = 1) OR
(DATEPART(weekday, @date_to_check) = 3 AND Tuesday = 1) OR
(DATEPART(weekday, @date_to_check) = 4 AND Wednesday = 1) OR
(DATEPART(weekday, @date_to_check) = 5 AND Thursday = 1) OR
(DATEPART(weekday, @date_to_check) = 6 AND Friday = 1) OR
(DATEPART(weekday, @date_to_check) = 7 AND Saturday = 1)))
OR
(rsf.FrequencyID = 3 AND rsf.DayNumber = DAY(@date_to_check) AND
DATEDIFF(MONTH, rsf.StartDate, @date_to_check) % Recurrence = 0)
OR
(rsf.FrequencyID = 4 AND
(DATEPART(MONTH, @date_to_check) = DATEPART(MONTH, rsf.StartDate)) AND
(DATEPART(DAY, @date_to_check) = DATEPART(DAY, rsf.StartDate)))
)

[/code]

Btw, rather than a separate flag for each day, a single tinyint value with bit flags would be easier to test.

I also specifically didn't use DATEPART because it depends on SQL settings, including DATEFIRST and language settings. My earlier method always works for all date and language settings.

You might also want to consider making FrequencyID of 4 be "QUARTERLY" and using 5 for "YEARLY".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources