Month Ranges With User Assigned Dates

I am currently bringing in data by the actual month and year.  It is done through vb.net code using Visual Studios 2008.  

 Jan/2011     1/1/2011 to 1/31/2011
 Feb/2011     2/1/2011 to 2/28/2011
 Mar/2011     3/11/2011 to 3/31/2011
 Apr/2011     4/1/2011 to 4/30/2011
May/2011      5/1/2011 to 5/31/2011  etc...

I posted the code below.  It initially was built starting at 1/1/2008 to current date at the time.  From then on, whenever a particular report is ran, it clears current year of data, then adds back the current years data.  Basically, refreshes the data of the current year.   See code...

It refers to a "Month_Table" that is:

Month          Month_Name

1                      January
2                      February
3                      March
4                      April  all the way through December.


I now need to bring in the data by "month" that a user assign:

Jan/2011  1/1/2011  to 1/31/2011
Feb/2011  2/1/2011 to 2/28/2011
Mar/2011  3/1/2011 to 4/3/2011
Apr/2011  4/4/2011 to 5/1/2011
May/2011  5/2/2011 to 5/31/2011   etc...

Now...I have a new "EOMRange" table starting in 2008 to current.

Month_Integer      Month_Name      Year         Beg_Date     End_Date
     ......
     1                          January           2011         1/1/2011       1/31/2011
     2                          February         2011          2/1/2011      2/28/2011
     3                          March              2011          3/1/2011       4/3/2011
     4                          April                 2011          4/4/2011       5/1/2011
     5                          May                 2011           5/2/2011    5/31/2011

Above is the "EOMRange" table's columns/names. (Can be used to replace the table "Month_Table" I mentioned above.)  This table needs to be used as a reference table to assign months and years to pull data in using the Beg_Date and End_Date for a month range.

I hope this is clear enough...if not...please let me know.
Thanks in advance for any help!
Tanya

Public Sub GetFuelCostAvg()

        Dim QueryString As String
        Dim MyRowAVG As DataRow
        Dim ColumnAVG As String
        Dim ColumnCost As String
        Dim ColumnQty As String
        Dim ColumnAVGNext As String

             'Empty table FuelCostWeightedAVGByMonth For Current Year
        QueryString = "DELETE FROM FuelCostWeightedAVGByMonth WHERE Year = Year(GetDate()) ;"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
       
        'Appends records for each Stock_Number per year, per month....only for Current Year
        ' QueryString = "INSERT INTO FuelCostWeightedAVGByMonth ( Stock_Number, [Month], [Year], Type ) SELECT DISTINCT TankMaster.Stock_Number, Month_Table.Month, Year_Table.Year, TankMaster.End_Of_Month_Grouping FROM Month_Table, TankMaster, Year_Table WHERE (((TankMaster.Stock_Number) Is Not Null) AND ((Year_Table.Year) Between 2008 And Year(getDate()))) ORDER BY Tankmaster.Stock_Number ASC, Year_Table.Year ASC, Month_Table.Month ASC;"
        QueryString = "INSERT INTO FuelCostWeightedAVGByMonth ( Stock_Number, [Month], [Year], Type ) SELECT DISTINCT TankMaster.Stock_Number, Month_Table.Month, Year_Table.Year, TankMaster.End_Of_Month_Grouping FROM Month_Table, TankMaster, Year_Table WHERE (((TankMaster.Stock_Number) Is Not Null) AND ((Year_Table.Year) = Year(getDate()))) ORDER BY Tankmaster.Stock_Number ASC, Year_Table.Year ASC, Month_Table.Month ASC;"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
        QueryString = "DELETE FuelCostWeightedAVGByMonth WHERE Month > Month(getdate()) AND Year = Year(GetDate());"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)

        'Updates each record that has data
        QueryString = "SELECT Unit_Of_Measure, Stock_Number FROM FuelCost ;"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
        For Each MyRow In MyDataSet.Tables("AnswerSet").Rows
            QueryString = "Update fuelcostweightedavgbymonth SET UM = '" & MyRow("Unit_Of_Measure") & "' WHERE Stock_Number = '" & MyRow("Stock_Number") & "';"
            Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
        Next
        MyDataSet = Nothing
        MyRow = Nothing

        QueryString = "SELECT Stock_Number, Fuel_Type, Sum(Extended_Cost) AS Cost, Sum(Quantity) AS Quantity, Sum(Extended_Cost) / Sum(Quantity) AS WeightedAVG, Month(Date) as Month, Year(Date) as Year FROM FuelCost Where Cost > 0 GROUP BY Stock_Number,  Year(Date), Month(Date), Fuel_Type ORDER BY Stock_Number ASC, Year(Date) ASC, Month(Date) Asc"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
        For Each MyRow In MyDataSet.Tables("AnswerSet").Rows

            'Set Variables
            FuelCostYear = MyRow("Year")
            FuelCostMonth = MyRow("Month")
            FuelCostStock = Trim(MyRow("Stock_Number"))
            FuelCostType = MyRow("Fuel_Type")
            WeightedAVG = MyRow("WeightedAVG")
            FuelCostCost = MyRow("Cost")
            FuelCostQuantity = MyRow("Quantity")

            'Update Values to FuelCostWeightedAVGByMonth            
            ColumnAVG = "Weighted_AVG_" & FuelCostMonth & ""
            ColumnCost = "Cost_" & FuelCostMonth & ""
            ColumnQty = "Quantity_" & FuelCostMonth & ""

            QueryString = "SELECT Month, Year, [" & ColumnAVG & "], [" & ColumnCost & "], [" & ColumnQty & "], Type FROM FuelCostWeightedAVGByMonth WHERE Year = '" & FuelCostYear & "' AND Month = '" & FuelCostMonth & "' AND Stock_Number = '" & FuelCostStock & "';"
            Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
            For Each MyRowAVG In MyDataSet.Tables("AnswerSet").Rows

                QueryString = "Update fuelcostweightedavgbymonth SET Cost = '" & FuelCostCost & "', Quantity = '" & FuelCostQuantity & "', Weighted_AVG =   '" & WeightedAVG & "', [" & ColumnCost & "] = '" & FuelCostCost & "', [" & ColumnQty & "] = '" & FuelCostQuantity & "', [" & ColumnAVG & "] =   '" & WeightedAVG & "' WHERE Stock_Number = '" & FuelCostStock & "' AND Month = '" & FuelCostMonth & "' AND Year = '" & FuelCostYear & "';"
                Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
                WeightedAVG = 0
            Next
            MyDataSet = Nothing
            MyRow = Nothing
        Next
        MyDataSet = Nothing
        MyRow = Nothing

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Carry over previous months Average if zero/null
        QueryString = "SELECT * FROM FuelCostWeightedAVGByMonth WHERE Year >= 2008 ORDER BY Stock_Number ASC, Year ASC, Month ASC"
        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
        For Each MyRow In MyDataSet.Tables("AnswerSet").Rows

            'Sets Variables
            FuelCostYearCurrent = MyRow("Year")
            FuelCostMonthCurrent = MyRow("Month")
            FuelCostStock = Trim(MyRow("Stock_Number"))

            'Sets Column Name
            ColumnAVG = "Weighted_AVG_" & FuelCostMonthCurrent & ""
            If FuelCostMonthCurrent = 13 Then
                FuelCostMonthCurrent = 1
                ColumnAVG = "Weighted_AVG_" & FuelCostMonthCurrent & ""
                FuelCostYearCurrent = FuelCostYearCurrent
            End If

            ColumnAVGNext = "Weighted_AVG_" & FuelCostMonthCurrent + 1 & ""
            FuelCostMonth = FuelCostMonthCurrent + 1
            FuelCostYearCurrent = FuelCostYearCurrent

            If FuelCostMonth = 13 Then
                FuelCostMonth = 1
                FuelCostMonthCurrent = 1
                ColumnAVGNext = "Weighted_AVG_" & FuelCostMonth & ""
                FuelCostYearCurrent = FuelCostYearCurrent + 1
            End If

            'Checks if Stock_Number Change, if so, then reset the WeightedAVGCurrent
            If IsDBNull(MyRow(ColumnAVG)) Then
                WeightedAVGCurrent = WeightedAVG
                If FuelCostStockNext <> "" And FuelCostStock <> FuelCostStockNext Then

                    If IsDBNull(MyRow(ColumnAVG)) Then
                        WeightedAVGCurrent = 0
                    End If
                End If
            ElseIf Not IsDBNull(MyRow(ColumnAVG)) Then
                WeightedAVGCurrent = MyRow(ColumnAVG)
            End If

            If WeightedAVGCurrent > 0 Then
                QueryString = "SELECT [" & ColumnAVGNext & "] FROM FuelCostWeightedAVGByMonth  Where Stock_Number = '" & FuelCostStock & "' AND Year = '" & FuelCostYearCurrent & "' AND Month = '" & FuelCostMonth & "'"
                Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
                For Each MyRowAVG In MyDataSet.Tables("AnswerSet").Rows
                    If IsDBNull(MyRowAVG(ColumnAVGNext)) Then
                        WeightedAVG = WeightedAVGCurrent
                        QueryString = "Update fuelcostweightedavgbymonth SET Weighted_AVG  = '" & WeightedAVGCurrent & "',  [" & ColumnAVGNext & "] =   '" & WeightedAVGCurrent & "' WHERE Stock_Number = '" & FuelCostStock & "' AND Month = '" & FuelCostMonth & "' AND Year = '" & FuelCostYearCurrent & "';"
                        Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)
                        FuelCostStockNext = FuelCostStock
                    End If
                Next
                MyDataSet = Nothing
                MyRow = Nothing

            End If

        Next
        MyDataSet = Nothing
        MyRow = Nothing

    End Sub

Select all Open in new window