May 1, 2003 at 1:15 pm
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.
May 1, 2003 at 1:24 pm
Try example.
select *
from northwind.dbo.orders np
inner join pubs.dbo.orders pp
on np.orderid = pp.orderid
May 2, 2003 at 4:12 am
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
May 2, 2003 at 8:46 am
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.
May 2, 2003 at 9:07 am
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
May 2, 2003 at 9:43 am
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.
May 2, 2003 at 10:23 am
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