May 5, 2010 at 6:22 am
Hi:
We are running a students database on SQL server 2005 box. I need to create a query from two tables ( student , attendance). if a student is a way for three consecutive days then and alert with the students details should be sent to the an email address using Database mail.
I know how to construct basic select statements but this is beyond my knowledge. your help is much appreciated.
May 5, 2010 at 7:12 am
We need a little more information to help you.
First question is how will this e-mail be sent? will this be a job that runs every day? If so is it correct to assume you only need to look at the last three days?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 5, 2010 at 7:41 am
Since this is homework, you need to show the work you've done already.
In order for someone to really help they're going to need to see the structure of the tables that you're querying from and some sample data. Please post that information along with the queries that you've been working on and you should get some help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2010 at 7:59 am
Ok, i can understand what you want your code to do. I am in the middle of writing a query for you, but it will take time, In the meanwhile, as you have not provided the DDLs, people will just pass over this thread.
For the sake of you, i have created some sample DDLs.. If not you others may use this and start preparing the code.
IF OBJECT_ID('TEMPDB..#STUDENTS') IS NOT NULL
DROP TABLE #STUDENTS
CREATE TABLE #STUDENTS
(
S_ID INT IDENTITY (1,1),
[NAME] VARCHAR(64)
)
IF OBJECT_ID('TEMPDB..#ATTENDANCE') IS NOT NULL
DROP TABLE #ATTENDANCE
CREATE TABLE #ATTENDANCE
(
A_ID INT IDENTITY (1,1),
S_ID INT ,
WEEK_NUM INT,
DAY_NUM INT,
ATTENDANCE INT
)
INSERT INTO #STUDENTS( [NAME] )
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
INSERT INTO #ATTENDANCE (S_ID,WEEK_NUM,DAY_NUM,ATTENDANCE)
SELECT 1, 1, 1, 1
UNION ALL SELECT 1, 1, 2, 1
UNION ALL SELECT 1, 1, 3, 1
UNION ALL SELECT 1, 1, 4, 1
UNION ALL SELECT 1, 1, 5, 0
UNION ALL SELECT 2, 1, 1, 1
UNION ALL SELECT 2, 1, 2, 0
UNION ALL SELECT 2, 1, 3, 1
UNION ALL SELECT 2, 1, 4, 1
UNION ALL SELECT 2, 1, 5, 0
UNION ALL SELECT 3, 1, 1, 0
UNION ALL SELECT 3, 1, 2, 0
UNION ALL SELECT 3, 1, 3, 1
UNION ALL SELECT 3, 1, 4, 1
UNION ALL SELECT 3, 1, 5, 0
UNION ALL SELECT 1, 2, 1, 1
UNION ALL SELECT 1, 2, 2, 1
UNION ALL SELECT 1, 2, 3, 1
UNION ALL SELECT 1, 2, 4, 1
UNION ALL SELECT 1, 2, 5, 0
UNION ALL SELECT 2, 2, 1, 1
UNION ALL SELECT 2, 2, 2, 0
UNION ALL SELECT 2, 2, 3, 0
UNION ALL SELECT 2, 2, 4, 1
UNION ALL SELECT 2, 2, 5, 0
UNION ALL SELECT 3, 2, 1, 0
UNION ALL SELECT 3, 2, 2, 0
UNION ALL SELECT 3, 2, 3, 0
UNION ALL SELECT 3, 2, 4, 1
UNION ALL SELECT 3, 2, 5, 0
SELECT * FROM #STUDENTS
SELECT * FROM #ATTENDANCE
Please edit this input data if you feel it is not similar to your data!
Cheers!
May 5, 2010 at 8:02 am
Please go through this following article to understand how to post data to get the best help:CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
When u do so, i am sure a lot of us will help u instantly...
So please post
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
Cheers!
May 5, 2010 at 8:34 am
Hey there! I have coded an below-par code for this requirement! This is what that struck my mind! Pro'ly there are many altenative super-fast , minimal-code, optimal-performance code available. But for instance, keep this.
The code below will find the Student ID, the week number and the days , if a student had consecutively taken 3 days leave!
DECLARE @sid INT , @WEEK_NUM INT
DECLARE GROUP_CURSOR CURSOR LOCAL
FOR
SELECT S_ID, WEEK_NUM FROM #ATTENDANCE GROUP BY S_ID, WEEK_NUM
OPEN GROUP_CURSOR
FETCH NEXT FROM GROUP_CURSOR INTO @sid , @WEEK_NUM
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @START_DAY_WEEK TINYINT
DECLARE @END_DAY_WEEK TINYINT
DECLARE @SUM INT
SET @START_DAY_WEEK = 1
WHILE @START_DAY_WEEK <=5
BEGIN
SELECT @SUM = CASE WHEN COUNT(ATTENDANCE) = 3 THEN SUM(ATTENDANCE) END FROM #ATTENDANCE
WHERE (S_ID = @sid AND WEEK_NUM = @WEEK_NUM) AND
(
((DAY_NUM = @START_DAY_WEEK) )
OR
(DAY_NUM = (@START_DAY_WEEK + 1))
OR
(DAY_NUM = (@START_DAY_WEEK + 2))
)
IF @SUM = 0
BEGIN
SELECT @sid S_ID , @WEEK_NUM WEEK_NUM
, @START_DAY_WEEK DAY_NUM1 , @START_DAY_WEEK+1 DAY_NUM2 , @START_DAY_WEEK+2 DAY_NUM3
--== EDIT @HTMLBody to be a concatenated string of the above select.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notification Mailer',
@recipients = @EMailAddress,
@subject = 'Absence Notification',
@body = @HTMLBody,
@body_format = 'HTML',
@mailitem_id = @MailItemID OUTPUT
END
SET @START_DAY_WEEK = @START_DAY_WEEK+ 1
END
FETCH NEXT FROM GROUP_CURSOR INTO @sid , @WEEK_NUM
END
For the Database Mail part, go through the Books Online (Free help that will come along with the SQL server 2005 and also available on the Internet for free)! The commented part in the above code is how you will trigger a mail for an operation!
Hope this helps you! Tell us if that did the trick for you!
Cheers!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply