|
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
(
CASE WHEN tTasks.taskUseId = 1 THEN
(
CASE WHEN price IS NULL THEN
(
SELECT
tReferencePricesForTasks.price
FROM tReferencePricesForTasks
WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId AND
tReferencePricesForTasks.projectId = tTasks.projectId AND
tReferencePricesForTasks.userId = tTasks.userId
)
ELSE
price
END
)
ELSE
0
END
)
*
(
SELECT
ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600,2)
)
AS subTotal
FROM
tTasks
LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
WHERE tTasks.taskUseId <> 3 AND
tTasks.invoiceId = '.$PHP_Invoice_ID_VALUE.'
UNION ALL
SELECT
(
CASE WHEN price IS NULL THEN
(
SELECT
ROUND(tReferencePricesForExpenses.preu,2)
FROM tReferencePricesForExpenses
WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId AND
tReferencePricesForExpenses.projectId = tExpenses.projectId AND
tReferencePricesForExpenses.userId = tExpenses.userId
)
ELSE
ROUND(cost, 2)
END
)
*
round(tExpenses.quantity,2)
AS subTotal
FROM
tExpenses
LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
WHERE tExpenses.invoiceId = '.$PHP_Invoice_ID_VALUE.'
)
AS VIEW1As you can see in my second query, I'm using $PHP_Invoice_ID_VALUE which is a PHP variable that I update in each row of the first query to get the Invoice ID.
I'd like to mix both queries, but I don't know how to do it.
The idea is not needing to call the second query in each row... that way everything should be faster and I would be able to apply filters and orders given the price too...
I would like to substitute the PHP variables for each invoice listed in the first query.
Using Mysql and MariaDB.
Sorry for the code dump, but I truly don't know how to ask it generically without missing any detail.
Thank you very much for your help.
|
|
|
|
|
WITH ctePrices As
(
SELECT
tTasks.invoiceId,
(
CASE WHEN tTasks.taskUseId = 1 THEN
(
CASE WHEN price IS NULL THEN
(
SELECT tReferencePricesForTasks.price
FROM tReferencePricesForTasks
WHERE tReferencePricesForTasks.taskTypeId = tTasks.taskTypeId
AND tReferencePricesForTasks.projectId = tTasks.projectId
AND tReferencePricesForTasks.userId = tTasks.userId
)
ELSE
price
END
)
ELSE
0
END
)
*
(
ROUND(TIME_TO_SEC(TIMEDIFF(endTime, startTime)) / 3600, 2)
)
AS subTotal
FROM
tTasks
LEFT JOIN tTasksTypes ON tTasks.taskTypeId = tTasksTypes.id
WHERE
tTasks.taskUseId <> 3
UNION ALL
SELECT
tExpenses.invoiceId,
(
CASE WHEN price IS NULL THEN
(
SELECT ROUND(tReferencePricesForExpenses.preu,2)
FROM tReferencePricesForExpenses
WHERE tReferencePricesForExpenses.expenseTypeId = tExpenses.expenseTypeId
AND tReferencePricesForExpenses.projectId = tExpenses.projectId
AND tReferencePricesForExpenses.userId = tExpenses.userId
)
ELSE
ROUND(cost, 2)
END
)
*
round(tExpenses.quantity, 2)
AS subTotal
FROM
tExpenses
LEFT JOIN tExpensesTypes ON tExpenses.expenseTypeId = tExpensesTypes.id
),
cteTotals As
(
SELECT
invoiceId,
SUM(subTotal) As total
FROM
ctePrices
GROUP BY
invoiceId
)
SELECT
tInvoices.*,
tCustomers.name,
cteTotals.total
FROM
tInvoices
INNER JOIN tCustomers ON tInvoices.CustomerId = tCustomers.id
INNER JOIN cteTotals ON cteTotals.invoiceId = tInvoices.id
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
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.userId
I would look over the roundings though
|
|
|
|
|
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... 
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!
|
|
|
|
|
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 <> 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.userId
) ctePrices
GROUP BY invoiceId
) cteTotals ON cteTotals.invoiceId = tInvoices.id
The 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[^]
|
|
|
|
|
It looks incredibly easy once you see it!
I'll try it!
Thank you very much!
![Beer | [beer]](https://www.codeproject.com/script/Forums/Images/beer.gif)
|
|
|
|
|
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.
|
|
|
|
|
First of all thank you very much for your answer!
PHPMyAdmin doesn't know the word "WITH"...
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...
|
|
|
|
|
Try inserting a semicolon just before WITH keyword:
;WITH ctePrices AS...
while (!(success = Try()));
|
|
|
|
|
Exactly the same... 
|
|
|
|
|
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()));
|
|
|
|
|
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!
|
|
|
|
|
what!
no no no lets not start that one
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
|
|
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!
|
|
|
|
|
Well, that's a problem with php, not mySQL.
|
|
|
|
|
So I started my quest to learn Angular V6, and I have about a year into it now and doing quite well with it. I've made the move to Angular V7 and will go V8 pretty soon. I choose MongoDB for the database and I really like it, but have yet to do anything advanced with it. Well I just don't know the limitations of MongoDb since it's a NoSQL or document based database.
I'm going to add a store to my project so I can sell things. I've read the right way to do it is to use SQL Server for storing transactions because it's faster. Like run MongoDb to store product information and images, perhaps the cart; and use SQL Server to store the purchase transaction. But then their is FireBase and CosmoDB out there as well. I don't expect to sell much the first year, and I'm considering going Mongo all the way.
Just looking for opinions, help, guidance on this.
I really don't want to go back to SQL Server again.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Quote: Well I just don't know the limitations of MongoDb since it's a NoSQL or document based database.
Yep, that's true, being NoSQL it provides a great amount of flexibility. But most of the flexibility is lost when you take it out of a JavaScript-based environment, such as Node.js. So, consider using it with Node.js web app and then see for yourself.
Quote: I've read the right way to do it is to use SQL Server for storing transactions because it's faster.
Faster in which case? Add a bunch of JOIN statements and heavy on index INSERT INTO queries and you will easily see how MongoDb performs better in most cases, and SQL Server slows down due to housekeeping.
The answer depends entirely on how you want to store the data, do you want to store the transactions as records and then pull out all from the tables one by one? Or do you want to have a single document of everything that a user has done in the system and be returned in a single go? I'll let you answer this.
With SQL Server—or any other relational database—your content is stored as a record, and you have to query the data using several JOIN clauses to prepare a single report. If you do not do this, then you are not following normalization techniques and are wasting money paid for relational features. In NoSQL—especially MongoDb, or other databases or same dialect—you store the data in a fashion that makes it easier to access, yes a bit slower on insert, but querying is better (this statement again of course can be debated upon).
Quote: FireBase and CosmoDB
Oh boy, don't read everything on the internet if you have to develop a product. Pick one and go with it!
Read here how Pinterest used old school MySQL and scaled their hyperactive Pinterest service on the internet; https://medium.com/pinterest-engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6f. There did not break a sweat for SQL Server or MongoDb, or Neo4j or Apache Cassandra (food for thought!  ), all they did was use the infrastructure they have, better optimize it and done.
Quote: I really don't want to go back to SQL Server again.
Okay, how about Apache Cassandra?
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
You got me thinking now.
I'll continue using Mongo and try to come up with a solid compact design.
Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Compress and fix access data in C#, I have to look for examples on google network but it's not running examples, how to compress and fix data in C# ?
|
|
|
|
|
I've just answered it in CG Forum.
Choose something from Google
|
|
|
|
|
Using SQL Server 2016 - I have a table with a TREE column (type is varchar) with data such as the following:
1.0
1.0.1
1.0.2
1.0.2.1
1.0.2.2
1.10.1.35
0) The number of "octets" (values between the periods) is an unknown quantity (it could be anywhere from 2 to 10 levels deep).
2) The number of digits within a given octet will be at least one, but never more than 2.
Desired output: I want all single-digit octets to be 0-padded. So given the sample above, the output would look like this:
01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35
I did it like this, and after spending some time with a couple of DBA's they couldn't improve on it:
;WITH cte AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM MYTABLE
)
, cte2 AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM cte
)
SELECT * FROM cte2;
Is there a better way?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
