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

Posted on 2011-10-27
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
    LVL 33

    Expert Comment

    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 33

    Expert Comment

    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

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

    LVL 7

    Author Comment


    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 33

    Accepted Solution

    DIm intDayofWeek = WeekDay(Now())
    Dim dtPreviousWorkDay
    if intDayofWeek = 2 Then
         dtPreviousWorkDay = DateAdd("d", -3, Now())
         dtPreviousWorkDay = DateAdd("d", -1, Now())
    End If

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    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…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now