SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Scheduling
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

286 Posts

Posted - 09/04/2013 :  12:55:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/04/2013 :  12:57:56  Show Profile  Reply with Quote
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?
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

286 Posts

Posted - 09/04/2013 :  13:35:44  Show Profile  Reply with Quote
I am not sure how to do this SQL

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/04/2013 :  14:00:48  Show Profile  Reply with Quote
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

Edited by - Lamprey on 09/04/2013 14:01:42
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 09/04/2013 :  14:22:24  Show Profile  Reply with Quote
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)))

Go to Top of Page

helixpoint
Constraint Violating Yak Guru

286 Posts

Posted - 09/04/2013 :  15:26:42  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 09/04/2013 :  15:53:54  Show Profile  Reply with Quote
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

Edited by - ScottPletcher on 09/04/2013 15:55:16
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

286 Posts

Posted - 09/04/2013 :  15:59:26  Show Profile  Reply with Quote
ahh

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 09/04/2013 :  18:31:25  Show Profile  Reply with Quote
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).
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

286 Posts

Posted - 09/05/2013 :  11:42:00  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 09/05/2013 :  12:18:26  Show Profile  Reply with Quote


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)))
     )



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".

Edited by - ScottPletcher on 09/05/2013 12:20:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000