Complex SQL queries

README

The fields of the table names are all messed up, but I cannot change them. This software has been done in 2013 in a rush with almost no VBA experience, I'm not really proud of how is written but is very flexible and useful. (Runs in MS ACCESS)

Generated query

SELECT cod_fam, 
       nome_fam, 
       id_vendedor, 
       nome_vendedor, 
       id_zona, 
       conta_cli, 
       sub_conta_cli, 
       nome_cli, 
       Sum(venda1)                                     AS VENDAS1, 
       Sum(venda2)                                     AS VENDAS2, 
       Round(Iif(Sum(venda1) = 0, 9999, (( Sum(venda2) - Sum(venda1) )) / Abs( 
                                        Sum( 
                                        venda1)) * 
                                              100), 2) AS PER_DIFF 
FROM  (SELECT quarter, 
              month, 
              cod_fam, 
              nome_fam, 
              id_vendedor, 
              nome_vendedor, 
              id_zona, 
              conta_cli, 
              sub_conta_cli, 
              nome_cli, 
              venda AS VENDA1, 
              0     AS VENDA2 
       FROM   stkqry_vendas07_fam_month_vnd_cli_f1 
       WHERE  year = '2014' 
              AND month = '01' 
              AND cod_fam LIKE 'A*' 
              AND id_vendedor = '05 ' 
       UNION ALL 
       SELECT quarter, 
              month, 
              cod_fam, 
              nome_fam, 
              id_vendedor, 
              nome_vendedor, 
              id_zona, 
              conta_cli, 
              sub_conta_cli, 
              nome_cli, 
              0     AS VENDA1, 
              venda AS VENDA2 
       FROM   stkqry_vendas07_fam_month_vnd_cli_f1 
       WHERE  year = '2015' 
              AND month = '01' 
              AND cod_fam LIKE 'A*' 
              AND id_vendedor = '05 ') 
GROUP  BY cod_fam, 
          nome_fam, 
          id_vendedor, 
          nome_vendedor, 
          id_zona, 
          conta_cli, 
          sub_conta_cli, 
          nome_cli 
HAVING ( Sum(venda1) > 1000 
          OR Sum(venda2) > 1000 ) 
ORDER  BY nome_vendedor, 
          nome_cli, 
          vendas2 DESC 

Generated query description

In Portuguese: Comparar o 01º mês de 2014 com o 01º mês de 2015 com valores superiores a 1000€ seleccionando as pequenas familias que começam por 'A' do vendedor XXX em todas as zonas. Tabela usada: 'XXX'

In English: Compare the 01º month of 2014 with the 01º month of 2015 with amounts exceeding € 1,000 selecting small families starting with 'A' of the seller XXX in all areas. Table used: 'XXX'

partial VBA for query

Sub makeQuery()
    Dim query As String
    Dim title As String
    Dim describeQuery As String
    
    Dim TableName As String
    Dim y1m1 As String
    Dim y2m2 As String
    
    Dim selectFieldsCollection As New Collection
    Dim selectFieldsString As String
    Dim groupByFieldsCollection As New Collection
    Dim groupByFieldsString As String
    Dim dummySelectColl As New Collection
    Dim dummySelectString As String
    Dim havingCollection As New Collection
    Dim havingString As String
    Dim subSelectFieldsCollection As New Collection
    Dim subSelectFieldsString As String
    Dim subFilters1Collection As New Collection
    Dim subFilters1String As String
    Dim subFilters2Collection As New Collection
    Dim subFilters2String As String
    
    Dim tableFamilyModifier As String
    Dim tableTimePeriodModifier As String
    Dim tableSellerModifier As String
    Dim tableCustomerModifier As String
    Dim tableEnterpriseModifier As String

    TableName = "STKQRY_VENDAS07"
    describeQuery = ""
    
    ' TIME PERIOD
    If valuesAreAccumulated Then
        describeQuery = describeQuery & "Comparar o acumulado do"
    Else
        describeQuery = describeQuery & "Comparar o"
    End If
    subFilters1Collection.Add "year = '" & getYear1 & "'"
    subFilters2Collection.Add "year = '" & getYear2 & "'"
    If getTimeInterval = "month" Then
        tableTimePeriodModifier = "_MONTH"
        subSelectFieldsCollection.Add "quarter"
        subSelectFieldsCollection.Add "month"
        describeQuery = describeQuery & " " & getMonth1 & "º mês de " & getYear1 & " com o " & getMonth2 & "º mês de " & getYear2 & ""
    ElseIf getTimeInterval = "quarter" Then
        tableTimePeriodModifier = "_QUARTER"
        subSelectFieldsCollection.Add "quarter"
        describeQuery = describeQuery & " " & getQuarter1 & "º trimestre de " & getYear1 & " com o " & getQuarter2 & "º trimestre de " & getYear2 & ""
    ElseIf getTimeInterval = "year" Then
        tableTimePeriodModifier = "_YEAR"
        describeQuery = describeQuery & " ano " & getYear1 & " com o ano " & getYear2 & ""
    End If
    
    ' MINUMUM VALUE
    If getMinimumValue > 0 Then
        describeQuery = describeQuery & " com valores superiores a " & getMinimumValue & "€"
        havingCollection.Add "(SUM(VENDA1) > " & getMinimumValue & " OR SUM(VENDA2) > " & getMinimumValue & ")"
    Else
        describeQuery = describeQuery & " incluindo todos os valores"
    End If
    
    ' ACCUMULATED
    If valuesAreAccumulated Then
        If getTimeInterval = "month" Then
            subFilters1Collection.Add "Month between '00' and '" & getMonth1 & "' "
            subFilters2Collection.Add "Month between '00' and '" & getMonth2 & "' "
        ElseIf getTimeInterval = "quarter" Then
            subFilters1Collection.Add "quarter between '0' and '" & getQuarter1 & "' "
            subFilters2Collection.Add "quarter between '0' and '" & getQuarter2 & "' "
        ElseIf getTimeInterval = "year" Then
            ' none by default
        End If
    Else
        If getTimeInterval = "month" Then
            subFilters1Collection.Add "Month = '" & getMonth1 & "' "
            subFilters2Collection.Add "Month = '" & getMonth2 & "' "
        ElseIf getTimeInterval = "quarter" Then
            subFilters1Collection.Add "quarter = '" & getQuarter1 & "' "
            subFilters2Collection.Add "quarter = '" & getQuarter2 & "' "
        ElseIf getTimeInterval = "year" Then
            ' none by default
        End If
    End If
    
    describeQuery = describeQuery & " seleccionando"
    
    ' FAMILY
    If getFamilyType = "GFAM" Then
        describeQuery = describeQuery & " as grandes familias"
        tableFamilyModifier = "_GFAM"
        selectFieldsCollection.Add "cod_gfam as COD_FAM"
        selectFieldsCollection.Add "nome_gfam as NOME_FAM"
        groupByFieldsCollection.Add "cod_gfam"
        groupByFieldsCollection.Add "nome_gfam"
        subSelectFieldsCollection.Add "cod_gfam"
        subSelectFieldsCollection.Add "nome_gfam"
        If getCodFam <> "" Then
            describeQuery = describeQuery & " que começam por '" & getCodFam & "'"
            subFilters1Collection.Add "cod_gfam like '" & getCodFam & "*'"
            subFilters2Collection.Add "cod_gfam like '" & getCodFam & "*'"
        End If
    ElseIf getFamilyType = "FAM" Then
        describeQuery = describeQuery & " as pequenas familias"
        tableFamilyModifier = "_FAM"
        selectFieldsCollection.Add "COD_FAM"
        selectFieldsCollection.Add "NOME_FAM"
        groupByFieldsCollection.Add "cod_fam"
        groupByFieldsCollection.Add "nome_fam"
        subSelectFieldsCollection.Add "COD_FAM"
        subSelectFieldsCollection.Add "NOME_FAM"
        If getCodFam <> "" Then
            describeQuery = describeQuery & " que começam por '" & getCodFam & "'"
            subFilters1Collection.Add "cod_fam like '" & getCodFam & "*'"
            subFilters2Collection.Add "cod_fam like '" & getCodFam & "*'"
        End If
    ElseIf getFamilyType = "" Then
        tableFamilyModifier = ""
        dummySelectColl.Add "'' as COD_FAM"
        dummySelectColl.Add "'' as NOME_FAM"
    Else
        MsgBox "Familia invalida"
    End If
    
    ' SELLER
    Dim sellerId As String
    Dim sellerName As String
    Dim includeSellers As Boolean
    title = sellerName
    If getSellerFilterType = "As selected" Then
        includeSellers = True
        sellerId = getSellerId
        sellerName = getSellerName
        If sellerId = "ID" Then
            sellerId = ""
        End If
    ElseIf getSellerFilterType = "All" Then
        includeSellers = True
        sellerId = ""
        sellerName = "Total = "
    ElseIf getSellerFilterType = "None" Then
        includeSellers = False
        sellerId = ""
        sellerName = "Total = "
    Else
        MsgBox "Some error from seller filter happened."
    End If
    
    If includeSellers = True Then
        tableSellerModifier = "_VND"
        If sellerId <> "" Then
            describeQuery = describeQuery & " do vendedor " & OneSpace(sellerName) & ""
            subFilters1Collection.Add "ID_VENDEDOR='" & sellerId & "' "
            subFilters2Collection.Add "ID_VENDEDOR='" & sellerId & "' "
        Else
            describeQuery = describeQuery & " do todos os vendedores"
        End If
        selectFieldsCollection.Add "ID_VENDEDOR"
        selectFieldsCollection.Add "NOME_VENDEDOR"
        groupByFieldsCollection.Add "ID_VENDEDOR"
        groupByFieldsCollection.Add "NOME_VENDEDOR"
        subSelectFieldsCollection.Add "ID_VENDEDOR"
        subSelectFieldsCollection.Add "NOME_VENDEDOR"
    Else
        tableSellerModifier = ""
        dummySelectColl.Add "'' as ID_VENDEDOR"
        dummySelectColl.Add "'' as NOME_VENDEDOR"
    End If
    
    ' ZONE FILTER
    If getZoneId = "" Then
        tableCustomerModifier = ""
        dummySelectColl.Add "'' as id_zona"
        dummySelectColl.Add "'' as CONTA_CLI"
        dummySelectColl.Add "'' as SUB_CONTA_CLI"
        dummySelectColl.Add "'' as NOME_CLI"
    Else
        tableCustomerModifier = "_CLI"
        
        If getZoneId = "*" Then
            describeQuery = describeQuery & " em todas as zonas"
        Else
            describeQuery = describeQuery & " na zona '" & OneSpace(getZoneId) & "'"
            subFilters1Collection.Add "ID_ZONA='" & getZoneId & "' "
            subFilters2Collection.Add "ID_ZONA='" & getZoneId & "' "
        End If
        
        selectFieldsCollection.Add "ID_ZONA"
        selectFieldsCollection.Add "CONTA_CLI"
        selectFieldsCollection.Add "SUB_CONTA_CLI"
        selectFieldsCollection.Add "NOME_CLI"
        
        groupByFieldsCollection.Add "ID_ZONA"
        groupByFieldsCollection.Add "CONTA_CLI"
        groupByFieldsCollection.Add "SUB_CONTA_CLI"
        groupByFieldsCollection.Add "NOME_CLI"
        
        subSelectFieldsCollection.Add "ID_ZONA"
        subSelectFieldsCollection.Add "CONTA_CLI"
        subSelectFieldsCollection.Add "SUB_CONTA_CLI"
        subSelectFieldsCollection.Add "NOME_CLI"
    End If
    
    describeQuery = describeQuery & "."
    
    
    ' PRINTING
    Dim y1m1Label As String
    Dim y2m2Label As String
    If getTimeInterval = "month" Then
        y1m1Label = "y" & getYear1 & "_m" & getMonth1
        y2m2Label = "y" & getYear2 & "_m" & getMonth2
    ElseIf getTimeInterval = "quarter" Then
        y1m1Label = "y" & getYear1 & "_q" & getQuarter1
        y2m2Label = "y" & getYear2 & "_q" & getQuarter2
    ElseIf getTimeInterval = "year" Then
        y1m1Label = "y" & getYear1
        y2m2Label = "y" & getYear2
    End If
    
    y1m1 = "VENDAS1"
    y2m2 = "VENDAS2"
    'If printIt Then
    '    y1m1 = "VENDAS1"
    '    y2m2 = "VENDAS2"
    'Else
    '    y1m1 = y1m1Label
    '    y2m2 = y2m2Label
    'End If
    
    
    
    ' GENERATE PDF NAME
    Dim pdfNameColl As New Collection
    If getSellerFilterType = "As selected" Then
        pdfNameColl.Add Trim(getSellerName)
    End If
    
    pdfNameColl.Add "COMPARE"
    
    If getSellerFilterType = "All" Then
        pdfNameColl.Add "VND"
    End If
    
    If getFamilyType <> "" Then
        ' fam or gfam
        pdfNameColl.Add getFamilyType
    End If
    If getZoneId <> "" Then
        pdfNameColl.Add "CLI"
    End If
    
    If valuesAreAccumulated Then
        pdfNameColl.Add "ACUMUL"
    End If
    
    pdfNameColl.Add y1m1Label
    pdfNameColl.Add y2m2Label
    
    PdfName = Join(pdfNameColl, "_")
    
    
    
    tableEnterpriseModifier = "_F1"
    
    TableName = TableName & tableFamilyModifier & tableTimePeriodModifier & tableSellerModifier & tableCustomerModifier & tableEnterpriseModifier
    
    ' ORDER BY
    describeQuery = describeQuery & " Tabela usada: '" & TableName & "'"
    setDescribedSQL describeQuery
    
    selectFieldsString = Join(selectFieldsCollection, ", ")
    groupByFieldsString = Join(groupByFieldsCollection, ", ")
    dummySelectString = Join(dummySelectColl, ", ")
    If Len(dummySelectString) > 0 Then
        dummySelectString = ", " & dummySelectString
    End If
    
    subSelectFieldsString = Join(subSelectFieldsCollection, ", ")
    subFilters1String = Join(subFilters1Collection, " AND ")
    subFilters2String = Join(subFilters2Collection, " AND ")
    havingString = Join(havingCollection, " AND ")
    
    query = "SELECT " & selectFieldsString & ", SUM(VENDA1) AS " & y1m1 & ", SUM(VENDA2) AS " & y2m2
    query = query & ", ROUND(IIF(SUM(VENDA1)=0, 9999, ((SUM(VENDA2)-SUM(VENDA1)))/abs(SUM(VENDA1))*100), 2) AS PER_DIFF " & dummySelectString & "  FROM"
    
    query = query & "( SELECT " & subSelectFieldsString & ", VENDA AS VENDA1, 0 AS VENDA2 "
    query = query & "FROM " & TableName & " "
    
    query = query & "WHERE " & subFilters1String
    
    query = query & "UNION ALL "
    
    query = query & "SELECT " & subSelectFieldsString & ", 0 AS VENDA1, VENDA AS VENDA2 "
    query = query & "FROM " & TableName & " "
    
    query = query & "WHERE " & subFilters2String & " ) "
    
    If getCustomWhereQuery <> "" Then
        query = query & "WHERE " & getCustomWhereQuery & " "
    End If
    
    query = query & "GROUP BY " & groupByFieldsString & " "
    
    If havingString <> "" Then
        query = query & "HAVING " & havingString & " "
    End If
    
    If getOrderBy <> "" Then
        query = query & "ORDER BY " & getOrderBy
    End If
    
    Dim output As New Collection
    If title = "" Then
        title = "Vendas"
    End If
    
    report_query = query
    report_title = title
    report_subTitle = describeQuery
    report_leftCol = y1m1Label
    report_centerCol = y2m2Label
    report_rightCol = "Diff"
    report_pdfName = PdfName
End Sub