Calculate fields

Calculate fields

• Saturday, January 12, 2013 8:08 PM

Hello

is it possible to calculate fields by this way?

 Date Country State Coefficient Realized 12.1.2012 Usa New York 1,3 No 12.1.2012 Usa New Jersey 1,5 Yes 12.1.2012 Canada Toronto 1,4 Yes

I see that date by the Country is the same, for this date I have 100\$ but if one transaction at this date in the same country was not realized, then sum is -100\$. Then I see I have again the same date, but other Country and transaction is realized. So the sum should be 100x1,4=140. And I need to get final sum, so 140-100=40.

Is it possible to do in sql?

Thanks

All Replies

• Sunday, January 13, 2013 10:18 AM

create table #t (dt date,country varchar(20),state varchar(20),Coefficient real,Realized varchar(20))

insert into #t values ('20120101','Usa', 'New York',1.3,'No')
insert into #t values ('20120101','Usa', 'New Jersey',1.5,'Yes')
insert into #t values ('20120101','Canada', 'Toronto',1.4,'Yes')

with cte
as
(
select *,row_number() over (order by dt) rn, case when exists (select * from #t t
where t.dt=#t.dt and t.country=#t.country
and Realized ='No') then -100 else 100*Coefficient end tt
from #t
) select * ,(select top 1 cte.tt+c.tt
from cte join cte c on cte.rn=c.rn-1 order by cte.tt+c.tt desc) as f
from cte

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

• Sunday, January 13, 2013 5:34 PM

Uri

thank you, but when I have more data in database sum of all is not is not correct.

for example here

2012-01-01    Usa    New York    1,3    No    1    -100    280
2012-01-01    Usa    New Jersey    1,5    Yes    2    -100    280
2012-01-01    Canada    Toronto    1,4    Yes    3    140    280
2012-01-11    Usa    New York    1,3    No    4    -100    280
2012-01-11    Usa    New Jersey    1,5    Yes    5    -100    280
2013-01-10    Usa    New York    1,3    Yes    6    130    280
2013-01-10    Usa    New Jersey    1,5    Yes    7    150    280