TSql RRS feed

  • Question

  • i am using sqlserver2005.

    i have got two databases DB1 and DB2. DB1 contains a table 'T_Buffalow' and DB2 also contains a table 'T_Buffalow' . both the table contains the coulmns as :

    sb_code char(4) 
    state_code char(2) 
    dist_code char(2) 
    tahsil_code char(4) 
    village_code char(8) 
    ward_code char(4) 
    bm1 bigint 
    bm2 bigint 
    bm3 bigint 
    bm4 bigint 
    bm5 bigint
    bm6 bigint 
    bf1 bigint 
    bf2 bigint 
    bf3 bigint 
    bf4 bigint 
    bf5 bigint 
    bf6 bigint 

    and i am looking for the output as  the coulmns: state_code,sb_code,quick,detail,difference.

    where the quick coulmn will contain the sum of bm1,bm2,bm3,bm4,bm5,bm6,bf1,bf2,bf3,bf4,bf5,bf6 from DB1

    and the detail coulmn will contain sum of bm1,bm2,bm3,bm4,bm5,bm6,bf1,bf2,bf3,bf4,bf5,bf6 from DB2

    and the difference coulmn will contain the difference of the coulmn quick and detail coulmn.

    the state_code coulmn will contain the different state_code.

    Monday, October 4, 2010 8:34 AM


  • Sorry, cannot test it right now

    with db1



    select <columns> sum(bm1+bm2+bm3+bm4+bm5+bm6+bf1+bf2+bf3+bf4+bf5+bf6) over (partition by state_code,sb_code) quick

    from db1.dbo.T_Buffalow




    select <columns> sum(bm1+bm2+bm3+bm4+bm5+bm6+bf1+bf2+bf3+bf4+bf5+bf6) over (partition by state_code,sb_code) detail

    from db2.dbo.T_Buffalow


    ) select db1.state_code,db1.sb_code,quck,detail, quick-detail as difference from

    db1 join db2 on db1.state_code=db2.state_code and db1.sb_code=db2.sb_code




    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Wednesday, October 6, 2010 9:27 AM
    • Marked as answer by NAPSTER.IN Monday, October 11, 2010 5:00 AM
    Monday, October 4, 2010 9:04 AM