How to manipulate/join tables between 2 databases?

  • Hi,

    I need to get compare sales % which is (4/30/03 sales minus previous year 4/30/02 sales)/ previous years 4/30/02 sales.

    The sales of this year is stored in ODS database and the sales of previous year is stored in DW database, both are in same server and same object owner.

    I know how to retrive another database by 3 prefix, but can someone tell me how to join these 2 tables from 2 databases?

    Thank you.

  • Try example.

    select *

    from northwind.dbo.orders np

    inner join pubs.dbo.orders pp

    on np.orderid = pp.orderid

  • If you use 3 prefix it will work. A bit longwinded but it does work. I've tried 2 prefix like in Allen's example before and it doesn't work for me.

    Kevin

  • I am trying to write the query to compare yesterday's sales and previous fiscal year same day sales as following, but got error message :

    Server: Msg 107, Level 16, State 2, Line 1

    The column prefix 'InSightAmKgODS.micros.opeartions' does not match with a table name or alias name used in the query.

    ---------------------------------

    Select (A.TYSales-B.PYSales),

    A. Store_Num

    from

    (select InSightAmKgODS.micros.Operations.NetFoodSales As TYSales,

    InSightAmKgODS.micros.store_table.store_num,

    InSightAmKgODS.micros.date_table.PYprdNum,

    InSightAmKgODS.micros.date_table.PYPrddyNum,

    InSightAmKgODS.micros.date_table.PYYear

    from InSightAmKgODS.micros.Operations,

    InSightAmKgODS.micros.store_table,

    InSightAmKgODS.micros.date_table

    where

    InSightAmKgODS.micros.Date_table.bus_Date = convert(varchar, GETDATE()-1,101)

    and InSightAmKgODS.micros.store_table.store_mgr is not null

    and

    InSightAmKgODS.micros.date_table.bus_date=InSightAmKgODS.micros.opeartions.bus_date

    and

    InSightAmKgODS.micros.store_table.store_num=InSightAmKgODS.micros.Operations.store_table)

    A,

    (Select InSightAmKgDW.micros.OperationsFacts.PYNetFoodSales As PYSales,

    InSightAmKgDW.micros.LocationDimension.store#,

    InSightAmKgDW.micros.DateDimension.DtdPYPrdNum,

    InSightAmKgDW.micros.DateDimension.DtdPYPrddyNum,

    InSightAmKgDW.micros.DateDimension.DtdPYYear

    from InSightAmKgDW.micros.LocationDimension,

    InSightAmKgDW.micros.OperationsFacts,

    InSightAmKgDW.micros.DateDimension

    where (InSightAmKgDW.micros.DateDimension.DtdPYYear=2002

    And InSightAmKgDW.micros.DateDimension.DtdPyPrdNum=

    (select D.DtdPyPrdNum

    from InSightAmKgDW.micros.DateDimension D

    where D.dtdbusdate=convert(varchar, getdate()-1,101)

    )

    And InSightAmKgDW.micros.DateDimension.DtdPYPrddyNum=

    (Select D.DtdPrddyNum

    From InSightAmKgDW.micros.DateDimension D

    Where D.dtdbusdate=convert(varchar, getdate()-1,101)

    )

    and InSightAmKgDW.micros.LocationDimension.LcdStoreMgr is not Null)

    and

    InSightAmKgDW.micros.DateDimension.DtdDateKey=InSightAmKgDW.micros.OperationsFacts.OpfDatekey

    and

    InSightAmKgDW.micros.LocationDimension.LcdLocKey=InSightAmKgDW.micros.OperationsFacts.OpfLockey)

    B

    WHERE A.Store_Num=B.Store#

    and A.PYprdNum=B.DtdPYPrdNum

    and A.PYPrddyNum=B.DtdPYPrddyNum

    and A.PYYear=B.DtdPYYear+1

    ---------------------------------------

    Can someone help?

    Thank you.

  • It's because you have a typo. The line

    InSightAmKgODS.micros.date_table.bus_date=InSightAmKgODS.micros.opeartions.bus_date

    should be

    InSightAmKgODS.micros.date_table.bus_date=InSightAmKgODS.micros.operations.bus_date

    (opeartions vs operations)

    This is where it is wise to use a table alias instead of repeatly typing long table names, especially when they are three part names like this.

    Jay Madren


    Jay Madren

  • Thank you. I got the statement to work now.

    The output format I need is %, such as 31.24%

    but now I get .3124. How to conver that?

    The current output is:

    --------------------

    .3124BK07627

    --------------------

    The code is :

    --------------------

    Select TOP 1 (A.TYSales-B.PYSales)/B.PYSales As Comp_Sales,

    A. Store_Num

    from

    (select OPS.Net_Food_Sales As TYSales,

    ST.store_num,

    DT.PYprd_Num,

    DT.PYPrddy_Num,

    DT.PYYear

    from InSightAmKgODS.micros.Operations OPS,

    InSightAmKgODS.micros.store_table ST,

    InSightAmKgODS.micros.date_table DT

    where

    DT.bus_Date = convert(varchar, GETDATE()-1,101)

    and ST.store_mgr is not null

    and DT.bus_date=OPS.bus_date

    and ST.store_num=OPS.store_num) A,

    (Select OPS.opfNetFoodSales As PYSales,

    Loc.LcdstoreNum,

    D.DtdPYPrdNum,

    D.DtdPYPrddyNum,

    D.DtdPYYear

    from InSightAmKgDW.micros.LocationDimension Loc,

    InSightAmKgDW.micros.OperationsFacts OPS,

    InSightAmKgDW.micros.DateDimension D

    where (D.DtdPYYear=2002

    And D.DtdPyPrdNum=

    (select D.DtdPyPrdNum

    from InSightAmKgDW.micros.DateDimension D

    where D.dtdbusdate=convert(varchar, getdate()-1,101)

    )

    And D.DtdPYPrddyNum=

    (Select D.DtdPrddyNum

    From InSightAmKgDW.micros.DateDimension D

    Where D.dtdbusdate=convert(varchar, getdate()-1,101)

    )

    and LOC.LcdStoreMgr is not Null)

    and D.DtdDateKey=OPS.OpfDatekey

    and LOC.LcdLocKey=OPS.OpfLockey) B

    WHERE A.Store_Num=B.LcdStoreNum

    and A.PYprd_Num=B.DtdPYPrdNum

    and A.PYPrddy_Num=B.DtdPYPrddyNum

    and A.PYYear=B.DtdPYYear+1

    and A.TYSales >0 and B.PYSales >0

    Order by Comp_Sales desc

    ---------------------------

    Thank you.

  • Change the first line to

    Select TOP 1 (A.TYSales-B.PYSales)/B.PYSales*100 As Comp_Sales,

    If you want to round the result to, say, one decimal place then

    Select TOP 1 ROUND((A.TYSales-B.PYSales)/B.PYSales*100, 1) As Comp_Sales,

    Jay Madren


    Jay Madren

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply