473,734 Members | 2,331 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

specialized Last Business Day of Previous Month

1 New Member
Platform: Windows2000, WindowsXP, Windows Vista, etc
Language: C#, ASP.NET
Pre-compiled Libraries: Enterprise Library 3.0 full


I have a requirement to implement in and display in C# and ASP.NET a DataGrid with Updatable rows based on a date retrieved from a data table in SQL Server. Below is the design algorithm.

Expand|Select|Wrap|Line Numbers
  1. 1.  Retrieve the Max(rundate) From MyDataTable
  2. 2.  If the current month -1 = rundate .month from step 1 then
  3.      2a.  Display a message that no updates can be made to the DataGrid at
  4.             this time.
  5.      else
  6.      2b.  Compute the next rundate to insert to MyDataTable based on the
  7.             rundate from step 1.
  8. 3.  Insert a set amount of rows of data (hardcoded values in a stored proc) with
  9.      the computed date and an amount passed to a stored procedure and then
  10.      executed
  11. 4.  Display Updatable grid with new data after insert has committed
  12.  
  13.  

In step 2a I need to get a date. this date has the following requirements:

1. it must be greater than the Max(rundate) from MyDataTable.
2. it must be 1 month in the past.
3. it must be the End of The Month and more specifically it must be
the Last Business Day of the Month (cannot be a Sat Sun Or Fed Holiday).
4. and finally If step 2b is called the date must be the last business day
of the previous month from current aka if the last business day of march
is the 31 and today is the April 1st and the year is 2008 then the date
inserted into MyDataTable should be 2/29/2008

Here is the code ive implemented so far:

Expand|Select|Wrap|Line Numbers
  1. protected void Page_Load(object sender, EventArgs e)
  2.     {
  3.         if (!IsPostBack)
  4.         {
  5.             try
  6.             {
  7.                 localhost.Service svc = new localhost.Service();
  8.  
  9.                 // Step 1 code to get xxxxxx max rundate here:
  10.  
  11.                 DataSet datedata = new DataSet();
  12.                 datedata = svc.QueryDate();
  13.                 DateTime xxxxdate = Convert.ToDateTime(datedata.Tables[0].Rows[0][0].ToString().Trim());
  14.                 DateTime curdate = DateTime.Now;
  15.                 // Step 2 If the current month -1 is equal to the rundate
  16.                 // from XXXXXXX display a message that no updates are possible at this time
  17.                 if (curdate.Month - 1 == xxxxdate.Month & curdate.Year == vfccdate.Year)
  18.                 {
  19.                     throw new Exception("No updates available for the current month. If you wish to update the XXXXXX data for "
  20.                                             + xxxxx + " use the override button below.");
  21.                 }
  22.  
  23.                 // Step 3 Otherwise compute the next rundate based on the max rundate from step 1
  24.                 else
  25.                 {
  26.                     // Last Business Day Of Month Validation.
  27.                     DateTime dtResult = getLastBusinessDayOfMonth(xxxxdate);
  28.  
  29.                      // If the month is more than one month out we need to insert new data rows
  30.                         if (dtResult.Month != DateTime.Now.Month - 1)
  31.                         {
  32.                             curdate = getLastBusinessDayOfMonth(curdate);
  33.                             DataSet dsReturn = new DataSet();
  34.                             dsReturn = svc.populateBaseDataGridsWithDate("MyDataTable", curdate);
  35.  
  36.  
  37.                         }
  38.                         dgMyDataGrid.DataSource = svc.populateBaseDataGridsWithDate("MyDataTable", dtResult);
  39.                         dgMyDataGrid.DataBind();
  40.  
  41.  
  42.  
  43.  
  44.                 }
  45.  
  46.             }
  47.             catch (Exception ex)
  48.             {
  49.                 this.lblErrorMessage.Text = "An error occured in:  " + ex.Message.ToString() +
  50.                     " Raised by the following inner exception:  " + ex.InnerException.ToString();
  51.                 this.btnOverrideData.Enabled = true;
  52.                 this.btnOverrideData.Visible = true;
  53.  
  54.             }
  55.          }
  56.          if (dgVFCC_SettledPositions.Items.Count <= 0)
  57.          {
  58.              dgVFCC_SettledPositions.Visible = false;
  59.              lblErrorMessage.Visible = true;
  60.          }
  61.  
  62.     }
  63.  
  64.    ...
  65.  
  66. private DateTime getLastBusinessDayOfMonth(DateTime dtDate)
  67.     {
  68.         try
  69.         {
  70.             // get the last day of the month
  71.             DateTime dtTest = new DateTime();
  72.             dtTest = dtDate;
  73.             dtTest.AddMonths(1);
  74.             dtTest.AddDays(-(dtTest.Day));
  75.  
  76.  
  77.  
  78.  
  79.             if (dtTest.Month == 5 & dtTest.DayOfWeek == DayOfWeek.Monday)   // Memorial Day can fall on the last day of the month so check for it first
  80.             {
  81.                 dtTest.AddDays(-3);
  82.                 return dtTest;
  83.             }
  84.             else
  85.             {
  86.                 if (dtTest.DayOfWeek == DayOfWeek.Saturday)
  87.                 {
  88.                     // this should insure a Friday if the day of week is a Saturday
  89.                     dtTest.AddDays(-1);
  90.                     return dtTest;
  91.                 }
  92.                 else if (dtTest.DayOfWeek == DayOfWeek.Sunday)
  93.                 {
  94.                     // this should insure a Friday if the day of week is a Sunday
  95.                     dtTest.AddDays(-2);
  96.                     return dtTest;
  97.                 }
  98.                 else
  99.                 {
  100.                     // If dtTest is anything other than this return it its a valid workday
  101.                     return dtTest;
  102.                 }
  103.                 // Return the value regardless of what happened in the conditional
  104.                 //return dtTest;
  105.             }
  106.  
  107.         }
  108.         catch (Exception ex)
  109.         {
  110.             // the only reason we should be in this catch block is if a null date was passed to the function
  111.             // let the user know and exit gracefully.
  112.  
  113.             this.lblErrorMessage.Text = "A NullDate Exception was encountered in " + ex.InnerException + "Contact your Systems Administrator for help with this function.";
  114.             this.lblErrorMessage.Visible = true;
  115.             this.btnOverrideData.Enabled = false;
  116.             this.btnOverrideData.Visible = false;
  117.             this.dgMyDataGrid.Visible=false;
  118.             this.dgMyDataGrid.Enabled=false;
  119.  
  120.  
  121.  
  122.         }
  123.         return dtDate;
  124.     }
  125.  
  126.  
  127. ...
  128.  
  129.  
  130.  
  131.  
  132.  
  133.  
Expand|Select|Wrap|Line Numbers
  1. //Service.asmx relevant code:
  2.  
  3. [WebMethod(EnableSession = true, Description = "Populate a Disconnected Dataset based on TableName for base data tables and a DateTime data value",
  4.         TransactionOption = TransactionOption.Supported)]
  5.     public DataSet populateBaseDataGridsWithDate(string TableName, DateTime rundate)
  6.     {
  7.         return getData(TableName,rundate);
  8.     }
  9.  
  10. private DataSet getData(string TableName, DateTime rundate)
  11.     {
  12.  
  13.  
  14.         Database sqldb = DatabaseFactory.CreateDatabase();
  15.         DataSet ServiceResults = new DataSet();
  16.         try
  17.         {
  18.  
  19.             if (TableName != "MyOtherDataTable")
  20.             {
  21.                 switch (TableName)
  22.                 {
  23.                     case "MyDataTable":
  24.                         DataSet ds5 = new DataSet();
  25.                         string strFlag = isInsertUpdate(rundate);
  26.                         string storedProcName;
  27.                         Decimal dmMyVar = 0.00M;
  28.                         string BusId = "999999";
  29.                         DbCommand cmd; ; 
  30.                         if (strFlag == "Insert")
  31.                         {
  32.                             storedProcName = "usp_InsertMyDataTable";
  33.                             cmd = sqldb.GetStoredProcCommand(storedProcName);
  34.                             sqldb.AddInParameter(cmd, "@myVar", DbType.Currency, dmMyVar);
  35.                             sqldb.AddInParameter(cmd, "@rundate", DbType.DateTime, rundate);
  36.                         }
  37.                         else
  38.                         {
  39.  
  40.                             storedProcName = "usp_getMyDataTable";
  41.                             cmd = sqldb.GetStoredProcCommand(storedProcName);
  42.                             sqldb.AddInParameter(cmd, "@rundate",DbType.DateTime, rundate);
  43.                         }
  44.  
  45.  
  46.                         return ds5 = sqldb.ExecuteDataSet(cmd);
  47.                     default:
  48.                         throw new ArgumentOutOfRangeException("Table Name not recognized");
  49.                 }
  50.  
  51.             }
  52.             else
  53.             {
  54.  
  55.                 DataSet dsExceptions = new DataSet();
  56.                 return dsExceptions = sqldb.ExecuteDataSet("usp_LoadMyOtherDataTable");
  57.  
  58.  
  59.             }
  60.         }
  61.         catch (ArgumentOutOfRangeException aorex)
  62.         {
  63.             Logger.Write(aorex.Message.ToString());
  64.  
  65.         }
  66.         catch (Exception ex)
  67.         {
  68.             Logger.Write(ex.Message.ToString());
  69.  
  70.         }
  71.         return ServiceResults;
  72.  
  73.  
  74.  
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81.     }
  82.  
  83. ...
  84.  
  85. private string isInsertUpdate(DateTime rundate)
  86.     {
  87.         Database sqldb = DatabaseFactory.CreateDatabase();
  88.         string storedProcName = "usp_QueryRunDate";
  89.         DbCommand cmd = sqldb.GetStoredProcCommand(storedProcName);
  90.         sqldb.AddInParameter(cmd, "@querydate", DbType.DateTime, rundate);
  91.         DataSet dsReturn = sqldb.ExecuteDataSet(cmd);
  92.         string ReturnValue = dsReturn.Tables[0].Rows[0][0].ToString();
  93.         if (ReturnValue != "0")
  94.         {
  95.             return "Update";
  96.         }
  97.         else
  98.         {
  99.             return "Insert";
  100.         }
  101.     }
  102.  
  103.  
The base issue is getting all of the requirements met for the rundate I need to try and calculate the DATE not the month day and year Int values that meets the conditions each month.

Any help would be greatly appreciated. If I have been overly verbose let me know and I will try to make this more concise
Apr 1 '08 #1
0 2361

Sign in to post your reply or Sign up for a free account.

Similar topics

3
9719
by: Killer | last post by:
How can i calculate the last day of the previous month? Help me,please
5
5758
by: Ray via SQLMonster.com | last post by:
Hi there, See if you can help me with the following: I need to write an SQL code that will return me: The 1st day & the Last day of the Previous Month in the following format (smalldatetime): yyyy-mm-dd hh:mi:ss (24h)
13
3613
by: SimonC | last post by:
I would like to return data from the last 2 weeks of each given month in Javascript, but in 2 formats. So, the penultimate week (Monday to Sunday) and the last week (Monday to ??) I'm not sure if it can be done, but all help welcomed. E.g. I have December and would like to see the last 2 weeks.. So this doesnt mean the last 15 days. What i mean by this is...
2
1687
by: JonZ | last post by:
I would like to setup MS Access to use with my maintenance business and am trying to figure out the best way to do it. I have a customer list that stays pretty consistent. Each month I send out an invoice to each customer for the previous months work which might include extra non recurring items. I need to keep the invoice records on file for accounts recievable and reference. I presently use clarisworks and make a seperate file for...
7
6034
by: MLH | last post by:
Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date '************************************************************************** ' Accepts a date. Determines month & year of the date. Returns ' the date of the last day of that month (in the same year) '************************************************************************** GetLastDayOfMonth = DateSerial(Year(dtDay), Month(dtDay), 31) End Function If I enter dtDay value of...
5
2051
by: G. Stewart | last post by:
The word "Business" in the term implies some sort of commercial aspects or connotations. But from what I can see, that is not necesserially the case at all? So what is the reasoning behind the term? Stupid question, I know ... and quite irrelevant in many ways ... but I really would like to know!
9
4653
by: brymcguire | last post by:
Hi, I have a requirement to design a query that identifies items sold between two dates. There is a 'SoldDate' datetime field used to register what date the item was sold. The query needs to identify all sales between the last day of the previous month and going back one year.
1
2315
by: guyborn | last post by:
I have been trying to get data from the database from the of the previous month to the second last week of the current month.I only managed to get data from the previous month to today's date. your help will be highly appreciated Regards Guyborn
4
11961
by: gimme_this_gimme_that | last post by:
Is there a way to get the last day of the previous business quarter from DB2? For 10/21/2008 the day would be 9/30/2008. Thanks.
0
8951
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9458
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9248
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8192
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6743
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4555
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4818
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3266
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2734
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.