Need to e-mail a shift report for previous WEEKDAY's data to plant manager
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?
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?
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.
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.
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...
ASKER
paulmacd:
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?
else
' 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?
UnifiedIS:
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.
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?
else
' 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?
UnifiedIS:
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim dtYesterday = DateAdd("d", -1, Date)
which will set dtYesterday to one day before whatever Date is.