September 5, 2013 at 7:49 am
I am trying to schedule emails. I have to write my own tables. I am not the best SQL coder, so I may be way off. I am really having a hard time withrecurrance. If the recurrance is 2 and the frequency is 3. It is every 2 months.The code is what I have so far. Recurrance is not added. Sql does not seem to be working
DECLARE @date_to_check datetime
SET @date_to_check = GETDATE()
--SET @date_to_check = '2013-09-02'
SELECT rsf.ID, te.Name,rsf.Data, rsf.ReportFormat, rsf.ReportTemplate
FROM tblReportScheduleFormat AS rsf INNER JOIN
tblScheuduleFrequency AS sf ON sf.ID = rsf.FrequencyId INNER JOIN
tblTrkEmail AS te ON rsf.EmailId = te.Id
WHERE @date_to_check between rsf.StartDate and Coalesce(@date_to_check, NULL)
AND (rsf.FrequencyId = 3) AND (rsf.DayNumber = DAY(@date_to_check)) OR
(rsf.FrequencyId = 4) AND (rsf.DayNumber = DAY(@date_to_check)) OR
(rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 0) AND (rsf.Monday = 1) OR
(rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 1) AND (rsf.Tuesday = 1) OR
(rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 2) AND (rsf.Wednesday = 1) OR
(rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 3) AND (rsf.Thursday = 1) OR
(rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 4) AND (rsf.Friday = 1)
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
1,1,2013-09-03,NULL,Blaaaa,0,1,0,0,0,0,0,NULL,1,2
2,1,2013-09-04,NULL,blaaa,0,0,0,0,0,0,0,NULL,2,3
3,2,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,15,3,2
4,3,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,NULL,4,1
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
September 5, 2013 at 8:15 am
Thanks for trying to post ddl and sample data. It is generally preferred to post insert statements to make it easier. Also you should post all of the tables involved. 😛
I don't exactly know what you are trying to do here but your where clause has some major flaws in it.
Let's break it into sections.
@date_to_check between rsf.StartDate and Coalesce(@date_to_check, NULL)
Not sure what you are trying to do here but this doesn't work. You are checking if the value of a variable is between rsf.StartDate and the value of the variable. I can't even begin to figure out why you have the coalesce there at all. It will return the first nonNULL value in the list. Putting NULL as one of the options is just pointless. It seems like maybe this should be:
rsf.StartDate < @date_to_check
Now let's move on the the next section that I don't understand.
DATEDIFF(DAY, 0, @date_to_check) = 0
This will NEVER evaluate to 0 unless the current system date is January 1st, 1900. The value of @date_to_check is the current time. The datediff function returns the number of days since 1/1/1900.
I also suspect that you have some logic errors in your conditions as well. Since you don't have parenthesis around your OR predicates they will evaluate by themselves.
Perhaps if you can post the last table and convert your data to insert statements along with an explanation of what you are trying to do here we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2013 at 8:35 am
There is one other table. That is the email table. I tried to fix this up a bit. Sorry. My SQL sucks. The Coalesce was messed up. The EndDate could be NULL
DECLARE @date_to_check datetime
SET @date_to_check = GETDATE()
SELECT rsf.ID, DATEPART(weekday, @date_to_check)
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)))
September 5, 2013 at 8:54 am
OK. So what is the question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2013 at 9:07 am
I am try to work Recurrance into the mix. Like I said in the original question
September 5, 2013 at 9:15 am
davef 22400 (9/5/2013)
I am try to work Recurrance into the mix. Like I said in the original question
OK we need to take a step back here. I still don't have all the tables involved and we have not yet seen data in a format that in conducive to working with. I have no idea what the recurrence means. I don't know your business, I don't know your data and I have no idea what you are trying to do. Luckily I am willing to help but it will take some work on your end first to provide the details so I can help.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply