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.tblReportScheduleFormatID - INTStartDate DATEEndDate - DATEMonday - BITTuesday - BITWednesday - BITThursday - BITFriday – BITFrequencyID - FK INTData - VARCHAR(50)Record Example1,09/04/2013, True, False, False, False, False,3,’DATA’tblScheuduleFrequencyID – INTNAME – VARCHAR(50)Data:1, Daily 2, Weekly3, MonthlyDaveHelixpoint Web Developmenthttp://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.tblReportScheduleFormatID - INTStartDate DATEEndDate - DATEMonday - BITTuesday - BITWednesday - BITThursday - BITFriday – BITFrequencyID - FK INTData - VARCHAR(50)Record Example1,09/04/2013, True, False, False, False, False,3,’DATA’tblScheuduleFrequencyID – INTNAME – VARCHAR(50)Data:1, Daily 2, Weekly3, MonthlyDaveHelixpoint Web Developmenthttp://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 SQLDaveHelixpoint Web Developmenthttp://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 datetimeSET @date_to_check = GETDATE()--DECLARE csrReportsToRunOnThisDate CURSOR FAST_FORWARD FORSELECT IDFROM dbo.tblReportScheduleFormat rsfINNER JOIN dbo.tblScheduleFrequency sf ON sf.ID = rsf.FrequencyIDWHERE (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.DayNumberDaveHelixpoint Web Developmenthttp://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
|
ahhDaveHelixpoint Web Developmenthttp://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,blaa6,1,2013-09-04,NULL,blaaa,0,0,0,0,1,0,0,NULL,2,3,PDF,blaa8,2,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,5,3,2,PDF,Blaa10,3,2013-09-05,NULL,Blaaa,0,0,0,0,0,0,0,NULL,4,1,PDF,BlaaCREATE TABLE [dbo].[tblScheuduleFrequency]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](50) NOT NULL,CONSTRAINT [PK_tblScheuduleFrequency] PRIMARY KEY CLUSTERED 1,Daily2,Weekly3,Monthly4,YearlyDECLARE @date_to_check datetimeSET @date_to_check = GETDATE()--SET @date_to_check = '2013-09-02'SELECT rsf.ID, rsf.ReportFormat, rsf.ReportTemplateFROM tblReportScheduleFormat rsfINNER JOIN tblScheuduleFrequency sf ON sf.ID = rsf.FrequencyIDWHERE @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))DaveHelixpoint Web Developmenthttp://www.helixpoint.com |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-05 : 12:18:26
|
[code]SELECT rsf.ID, rsf.ReportFormat, rsf.ReportTemplateFROM tblReportScheduleFormat rsfINNER JOIN tblScheuduleFrequency sf ON sf.ID = rsf.FrequencyIDWHERE @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". |
|
|
|
|
|