Need help mixing two queries in one...
-
The first CTE can probable be simplified to:
SELECT tTasks.invoiceId ,CASE WHEN tTasks.taskUseId = 1 THEN COALESCE(tTasks.price,tReferencePricesForTasks.price) ELSE 0 END \* (ROUND(TIME\_TO\_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal FROM tTasks JOIN tReferencePricesForTasks ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId AND tReferencePricesForTasks.projectId = tTasks.projectId AND tReferencePricesForTasks.userId = tTasks.userId WHERE tTasks.taskUseId <> 3 UNION ALL SELECT tExpenses.invoiceId ,ROUND(COALESCE(tExpenses.price,tReferencePricesForExpenses.preu), 2) \* round(tExpenses.quantity, 2) AS subTotal FROM tExpenses JOIN tReferencePricesForExpenses ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND tReferencePricesForExpenses.projectId = tExpenses.projectId AND tReferencePricesForExpenses.userId = tExpenses.userIdI would look over the roundings though
Wrong is evil and must be defeated. - Jeff Ello
First of all, thank you very much for your answer! I've seen the CTE concept in the Internet... it would make this a little bit easier... the problem is that PHPMyAdmin doesn't like the clause "WITH" and therefore I can't test it... X| I'm thinking of storing the total price value into a new column of the invoice table and recalculate it after creating it or each time I modify it... It probably will be much faster when getting all the data and I can't see why it would be a bad design... but I'm super novice... Would it be something terrible? And I'm curious... why would you look at the roundings? Thanks again! :thumbsup:
www.robotecnik.com[^] - robots, CNC and PLC programming
-
First of all thank you very much for your answer! PHPMyAdmin doesn't know the word "WITH"... X| Can't even try it... I'm trying to avoid the need to do two queries while populating a table... Would it be a bad design to add that totalInvoicePrice into the table tInvoices? that way at the moment of creating the invoice I would do what it's working now... and when I would need to get the data it would be extremely easy...
www.robotecnik.com[^] - robots, CNC and PLC programming
-
Try inserting a semicolon just before
WITHkeyword:;WITH ctePrices AS...
while (!(success = Try()));
-
Exactly the same... :~
www.robotecnik.com[^] - robots, CNC and PLC programming
-
First of all thank you very much for your answer! PHPMyAdmin doesn't know the word "WITH"... X| Can't even try it... I'm trying to avoid the need to do two queries while populating a table... Would it be a bad design to add that totalInvoicePrice into the table tInvoices? that way at the moment of creating the invoice I would do what it's working now... and when I would need to get the data it would be extremely easy...
www.robotecnik.com[^] - robots, CNC and PLC programming
Why not create a view in the database that includes the sum of the items, no need to update the value as it will calc every time you reference the view.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
That was a long shot; and I think it's MSSQL syntax anyway, so I'm afraid I was off-topic on this one. Sorry for that.
while (!(success = Try()));
-
First of all, thank you very much for your answer! I've seen the CTE concept in the Internet... it would make this a little bit easier... the problem is that PHPMyAdmin doesn't like the clause "WITH" and therefore I can't test it... X| I'm thinking of storing the total price value into a new column of the invoice table and recalculate it after creating it or each time I modify it... It probably will be much faster when getting all the data and I can't see why it would be a bad design... but I'm super novice... Would it be something terrible? And I'm curious... why would you look at the roundings? Thanks again! :thumbsup:
www.robotecnik.com[^] - robots, CNC and PLC programming
So inline the CTEs then:
SELECT tInvoices.*
,tCustomers.name
,cteTotals.total
FROM tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN (
SELECT invoiceId
,SUM(subTotal) AS total
FROM (
SELECT tTasks.invoiceId
,CASE
WHEN tTasks.taskUseId = 1
THEN COALESCE(tTasks.price, tReferencePricesForTasks.price)
ELSE 0
END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
FROM tTasks
JOIN tReferencePricesForTasks ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
WHERE tTasks.taskUseId <> 3UNION ALL SELECT tExpenses.invoiceId ,ROUND(COALESCE(tExpenses.price, tReferencePricesForExpenses.preu), 2) \* round(tExpenses.quantity, 2) AS subTotal FROM tExpenses JOIN tReferencePricesForExpenses ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND tReferencePricesForExpenses.projectId = tExpenses.projectId AND tReferencePricesForExpenses.userId = tExpenses.userId ) ctePrices GROUP BY invoiceId ) cteTotals ON cteTotals.invoiceId = tInvoices.idThe problem I have with rounding is that you're rounding to early, and even using a product of two roundings in the sum. That way you're creating a rounding error: Round-off error - Wikipedia[^]
Wrong is evil and must be defeated. - Jeff Ello
-
Why not create a view in the database that includes the sum of the items, no need to update the value as it will calc every time you reference the view.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
I thought of using a view, but at the end I think it will even be better storing the value inside the same invoices table... I don't need recalculating the value... It is only calculated once the invoice is made. Thank you very much for your answer!
www.robotecnik.com[^] - robots, CNC and PLC programming
-
So inline the CTEs then:
SELECT tInvoices.*
,tCustomers.name
,cteTotals.total
FROM tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN (
SELECT invoiceId
,SUM(subTotal) AS total
FROM (
SELECT tTasks.invoiceId
,CASE
WHEN tTasks.taskUseId = 1
THEN COALESCE(tTasks.price, tReferencePricesForTasks.price)
ELSE 0
END * (ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)) AS subTotal
FROM tTasks
JOIN tReferencePricesForTasks ON tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
WHERE tTasks.taskUseId <> 3UNION ALL SELECT tExpenses.invoiceId ,ROUND(COALESCE(tExpenses.price, tReferencePricesForExpenses.preu), 2) \* round(tExpenses.quantity, 2) AS subTotal FROM tExpenses JOIN tReferencePricesForExpenses ON tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND tReferencePricesForExpenses.projectId = tExpenses.projectId AND tReferencePricesForExpenses.userId = tExpenses.userId ) ctePrices GROUP BY invoiceId ) cteTotals ON cteTotals.invoiceId = tInvoices.idThe problem I have with rounding is that you're rounding to early, and even using a product of two roundings in the sum. That way you're creating a rounding error: Round-off error - Wikipedia[^]
Wrong is evil and must be defeated. - Jeff Ello
-
First of all thank you very much for your answer! PHPMyAdmin doesn't know the word "WITH"... X| Can't even try it... I'm trying to avoid the need to do two queries while populating a table... Would it be a bad design to add that totalInvoicePrice into the table tInvoices? that way at the moment of creating the invoice I would do what it's working now... and when I would need to get the data it would be extremely easy...
www.robotecnik.com[^] - robots, CNC and PLC programming
Well, that's a problem with php, not mySQL.
Wrong is evil and must be defeated. - Jeff Ello
-
It looks incredibly easy once you see it! I'll try it! Thank you very much! :beer:
www.robotecnik.com[^] - robots, CNC and PLC programming
It is. The purpose of a CTE is to make the code more readable. when the query is analyzed by the optimizer all CTEs are inlined.
Wrong is evil and must be defeated. - Jeff Ello
-
The WITH clause appears in MySQL manuals... so it should definitely work... but it doesn't who knows why... Thank you very much for trying it! :thumbsup:
www.robotecnik.com[^] - robots, CNC and PLC programming
what! no no no lets not start that one
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
what! no no no lets not start that one
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
what! no no no lets not start that one
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Start it please!
www.robotecnik.com[^] - robots, CNC and PLC programming
Abbott & Costello Who's On First - YouTube[^]
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Hello, First of all sorry for putting like a code dump here... I'll try to explain it as clearly as possible, but given my inexperience with SQL (years and years without touching it) I think it is better if I post the two queries completely rather than asking a generic question. I have two SQL queries that work well. I need to mix them and I don't know how to do it. The first one returns me all the details of the Invoice and the customer name, and the second one gives me the invoice cost after making the right selections and calculations (more on this after). tInvoices fields are: id, invoiceNumber, invoiceSeries, date, customerId, notSent tCustomers fields are: id, name, ... And the first query is simple:
SELECT tInvoices.*, tCustomers.name FROM tInvoices INNER JOIN tCustomers ON CustomerId = tCustomers.id;
That way I get all the invoice details and the customer name in each row to show it in an HTML table. But I also want to put the invoice price in that table and this is much more complicated: I have a structure in which there is a reference price per customer, project and user... this means that depending on what's agreed with each customer for each project, each user will have a cost... If I go to a customer company to program something I could get X€ and other guy from the company could get Y€. And those proces could be V€ and W€ for another project for the same customer... Appart of that I've set in each task a special cost field. If that cost is filled (not NULL) then I'll use that cost as an exception (again something pacted with the customer). All this said... this happens with the tasks (work done in a project by a human being) and with the expenses (hotel, gasoline, meals... tickets in general). These are the tables involved: tTasks fields are: id, projectId, userId, taskTypeId, taskUseId, invoiceId, date, startTime, endTime, price, notes tReferencePricesForTasks fields are: projectId, userId, taskTypeId, price tTasksTypes fields are: id, name tExpenses fields are: id, projectId, userId, expenseTypeId, invoiceId, date, quantity, price, notes tReferencePricesForExpenses fields are: projectId, userId, espenseTypeId, price tExpensesTypes fields are: id, name, allowEditingAmounts And the second query is not that simple, but seems to work well:
SELECT SUM(subTotal) AS total FROM
(
SELECT
(