Need to e-mail a shift report for previous WEEKDAY's data to plant manager
Posted on 2011-10-27
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?