strSql = "SELECT G6.Name,G6.AUM, SUM(G6.IHP)AS IHP, G6.AUM-ISNULL(SUM(G6.IHP), 0) AS NET_AUM, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession FROM (" & vbCrLf
For i = 0 To currencies.Count - 1
Dim curr As String = currencies(i)
strSql &= "SELECT G3.Name, G3.Average AS AUM, ISNULL(G4.Multiplication, 0)AS IHP, G4.ProdName, G3.Currency, G3.VAT, G3.mngmt_fee, G3.retrocession " & vbCrLf
strSql &= "FROM (SELECT cd_clients.Name, AVG(client_data.val) AS Average, cd_clients.Currency, cd_clients.VAT, cd_clients.mngmt_fee, cd_clients.Retrocession " & vbCrLf
strSql &= "FROM cd_clients INNER JOIN client_data ON cd_clients.id = client_data.cd_clientID where client_data.valDate Between '" & mindate & "' And '" & maxdate & "' " & vbCrLf
strSql &= "GROUP BY cd_clients.Name,cd_clients.Currency,cd_clients.VAT,cd_clients.mngmt_fee, cd_clients.Retrocession) AS G3 " & vbCrLf
strSql &= "LEFT JOIN (SELECT G2.Name, G1.ProdName, (SUM(G1.MonthlyAvg*G2.Pos)/3) AS Multiplication " & vbCrLf
strSql &= "FROM(SELECT cd_prod.name AS ProdName, AVG(prod_data." & curr & ") AS MonthlyAVG, MONTH(prod_data.valdate) AS MonthPart " & vbCrLf
strSql &= "FROM cd_prod INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID " & vbCrLf
strSql &= "WHERE YEAR(prod_data.valdate) = '" & cbYear.Text & "' AND MONTH(prod_data.valdate) BETWEEN " & mone & " AND " & mthree & " " & vbCrLf
strSql &= "GROUP BY MONTH(prod_data.valdate), cd_prod.name) AS G1 " & vbCrLf
strSql &= "LEFT JOIN (SELECT cd_clients.Name, pos_data.product as ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & dateone & "') AS MonthPart " & vbCrLf
strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' AND '" & dateone & "' " & vbCrLf
strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name " & vbCrLf
strSql &= "UNION ALL " & vbCrLf
strSql &= "SELECT cd_clients.Name, pos_data.product AS ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & datetwo & "') AS MonthPart " & vbCrLf
strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' AND '" & datetwo & "' " & vbCrLf
strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name " & vbCrLf
strSql &= "UNION ALL " & vbCrLf
strSql &= "SELECT cd_clients.Name, pos_data.product AS ProdName, SUM(cast(pos_data.Shares as Money)) AS POS, MONTH('" & maxdate & "') AS MonthPart " & vbCrLf
strSql &= "FROM pos_data RIGHT JOIN cd_clients on pos_data.reference = cd_clients.Name " & vbCrLf
strSql &= "WHERE pos_data.period BETWEEN '12/31/2001' And '" & maxdate & "' " & vbCrLf
strSql &= "GROUP BY pos_data.reference, pos_data.product, cd_clients.Name) AS G2 on G2.ProdName = G1.ProdName AND G2.MonthPart = G1.MonthPart " & vbCrLf
strSql &= "GROUP BY G1.ProdName, G2.Name) AS G4 on G3.Name = G4.Name "
strSql &= "INNER JOIN (select Column_name from information_schema.columns where Column_name ='" & curr & "') As G5 on Column_name = G3.Currency" & vbCrLf
If i = currencies.Count - 1 Then
IsFirstSubquery = False
Else
strSql &= "UNION ALL " & vbCrLf
End If
Next i
strSql &= ")As G6 GROUP BY G6.Name,G6.AUM, G6.Currency, G6.VAT, G6.mngmt_fee, G6.retrocession ORDER BY G6.Name"