Need to e-mail a shift report for previous WEEKDAY's data to plant manager

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
I have an MS SQL database which stores data fr one of our manufacturing facilities.  Data is entered and viewed via web pages.
My plant manager is asking that an e-mail be sent to him with the previous night's data so that he doesn't have to open a web page, make data selections and view a report to print.  He wants data sent to his e-mail address so he can receive it on his Blackberry.

I know how to pull the data and send it in an e-mail.  My issue is with data selection.

Data is stored by date entered and by shift number.  In this case, plant manager wants data from second shift sent to him.

Tuesday thru Friday after the first day of the moth this is simple: get the current date, subtract one and pull data for second shift.

However, is ASP code smart enough to know that (November 1, 2001) - 1 = October 31, 2011?
Also, how do I account for Monday runs?  I need the code to go back to the previous Friday.  For example, a report run on October 31, 2011 will need to go back to data from October 28, 2011.  However, a report run on January 2, 2012 will need to go back to December 30, 2011.

How can I automate all of these scenarios to pull second shift data from the previous shift?
Question by:dimmergeek
  • 3
LVL 34

Expert Comment

by:Paul MacDonald
ID: 37037941
Yes.  You can do something like this:
   Dim dtYesterday = DateAdd("d", -1, Date)
which will set dtYesterday to one day before whatever Date is.

LVL 34

Expert Comment

by:Paul MacDonald
ID: 37037973
Sorry, I neglected the rest of the question.  

You'll have to look for Mondays and branch the code based on that.  If you do:
   Dim intDayOfWeek = WeekDay(Now())
and branch if intDayOfWeek = 2 (which is Monday) you'll be all set.
LVL 18

Expert Comment

ID: 37037974
use the datepart function to decide how many days to go back.  For sunday, 2, Monday = 3, everything else = 1.  Hopefully, you won't have to worry about holidays...


Author Comment

ID: 37038074

So, the code going to be:

DIm intDayofWeek = WeekDay(Now())

if intDayofWeek = 2 Then
     ' Here's where I'm lost.  How do I go back to the previous Friday?
     ' What if it's not a Monday?
     ' In many cases I can just subtract 1, but what if the previous day is the last day of the previous month?

     As for holidays, we do not run on holidays, so when I run my SQL it will come back empty.  When th erecordset comes back empty, I will either not send an e-mail or send one that says there was no data to report just so the guy knows the program is still running.
LVL 34

Accepted Solution

Paul MacDonald earned 1200 total points
ID: 37038092
DIm intDayofWeek = WeekDay(Now())
Dim dtPreviousWorkDay
if intDayofWeek = 2 Then
     dtPreviousWorkDay = DateAdd("d", -3, Now())
     dtPreviousWorkDay = DateAdd("d", -1, Now())
End If

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question