December 21, 2018 at 9:46 am
Please, I need your kind help. I have the below three tables, and I want to calculate the total amount with VAT and without VAT for each product bills in a date range. I'm not getting the right result. "products duplicated."
Bill Table columns
1- BillNumber PK
2- BillDate
3. Amount
4. VAT
Bill Item Table
1. BillItemID PK
2. BillNumber FK
3. ProductID FK
Product Table
1. ProductId PK
SELECT p.ProductNam,
COUNT(b.BillNumber) as TotalOperations,
SUM((b.Amount + b.VAT) - b.Discount) as TotalWithVAT,
SUM(b.Vat) as TotalVAT,
SUM((b.Amount - b.Vat) - b.Discount) as TotalWithoutVAT
FROM [Bills].[Bill] b
INNER JOIN
[Bills].[BillItem] i
ON b.BillNumber = i.BillNumber
INNER JOIN
[Products].[Product] p
ON i.ProductId = p.ProductId
WHERE b.BillDate >= @FromDate
AND b.BillDate <= @ToDate
GROUP BY p.ProductName,
b.Discount,
b.Vat
result :
product name Totaloperations TotalwithVAT TotalVAT ToyalWithouVAT
Product A 50 22 3 19
Product A 11 14 0 14
Product B 42 67 0 67
December 21, 2018 at 10:21 am
Can a bill row have more than one bill item rows?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2018 at 10:33 am
Phil Parkin - Friday, December 21, 2018 10:21 AMCan a bill row have more than one bill item rows?
Thank you for the info. I did perform a google search with no luck. Best regards.
December 21, 2018 at 11:03 am
Hello,
Without populating tables to test your particular example-- When dealing with unexpected rows, a good clue is that p.ProductName is your only SELECT'ed column which is not also an aggregate function like a COUNT or SUM.
This typically means this column should also become your only GROUP. Your Product A is repeated because of the existence of multiple differing values for Product A in b.Discount or b.VAT. You should take those columns out of the GROUP BY clause, so that multiple differing b.Discount or b.VAT only affect your math, and do not affect the resultant GROUPs.
...GROUP BY p.ProductName
December 21, 2018 at 11:14 am
sami Jafaar - Friday, December 21, 2018 10:33 AMPhil Parkin - Friday, December 21, 2018 10:21 AMCan a bill row have more than one bill item rows?Thank you for the info. I did perform a google search with no luck. Best regards.
Huh?
You have a table called Bill and one called BillItem. Is this a one-to-many relationship?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2018 at 12:32 pm
Phil Parkin - Friday, December 21, 2018 11:14 AMsami Jafaar - Friday, December 21, 2018 10:33 AMPhil Parkin - Friday, December 21, 2018 10:21 AMCan a bill row have more than one bill item rows?Thank you for the info. I did perform a google search with no luck. Best regards.
Huh?
You have a table called Bill and one called BillItem. Is this a one-to-many relationship?
Yes, Bill must have at least one row in the Bill Item table (one-to-many)
December 21, 2018 at 12:37 pm
tb_db_gg - Friday, December 21, 2018 11:03 AMHello,Without populating tables to test your particular example-- When dealing with unexpected rows, a good clue is that p.ProductName is your only SELECT'ed column which is not also an aggregate function like a COUNT or SUM.
This typically means this column should also become your only GROUP. Your Product A is repeated because of the existence of multiple differing values for Product A in b.Discount or b.VAT. You should take those columns out of the GROUP BY clause, so that multiple differing b.Discount or b.VAT only affect your math, and do not affect the resultant GROUPs.
...GROUP BY p.ProductName
But if I used ProductName only in the Group By I'm getting an error message "invalid in the select list because it is not contained in either aggregate function or the GROUP by clause".
December 21, 2018 at 12:42 pm
If the code you posted is literally the code you are running: the issue would be that you've SELECT'ed "p.ProductNam" and not "p.ProductName".
December 21, 2018 at 12:43 pm
sami Jafaar - Friday, December 21, 2018 12:32 PMPhil Parkin - Friday, December 21, 2018 11:14 AMsami Jafaar - Friday, December 21, 2018 10:33 AMPhil Parkin - Friday, December 21, 2018 10:21 AMCan a bill row have more than one bill item rows?Thank you for the info. I did perform a google search with no luck. Best regards.
Huh?
You have a table called Bill and one called BillItem. Is this a one-to-many relationship?Yes, Bill must have at least one row in the Bill Item table (one-to-many)
There is your problem.
The join is creating multiple rows for a single Bill row. And you are summing values from the Bill table, so they are added together as many times as there are rows in the BillItem table.
Can you change your query such that it SUMs values from the BillItem table, rather than the Bill table?
If a Bill has multiple BillItem rows relating to different products, you must change your query to SUM values from BillItem, otherwise these values will not be split by product.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2018 at 12:48 pm
Phil Parkin - Friday, December 21, 2018 12:43 PMsami Jafaar - Friday, December 21, 2018 12:32 PMPhil Parkin - Friday, December 21, 2018 11:14 AMsami Jafaar - Friday, December 21, 2018 10:33 AMPhil Parkin - Friday, December 21, 2018 10:21 AMCan a bill row have more than one bill item rows?Thank you for the info. I did perform a google search with no luck. Best regards.
Huh?
You have a table called Bill and one called BillItem. Is this a one-to-many relationship?Yes, Bill must have at least one row in the Bill Item table (one-to-many)
There is your problem.
The join is creating multiple rows for a single Bill row. And you are summing values from the Bill table, so they are added together as many times as there are rows in the BillItem table.
Can you change your query such that it SUMs values from the BillItem table, rather than the Bill table?If a Bill has multiple BillItem rows relating to different products, you must change your query to SUM values from BillItem, otherwise these values will not be split by product.
But I need to SUM the total amount of VAT also, which exists in the Bill table only, so how can I handle that?
December 21, 2018 at 1:03 pm
If your schema is as-posted, then no Amount exists in the Bill Item table for you to SUM. If I understand correctly, you are correct to SUM(Bill.Amount). So the correct columns other than "ProductNam" are being SELECT'ed, and you'll just need to play with your math.
I suspect that Bill.Amount - Bill.VAT isn't what you want in column "TotalWithoutVat", because I can't think of a use case for subtracting the VAT. But it's up to you if there's a case.
Bill Table columns
1- BillNumber PK
2- BillDate
3. Amount<<<
4. VAT
Bill Item Table
1. BillItemID PK
2. BillNumber FK
3. ProductID FK
Product Table
1. ProductId PK2. productName
December 21, 2018 at 1:23 pm
As a side note, the schema here is confusing and different from what would usually support an itemized bill. More normally:
Bill (id, billdate, processdate, etc)
->
Many BillItem (billID, Amount, ProductID, VAT)
Here though, you have:
Bill (id, date, Amount, VAT)
->
Many BillItem (id, billID, productid)
Because of Value/VAT living on Bill, It's structured such that there is no way to break apart the Bill into line-items with a cost per-line-item. You can still do the query you want, but you wouldn't be able to show a conventional itemized bill.
December 24, 2018 at 10:37 am
>> I have the below three tables, and I want to calculate the total billing_amt with and without vat_amt for each product bills in a date range. I'm not getting the right result. "products duplicated." <<
Why did you fail to post DDL? Why did you invent your own language? Like you think columns in a table are referenced by a sequential number? What do your foreign keys reference? Why is everything nullable?
Here’s my attempt at correcting your personal programming language.
CREATE TABLE Products
(product_gtin CHAR(15) NOT NULL PRIMARY KEY,
product_name VARCHAR(25) NOT NULL);
See how tables have to have a primary key and the primary key must be not null? I’m going to go ahead and use the GTIN to identify your products. I assume your company prefers to use industry standards instead of inventing their own.
CREATE TABLE Billings
(billing_nbr CHAR(10) NOT NULL PRIMARY KEY
billing_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
billing_amt DECIMAL (12,2) NOT NULL
CHECK(billing_amt >= 0.00),
vat_amt DECIMAL (12,2) NOT NULL
CHECK(vat_amt >= 0.00)
);
Notice the data types, a default timestamp and constraints on what I assume are currency amounts. apparently your VAT is computed at the billing level instead of at the details. The billings are what is called a strong entity. It exist by itself, but the details of the billing are what is called a weak entity; they have to have a strong entity in order to exist. This is where the reference clauses come in
CREATE TABLE Billing_Details
(billing_nbr CHAR(10) NOT NULL
REFERENCES Billings (billing_nbr)
ON DELETE CASCADE, --- important!!
product_gtin CHAR(15) NOT NULL
REFERENCES Products(product_gtin)
ON UPDATE CASCADE
ON UPDATE CASCADE
);
Where is the discount amount in any of these tables? It looks kind of like you may have put it in with the billing, but it could just as well have gone in the details.
SELECT P.product_name,
COUNT(B.billing_nbr) AS operation_cnt,
SUM(B.billing_amt + B.vat_amt - B.discount_amt)
AS withvat_billing_amt_tot,
SUM(B.vat_amt) AS vat_amt_tot,
SUM(B.billing_amt – B.vat_amt - B.discount_amt)
AS billing_amt_tot
FROM Billings AS B,
Billing_Details AS D,
Products AS P
WHERE B.billing_nbr = D.billing_nbr
AND D.product_gtin = P.product_gtin
AND B.billing_date BETWEEN @in_from_date AND @in_to_date
GROUP BY P.product_name;
why do you think that showing is the result of your query, when we have no sample data, will be helpful? There’s an old George Carlin comedy routine about baseball scores in which he simply announces scores without telling you who the teams are or where the games were or anything else
Please post DDL and follow ANSI/ISO standards when asking for help.
December 26, 2018 at 3:48 pm
Can you post your CREATE TABLE and INSERT scripts so we have a little bit of sample data? Invoices and . InvoiceLineItem structures are pretty standard. If you post the scripts for those two, this should be really easy.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply