Generate the Aggregate Query

Listing 3. Class-level variables determine the T-SQL SELECT, WHERE, GROUP BY, and ORDER BY clauses' parameters. The current version of the project doesn't use Orders column data, which is intended for calculating average order amount.

Private Function WriteOrdersQuery() As String
   'Generate the T-SQL query from request document values
   Dim strSelect As String
   Dim strField As String
   Dim strHeader As String
   Dim strPeriod As String
   Dim strYear As String
   Dim datLastDate As DateTime
   Dim strFrom As String
   Dim strWhere As String
   Dim strGroup As String
   Dim strOrder As String
   Dim strSQL As String

   datStartDate = CDate(strStartDate)
   datLastDate = datStartDate
   strFrom = "FROM Customers " + _
      "INNER JOIN Orders ON Customers.CustID = _
      Orders.CustID " + _
      "INNER JOIN LineItems ON Orders.OrderID = _
      LineItems.OrderID "

   strSelect = "SELECT TOP 100 PERCENT "
   strYear = "DATEPART(year, OrderDate)"
   strWhere = "WHERE (OrderDate BETWEEN '" + _
      strStartDate + " " + strStartTime + _
      "' AND '"
   strGroup = "GROUP BY "
   strOrder = "ORDER BY "
   If blnStates Then
      strField = "State"
      strSelect += strField + ", "
   Else
      strField = "SUBSTRING(SKU, 1, 4)"
      strSelect += strField + " AS Category, "
   End If
   strGroup += strField + ", "
   strOrder += strField + ", "

   Select Case strInterval
      Case "Quarter"
         strHeader = "DATENAME(year, _
            OrderDate) + 'Q' + DATENAME(quarter, _
            OrderDate)"
         strPeriod = "DATEPART(quarter, _
            OrderDate)"
         datLastDate = datLastDate.AddMonths(3 * _
            CInt(strDataCols))
      Case "Month"
         strHeader = "SUBSTRING(DATENAME(month, _
            OrderDate), 1, 3) + ' ' + _
            DATENAME(year, OrderDate)"
         strPeriod = "DATEPART(month, OrderDate)"
         datLastDate = _
            datLastDate.AddMonths( _
            CInt(strDataCols))
      Case "Week"
         strHeader = "DATENAME(year, OrderDate) _
            + 'W' + DATENAME(week, OrderDate)"
         strPeriod = "DATEPART(week, OrderDate)"
         datLastDate = datLastDate.AddDays(7 * _
            CInt(strDataCols))
      Case "Day"
         strHeader = "CAST(DATEPART(mm, _
            OrderDate) AS varchar) + '/' + _
            CAST(DATEPART(dd, OrderDate) AS _
            varchar) + '/' + CAST(DATEPART(yy, _
            OrderDate) AS varchar)"
         strPeriod = "DATEPART(dy, OrderDate)"
         datLastDate = _
            datLastDate.AddDays(CInt(strDataCols))
   End Select
   datLastDate = datLastDate.AddSeconds(-1)
   strGroup += strPeriod + ", " + strYear + ", " _
      + strHeader + " "
   strOrder += strPeriod + ", " + strYear + ", " _
      + strHeader + " "
   strPeriod += " AS Period, " + strYear + " AS _
      Year "
   strSelect += strHeader + " AS Header, _
      COUNT(Orders.OrderID) AS Orders, _
      ROUND(SUM(Quantity * UnitPrice), 0) AS _
      Amount, " + strPeriod
   strWhere += Format(datLastDate, "M/d/yyyy _
      hh:mm:ss tt") + "') AND LineItems.Deleted = 0 "

   strSQL = strSelect + strCrLf + strFrom + _
      strCrLf + strWhere + strCrLf + strGroup + _
      strCrLf + strOrder
   Return strSQL
End Function