PC SOFT

FORUMS PROFESSIONNELS
WINDEV, WEBDEV et WINDEV Mobile

Accueil  |  Messages récents  | Connexion  |   | Français  FR
Accueil → WINDEV 2024 → WD21 - How to negate a value when defining a Query
WD21 - How to negate a value when defining a Query
Débuté par Sam Asid, 20 juin 2017 11:50 - 2 réponses
#1
Posté le 20 juin 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
#2
Posté le 21 juin 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
#3
Posté le 22 juin 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.
→ Revenir à WINDEV 2024