Skip to content

Instantly share code, notes, and snippets.

@totty90
Last active August 29, 2015 14:17
Show Gist options
  • Save totty90/5be77522916d87e904ff to your computer and use it in GitHub Desktop.
Save totty90/5be77522916d87e904ff to your computer and use it in GitHub Desktop.
Complex SQL queries
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)
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
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'
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment