Saturday, January 12, 2013 8:08 PM
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?
Sunday, January 13, 2013 10:18 AMcreate 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')
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
) 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
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
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