sql - easiest way to substract in one column and add in other -


basically i'm trying move existances 1 register another, substracting origin point , adding destiny. have 1 table of "stocks" this:

country city value

aa      01   500 aa      02   400 bb      01   300 cc      01   100 cc      02  1000 cc      03  2000 

and receiving document of "movements" structure this:
quantity origincountry origincity endcountry endcity

10      aa             01         cc         01 20      aa             01         bb         02 50      bb             01         cc         03 

is there way without creating 2 normalized tables , several queries.

edit

the answer must this:

quantity origincountry origincity endcountry endcity

aa      01   470 aa      02   400 bb      01   250 cc      01   110 cc      02  1020 cc      03  2050 

negative number , business rules aren't relevant

try (isnull tsql):

select country, city, value - isnull(p, 0) + isnull(m, 0) q stocks s left join (select origincountry, origincity, sum(quantity) m movements group origincountry, origincity) mm    on origincountry = s.country , origincity = s.city left join (select endcountry, endcity, sum(quantity) p movements group endcountry, endcity) mp on   on endcountry = s.country , endcity = s.city 

for ms access (add tag next time...):
should work:

select country, city, value - nz(p, 0) + nz(m, 0) q ((stocks s left join (select origincountry, origincity, sum(quantity) m movements group origincountry, origincity) mm    on (origincountry = s.country , origincity = s.city)) left join (select endcountry, endcity, sum(quantity) p movements group endcountry, endcity) mp on   on (endcountry = s.country , endcity = s.city)) 

or ((if error use dsum function):

select country, city, value    - nz((     select sum(quantity) m      movements      origincountry = s.country , origincity = s.city   ), 0) + nz((     select sum(quantity) p      movements      endcountry = s.country , endcity = s.city   ), 0) q stocks s 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -