|
| WD21 - How to negate a value when defining a Query |
| Iniciado por guest, 20,jun. 2017 11:50 - 2 respuestas |
| |
| | | |
|
| |
| Publicado el 20,junio 2017 - 11:50 |
Hello All,
I trust this finds you all well.
I am building two queries to be used in a join. I would like to query data from two different months. For one month I want the values to be positive (+ve) and the other month Negative (-ve). I want to build a report out of the 'joined queries' such that query1.value+query2.value will give me the net of the two [ query1.value(+ve) + query2.value (-ve) ]=Net.
How do I get the queryvalue "negated" in the query builder?
Thanks in advance.
Sam |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,junio 2017 - 17:16 |
1 Make the two queries by selecting the right data. data --Between two dates -- You can select this in the editor or add two params Start date -- End ddate
-- greater then or equal to
-- less then or equal to
2 Add a calculated element to the list of elements.
Select this : CASE WHEN ? THEN ? [WHEN ? THEN ?] ELSE ? END
? after when add the row woth the positive data
? after then add the positive data agan but add a minus sign before
Should work
regards
Allard |
| |
| |
| | | |
|
| | |
| |
| Publicado el 22,junio 2017 - 10:03 |
Sam,
you could also use a UNION for that
SELECT SUM(netvalue) as netvalue, othervalue1, othervalue2 FROM
(
SELECT table1.value as netvalue, othervalue1, othervalue2 FROM table1 WHERE yourdate BETWEEN {paramd1} AND {paramd2}
UNION ALL
SELECT (table2 .value*-1) as netvalue, othervalue1, othervalue2 FROM table2 WHERE yourdate BETWEEN {paramd3} AND {paramd4}
)
GROUP BY othervalue1, othervalue2
I'm not sure if you can do this using the GUI of the query editor. But you can create it in the sql-code-editor. |
| |
| |
| | | |
|
| | | | |
| | |
|