Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Calculate fields

Dotaz Calculate fields

  • Saturday, January 12, 2013 8:08 PM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0
     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
     
     
    Sign In to Vote
    0

    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