حمیدرضاصادقیان
دوشنبه 21 خرداد 1386, 13:12 عصر
سلام خدمت اساتید گرام. در مورد گزارش کل من query زیر رو نوشتم ولی با یک مشکل کوچک مواجه شدم.
select sanad,rdf,kol_n,moen_n,tafzil_n,disk1,date1,price, bd_bs,remainder= (select sum
(case when x.bd_bs = 1 then price else -price end)
from f_sanad x where x.sanad = t.sanad and x.rdf <= t.rdf)from f_sanad t
where kol_N=10
order by sanad, rdf
داده ها
----------------------------------------------
Sanad rdf date price bd_bs remainder
-------------------------------------------------------
754 1 81/03/01 1000000 1 1000000
931 2 81/03/06 1000000 0 0
1560 1 81/03/18 2000000 1 2000000
1561 2 81/03/18 2000000 0 0
1753 1 81/03/25 2000000 1 2000000
1754 2 81/03/25 1000000 1 1000000
1755 2 81/03/25 2000000 0 0
1755 4 81/03/25 1000000 0 0
1799 1 81/03/25 1000000 1 1000000
1800 1 81/03/25 2000000 1 2000000
1801 1 81/03/25 2000000 1 2000000
1802 1 81/03/25 1000000 1 1000000
1803 1 81/03/25 2000000 1 2000000
1804 6 81/03/25 8000000 0 0
1827 1 81/03/27 1000000 1 1000000
1828 2 81/03/27 1000000 0 0
2191 1 81/04/08 1000000 1 1000000
2192 2 81/04/08 1000000 0 0
2524 1 81/04/17 2000000 1 2000000
2525 2 81/04/17 2000000 0 0
---------------------------------------------------
و جواب query در زیر امده
Sanad rdf date price bd_bs remainder(incorrect) remainder(Correct)
-----------------------------------------------------------------------------------------
1799 1 81/03/25 1000000 1 1000000 1000000
1800 1 81/03/25 2000000 1 2000000 3000000
1801 1 81/03/25 2000000 1 2000000 5000000
1802 1 81/03/25 1000000 1 1000000 6000000
1803 1 81/03/25 2000000 1 2000000 8000000
select sanad,rdf,kol_n,moen_n,tafzil_n,disk1,date1,price, bd_bs,remainder= (select sum
(case when x.bd_bs = 1 then price else -price end)
from f_sanad x where x.sanad = t.sanad and x.rdf <= t.rdf)from f_sanad t
where kol_N=10
order by sanad, rdf
داده ها
----------------------------------------------
Sanad rdf date price bd_bs remainder
-------------------------------------------------------
754 1 81/03/01 1000000 1 1000000
931 2 81/03/06 1000000 0 0
1560 1 81/03/18 2000000 1 2000000
1561 2 81/03/18 2000000 0 0
1753 1 81/03/25 2000000 1 2000000
1754 2 81/03/25 1000000 1 1000000
1755 2 81/03/25 2000000 0 0
1755 4 81/03/25 1000000 0 0
1799 1 81/03/25 1000000 1 1000000
1800 1 81/03/25 2000000 1 2000000
1801 1 81/03/25 2000000 1 2000000
1802 1 81/03/25 1000000 1 1000000
1803 1 81/03/25 2000000 1 2000000
1804 6 81/03/25 8000000 0 0
1827 1 81/03/27 1000000 1 1000000
1828 2 81/03/27 1000000 0 0
2191 1 81/04/08 1000000 1 1000000
2192 2 81/04/08 1000000 0 0
2524 1 81/04/17 2000000 1 2000000
2525 2 81/04/17 2000000 0 0
---------------------------------------------------
و جواب query در زیر امده
Sanad rdf date price bd_bs remainder(incorrect) remainder(Correct)
-----------------------------------------------------------------------------------------
1799 1 81/03/25 1000000 1 1000000 1000000
1800 1 81/03/25 2000000 1 2000000 3000000
1801 1 81/03/25 2000000 1 2000000 5000000
1802 1 81/03/25 1000000 1 1000000 6000000
1803 1 81/03/25 2000000 1 2000000 8000000