Using this SQL statement SELECT A_Sales_Ledger.Nominal_Code, NL_Codes.NL_Description, SUM(A_Sales_Ledger.DEbit), SUM(A_Sales_Ledger.Credit) FROM A_Sales_Ledger A_Sales_Ledger LEFT JOIN NL_Codes on A_Sales_Ledger.nominal_Code = NL_Codes.NL_Code WHERE Customer_ID = 49 group by Nominal_Code, NL_Codes.NL_Description Returns this I need to refine this query so that I can add the values for 0 to the values of 4105 and remove 0 from the output... Clearly I already have my Friday night head on as anything I try doesn't work :-) asked 08 Sep '17, 16:01 gchq Volker Barth |
Here's a past friday night approach: If you want to use a grouping that is not simply based on the values of one column but want to "merge" two groups into one, you can either
I do not understand your schema but here's an attempt:
In my understanding, that would mean you turn the left join into an inner join by replacing the join condition with one that joins the value 0 to 4105 and otherwise do the grouping as usually, somewhat like SELECT N.NL_Codes AS Nominal_Code, N.NL_Description, SUM(A.Debit), SUM(A.Credit) FROM A_Sales_Ledger A INNER JOIN NL_Codes N ON IF A.Nominal_Code = 0 THEN 4105 END IF = N.NL_Code WHERE Customer_ID = 49 GROUP BY N.NL_Codes, N.NL_Description
Note that then the according expression must also appear in the SELECT clause, and note that a GROUP BY expression does not allow an alias, such as SELECT IF Nominal_Code = 0 THEN 4105 ELSE Nominal_Code END IF AS Nominal_Code, ISNULL(N.NL_Description, 'Assessments-Regular') AS NL_Description, SUM(A.Debit), SUM(A.Credit) FROM A_Sales_Ledger A LEFT JOIN N NL_Codes N on A.Nominal_Code = N.NL_Code WHERE Customer_ID = 49 GROUP BY IF Nominal_Code = 0 THEN 4105 ELSE Nominal_Code END IF, ISNULL(N.NL_Description, 'Assessments-Regular') answered 10 Sep '17, 07:43 Volker Barth Ah, the preview showed both samples numbered with 1. and 2., don't know why the real view does not.
(10 Sep '17, 07:44)
Volker Barth
|
In the end I just manipulated the returned data - a cleaner method would have been preferable, but in this case only a few rows are returned so no real performance impact Dim vDebit As Decimal = 0 Dim vCredit As Decimal = 0 For Each Row As DataRow In MoveNominalDT.Rows If Row("Code") = 0 Then vDebit = Row("Debit") vCredit = Row("Credit") End If Next For Each Row As DataRow In MoveNominalDT.Rows If Row("Code") = vAssDefault Then Row("Credit") += vCredit Row("Debit") += vDebit End If Row("Balance") = Row("Debit") - Row("Credit") Next For Each Row As DataRow In MoveNominalDT.Rows Row("Selected") = False If Row("Debit") = Row("Credit") Then Row.Delete() End If Next MoveNominalDT.AcceptChanges() For Each Row As DataRow In MoveNominalDT.Rows If Row("Code") = 0 Then Row.Delete() End If Next MoveNominalDT.AcceptChanges() answered 08 Sep '17, 18:42 gchq |