Solved How to get the unique rows in sql server report (1 Viewer)

N

nector

Member
Local time
Today, 09:46
Joined
Jan 21, 2020
Messages
368
How do we get a unique record from sql server relating to a primary key without any duplicate on the report, I have been using the code below but if the user creates two or three lines with the same product name , unit price and total only one record will appear instead of all. Why it does not follow the primary key? For example CustomerInvoiceID is unique so how do I make sure that the foreign keys with three lines created for the same product can be treated as unique so that those lines are not left out.

Users in many times tend to create 4, 5,8 relating to one thing especially when using the POS (point of sales) , and so with the code below it will only pick one line and considers others as duplicates which is not correct. Access allow to use unique record not just values or Distinct Row


Code: 
SELECT DISTINCT
                  tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
                  * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
                  0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
                  * COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
                  * COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
                  ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
                  * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
                  (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
                  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
                  0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
                  0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
                  * COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
                  * Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
                  ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
                  tblCustomerInvoice.StatusOne
FROM     ((tblCustomers INNER JOIN
                  tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
                  (tblProducts INNER JOIN
                  tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
                  tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID;
 
Last edited:
N

nector

Member
Local time
Today, 09:46
Joined
Jan 21, 2020
Messages
368
What I'm saying is as below:

Invoice header table

Invoice no. 01



Details table
Inv. Product name qty total
01 Mango 4 4
01 Mango 4 4
01 Mango 4 4
01 Mango 4 4

Total 16 16

But the code above will sum as below:

Product name qty total
Mango 4 4

The rest will considered as duplicate, that is where problem
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:46
Joined
Aug 30, 2003
Messages
36,125
Did you try the method from the link? Given your example above:

SELECT Invoice, Sum(Total) As SumTotal
FROM DetailsTable
GROUP BY Invoice

That's just the basics, you can add the other table and fields as desired. The key is to group by the fields you want to be unique and sum the others.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 07:46
Joined
Jul 21, 2014
Messages
2,280
pbaldy has given you the answer to your direct question, but separately I am curious about some other things:
nector said:
Code: 
SELECT DISTINCT
                  tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
                  * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
                  0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
                  * COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
                  * COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
                  ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
                  * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
                  (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
                  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
                  0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
                  0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
                  * COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
                  * Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
                  ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
                  tblCustomerInvoice.StatusOne
FROM     ((tblCustomers INNER JOIN
                  tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
                  (tblProducts INNER JOIN
                  tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
                  tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID;
Click to expand...
What software did you use to create this query? Is it in Access?

Did you write it by hand or use a query builder?

Is the formatting done by you, or did the software format it like that?

Will you use a pass-through to use a the report's RecordSource, or are you using linked tables and running the query in Access? (I guess pass-through, since COALESCE() won't work in Access SQL)
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:46
Joined
Aug 30, 2003
Messages
36,125
Note this was posted in the SQL Server forum.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 07:46
Joined
Jul 21, 2014
Messages
2,280
I know that, but there is mention of a report, and the OP's previous posts have been about an application in Access
 
N

nector

Member
Local time
Today, 09:46
Joined
Jan 21, 2020
Messages
368
Can this work , sorry people I have no access to my server until 23.30 hours:


Code: 
SELECT
                  tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
                  * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
                  0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
                  * COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
                  * COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
                  ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
                  * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
                  (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
                  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
                  0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
                  0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
                  * COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
                  * Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
                  ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
                  tblCustomerInvoice.StatusOne
FROM     ((tblCustomers INNER JOIN
                  tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
                  (tblProducts INNER JOIN
                  tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
                  tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID,GROUP BY tblCustomerInvoice.InvoiceID
 
Minty

Minty

AWF VIP
Local time
Today, 07:46
Joined
Jul 26, 2013
Messages
10,371
No one is going to read that wall of SQL to spot problems. Please format it so it is readable:
Make an underlying query that produces the coalesced 0 values without any of the calculations.

Then within that make the simple VAT calculations only.

Use that query (As a sub query) to make the rest of the complicated calculations, and get rid of the IIF() statements, use CASE or Coalesce() instead. Nested IIfs are a pain to decipher at the best of time, and SQL only allows them because (Anyone any idea why they introduced them?).

It might be readable and at that point we can probably spot the error.

Also sort your joins out, the brackets and format are a hangover from copying from Access.
They couldn't be any easier to write in SSMS.

Just to highlight the issue - this is one calculation:

SQL: 
      (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),(([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue,
 
N

nector

Member
Local time
Today, 09:46
Joined
Jan 21, 2020
Messages
368
I wrote that SQL for two days and ensured that it is valid , see the validator below

www.coderstool.com

SQL Syntax Checker And SQL Syntax Validator

Validate SQL Syntax, indicate the incorrect syntax errors if any. Plus SQL formatting tool to beautify SQL statements. Simply load the complex query text and click Validate.
www.coderstool.com www.coderstool.com



Code: 
SELECT
                  tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
                  * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
                  0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
                  * COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
                  * COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
                  ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
                  * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
                  (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
                  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
                  0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
                  0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
                  * COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
                  * Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
                  ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
                  tblCustomerInvoice.StatusOne
FROM     ((tblCustomers INNER JOIN
                  tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
                  (tblProducts INNER JOIN
                  tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
                  tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID,GROUP BY tblCustomerInvoice.InvoiceID
 
Minty

Minty

AWF VIP
Local time
Today, 07:46
Joined
Jul 26, 2013
Messages
10,371
I didn't say it was invalid, I said it was unreadable, and therefore almost impossible to help you fix the underlying problem.

I'll show you how to make it more readable by refactoring it - but it will take me about 1/2 an hour, and I'm busy.
l'll come back to you.
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 07:46
Joined
Jul 21, 2014
Messages
2,280
Yes, it is valid, but it is utterly incomprehensible!!!

Can you really tell at a glance what your query is doing when you look at your SQL as posted?

Exploding the SQL in to fields helps only slightly, but there is plenty of room for reducing expressions that are repetitive.

Here is an 'exploded' view of the query:
EDIT: actually it's too large to post in a single post!!! :ROFLMAO: :oops: attaching as a separate file

At least you can see which fields are involved!

Besides the expression posted by @Minty, there are others which can be refined, eg:
SQL: 
-- ...
  IIf(
    (TaxClassA = 'I'),
    (
      (
        IIf(
          (TaxClassA <> 'I'),
          (
            (
              UnitPrice / (
                (
                  1 + (COALESCE(VAT, 0) + COALESCE(TourismLevy, 0))
                )
              )
            )
          ),
          (
            (
              UnitPrice / (1 + Insurance)
            )
          )
        )
      ) * Quantity
    ),
    (
      (
        (
          UnitPrice / (
            (
              1 + (COALESCE(VAT, 0) + COALESCE(TourismLevy, 0))
            )
          )
        ) * Quantity
      ) * COALESCE(tblCustomerInvoice.FCRate, 0)
    )
  ) AS ActualPrice,
-- ...
There is a whole nested IIF() statement that is unecessary - your first test whether TaxClassA = 'I', then immediately test whether TaxClassA <> 'I' which it can never be, since you are only in that part of the code since the first test is True.

This needs to be broken down into chunks. You can build all the chunks into a single query at the end.

First, just deal with aggregating your line details table, eg:
SQL: 
SELECT
  InvoiceID,
  SUM(Quantity) AS TotalQuantity,
  SUM(...) AS Total....
  -- do other calculations as required
FROM tblLineDetails
GROUP BY
  InvoiceID
Then join this to the rest of your query in place of tblLineDetails
 

Attachments

  • 20240213_nector_massive_query.zip
    1.5 KB · Views: 31
Minty

Minty

AWF VIP
Local time
Today, 07:46
Joined
Jul 26, 2013
Messages
10,371
I'm unable to test if this is correct, but it should give you the general idea. Just doing the a majority of the COALESCE statements outside the calculations makes it a lot more readable, that's before trying to breakdown the calculations into meaningful chucks.

You can nest queries like this without it hurting performance very much in SQL server, it knows how to optimise them.

As @cheekybuddha suggested, once you can see the wood for trees you should refactor some of those IIf() statements, and move some of the initial calcs into the inner base calcs query.

SQL: 
SELECT        BaseCalcs.*, 
                                    
            (((((([UnitPrice]) / (1 + CalVAT)) + 
            (IIf([UnitPrice] > CalRRP, (([UnitPrice] * CalVAT) / (1 + CalVAT)), ((CalRRP * CalVAT) / (1 + CalVAT))))) * [Quantity]) - ((((([UnitPrice]) / (1 + CalVAT)) + (IIf([UnitPrice] > CalRRP,(([UnitPrice] * CalVAT) / (1 + CalVAT)), ((CalRRP * CalVAT) / (1 + CalVAT))))) * [Quantity]) * CalDisc) - ((IIf((CalRRP > [UnitPrice]),((CalRRP * CalVAT) / (1 + CalVAT)), (([UnitPrice] * CalVAT) / (1 + CalVAT)))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice]) * CalDisc) - ((([UnitPrice] / (1 + CalVAT)) * [Quantity]) * CalDisc)) 
            AS Revenue,

            ((((IIf((RRP > UnitPrice), ((RRP * CalVAT) / (1 + CalVAT)), ((UnitPrice * COALESCE (VAT,0)) / (1 + CalVAT)))) * Quantity)) * CInv.FCRate) 
            AS FinalVat, 

            ((((((([UnitPrice]) / (1 + CalVAT)) + (IIf([UnitPrice] > CalRRP, (([UnitPrice] * CalVAT) / (1 + CalVAT)),
            ((CalRRP * CalVAT) / (1 + CalVAT))))) * [Quantity]) - (((((([UnitPrice]) / (1 + CalVAT)) + (IIf([UnitPrice] > CalRRP, (([UnitPrice] * CalVAT) / (1 + CalVAT)),
            ((CalRRP * CalVAT) / (1 + CalVAT))))) * [Quantity])) * CalDisc)) + (([Quantity] * [UnitPrice]) * CalDisc))) - ((([UnitPrice] / (1 + CalVAT)) * [Quantity]) * CalDisc) 
            AS Totals, 
            
            ((IIf(((RRP / ((1 + (CalVAT + CalLEVY)))) > ((UnitPrice / ((1 + (CalVAT + CalLEVY)))))), (RRP / ((1 + (CalVAT + CalLEVY)))) * (CalVAT), ((UnitPrice / ((1 + (CalVAT + CalLEVY))))) * VAT)) * Quantity) * CalFCRate AS ExVATTax, (((UnitPrice / ((1 + (CalVAT + CalLEVY))))) * Quantity) AS NetSales, ((1 + (CalVAT + CalLEVY))) 
            AS UnitNetTax,

            ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (CalVAT + CalLEVY))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (CalVAT + CalLEVY)))) * Quantity) * CalFCRate)) * CalLEVY)) AS FinTourism, ((UnitPrice / ((1 + (CalVAT + CalLEVY))))) 
            AS SellingPrice, 

            (RRP / ((1 + (CalVAT
            + CalLEVY)))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (CalVAT + CalLEVY))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (CalVAT + CalLEVY)))) * Quantity) * CalFCRate)) AS ActualPrice, 

            ((IIf((TaxClassA = 'i'),  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, 

            ((COALESCE (((UnitPrice / ((1 + (CalVAT + CalLEVY))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0) * COALESCE (TurnoverTax, 0)) AS TOTTax, 

            ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, 

            ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)* COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, 

            ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (CalVAT + CalLEVY))))), ((UnitPrice / (1 + Insurance))))) * Quantity) AS InsuranceVPD, 

            (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (CalVAT + CalLEVY))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, 

            ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable
    FROM 
        (
                tblCustomers.Company, 
                CInv.InvoiceID, 
                CInv.ShipDate, 
                CInv.Warehouse, 
                tblEfdReceipts.InvoiceNumber, 
                TLD.UnitPrice, 
                TLD.Bettings, 
                TLD.Insurance,
                TLD.Duty,
                TLD.VAT, 
                Coalesce (TLD.VAT,0) as CalVAT,
                TLD.ExciseClass,
                TLD.Discount, 
                COALESCE ([Discount], 0) as CalDisc
                TLD.TourismLevy, 
                COALESCE (TourismLevy, 0) as CalLEVY
                TLD.RRP,
                COALESCE ([RRP], 0) as CalRRP
                CInv.StatusOne
                CInv.InvoiceID AS SNR, 
                CInv.CreatedBy,                 
                CInv.FCRate, 
                COALESCE (CInv.FCRate,0) as CalFCRate
                CInv.Cashier, 
                TLD.TaxClassA
            
            FROM       tblCustomers 
                INNER JOIN tblCustomerInvoice CInv ON tblCustomers.CustomerID = CInv.CustomerID
                INNER JOIN tblLineDetails TLD ON CInv.InvoiceID = TLD.InvoiceID
                INNER JOIN tblProducts ON tblProducts.ProductID = TLD.ProductID
                INNER JOIN tblEfdReceipts ON CInv.InvoiceID = tblEfdReceipts.INVID
            
        ) as BaseCalcs        

        GROUP BY InvoiceID
 
N

nector

Member
Local time
Today, 09:46
Joined
Jan 21, 2020
Messages
368
Many thanks for help people , I will test it in the evening since the code does not change at all.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:46
Joined
Feb 19, 2002
Messages
43,275
Were you getting paid for all the unnecessary parentheses?
 
GPGeorge

GPGeorge

Grover Park George
Local time
Yesterday, 23:46
Joined
Nov 25, 2004
Messages
1,867
nector said:
Can this work , sorry people I have no access to my server until 23.30 hours:


Code: 
SELECT
                  tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
                  (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
                  * COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
                  0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
                  ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
                  * COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
                  * COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
                  ((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
                  * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
                  + COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
                  (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
                  ((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
                  0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
                  0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
                  * COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
                  * Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
                  ((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
                  tblCustomerInvoice.StatusOne
FROM     ((tblCustomers INNER JOIN
                  tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
                  (tblProducts INNER JOIN
                  tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
                  tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID,GROUP BY tblCustomerInvoice.InvoiceID
Click to expand...
Interestingly, none of the viewers of this post have access to your server either....
 
Minty

Minty

AWF VIP
Local time
Today, 07:46
Joined
Jul 26, 2013
Messages
10,371
Pat Hartman said:
Were you getting paid for all the unnecessary parentheses?
Click to expand...
Hopefully, by the pair.
I wasn't going to try and refactor those bits and remove them, way too many for my poor brain to cope with.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:46
Joined
Feb 19, 2002
Messages
43,275
Yet another thread this week where I can use my "cheaper by the dozen" line;)
 
You must log in or register to reply here.

Similar threads

N
Solved Access query with linked tables slow
Replies
3
Views
424
Pat Hartman
P
N
How to get reginal date and time correct with VBA
Replies
10
Views
705
Nixversteher
N
N
Queries Directed to Cloud SQL Server from Access Extreamely Slow
2
Replies
26
Views
1,305
gemma-the-husky
gemma-the-husky
V
A form using a query as a source doesn't refresh on changes in the table in some cases, and how to workaround this?
Replies
9
Views
355
Pat Hartman
P
N
Solved How to changes thousands of products prices in a retail business (The backed is SQL Server AZURE)
Replies
3
Views
344
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom