USE 2 different sql databases within 1 query

Question

I need to modify the following sql 2014 set of queries to use two separate database from 2 different server.

I need to first determine total Revenue, total cost , & total gross profit between the 2 databases, before calculating the Average of the past 7 days based on the sum of the Revenue Totals. the current function I am using works great except it is not using the Sum of Revenue Totals from both database.Tables [dbo].[dw_rpt_traffic] & [mediaalpha].[PublisherCallByDay]

I need to to join the tables from the 2 different server.databases. Note the they should be joined by CallDate & CreateDate. However neither table contains all the dates need for the past 90 days(running 90 days based on current date) so this will affect the type of join needed.

Revised code: Still have issue with last portion of code to combine the data from both datasources to determine the average. Error: Invalid object name 'rpt'.

WITH    RPT
      AS ( SELECT   x.CreateDate
                   , x.RevenueTotals
                   , (x.RevenueTotals-x.COSTTOTALS) as GrossProfit

           FROM     ( SELECT  CAST(t.Create_DTG AS DATE) AS CreateDate
                            ,  SUM([AGENT_REV]+[ANCHOR_REV]+[CORP_REV]+[OFFSITE_REV]) as RevenueTotals
                            ,  SUM([MEDIA_EST_COST]+[OTHER_COST]) as COSTTOTALS
                      FROM     sqlclus3.[abc1234RPT].[dbo].[dw_rpt_traffic] t,
                      WHERE     CAST(t.CREATE_DTG AS DATE) > CAST(GETDATE() - 90 AS DATE)
                      GROUP BY  CAST(t.CREATE_DTG AS DATE)
                    ) x
         )
SELECT  r.CreateDate
       ,r.RevenueTotals
       ,r.GrossProfit
FROM    RPT r
WHERE   r.CreateDate > CAST(GETDATE() - 90 AS DATE)
ORDER BY r.CreateDate desc;


WITH    Calls
      AS ( SELECT   x.Call_Date
                   , x.RevenueTotals
                   , x.Gross_Profit
           FROM     ( SELECT  CAST(t.[CallDate] AS DATE) AS Call_Date
                                    ,  SUM(Revenue) as RevenueTotals
                                    ,  SUM(Cost) as CostTotals
                                    ,  SUM(GROSSPROFIT) AS Gross_Profit
                              FROM     [abc123ETL].[mediaalpha].[PublisherCallByDay] t
                              WHERE     CAST(t.[CallDate] AS DATE) > CAST(GETDATE() - 90 AS DATE)
                              GROUP BY  CAST(t.[CallDate] AS DATE)

                    ) x
         )
SELECT  c.Call_Date
       ,c.RevenueTotals
       ,c.Gross_Profit
FROM    Calls c
WHERE   c.Call_Date > CAST(GETDATE() - 90 AS DATE)
ORDER BY c.Call_Date desc;     

select A.Create_Date
    , A.RevTotal as RevenueTotal
    , A.Gross_profit as GrossProfit
    , AVG(A.RevTotal) OVER ( ORDER BY A.Create_Date 
                             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                             ) AVG7DAYS
FROM
    (
    Select Rpt.CreateDate as Create_Date
        , sum(rpt.RevenueTotals+calls.RevenueTotals) as RevTotal
        , sum(rpt.GrossProfit+calls.Gross_Profit) as GrossProfits
    from rpt FULL OUTER JOIN calls 
            on rpt.createDate = calls.call_date
    ) A
ORDER BY a.Create_Date

OLD Code

use sqlclus3.ABC342

go



WITH    cte
  AS ( SELECT   x.CreateDate
               , x.RevenueTotals
               , x.RevenueTotals-x.COSTTOTALS as GrossProfit
               , AVG(x.RevenueTotals) OVER ( ORDER BY x.CreateDate 
                                      ROWS BETWEEN 6 PRECEDING AND     CURRENT ROW 
                ) AS Avg7Days
       FROM     ( SELECT  CAST(t.Create_DTG AS DATE) AS CreateDate
                        ,  SUM([AGENT_REV]+[ANCHOR_REV]+[CORP_REV]+[OFFSITE_REV]) as RevenueTotals
                        ,  SUM([MEDIA_EST_COST]+[OTHER_COST]) as COSTTOTALS
                  FROM     [dbo].[dw_rpt_traffic] t
                  WHERE     CAST(t.CREATE_DTG AS DATE) > CAST(GETDATE() - 90 AS DATE)
                  GROUP BY  CAST(t.CREATE_DTG AS DATE)
                ) x
     )

SELECT  c.CreateDate
   ,c.RevenueTotals
   ,c.Avg7Days
   ,c.GrossProfit

FROM    cte c

WHERE   c.CreateDate > CAST(GETDATE() - 90 AS DATE)

ORDER BY c.CreateDate desc;


Use SEASQL03.[ABC123]

go

WITH    Calls
  AS ( SELECT   x.Call_Date
               , x.RevenueTotals
               , x.Gross_Profit
               , AVG(x.RevenueTotals) OVER ( ORDER BY x.[Call_Date] 
                                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
                ) AS Avg7Days
       FROM     ( SELECT  CAST(t.[CallDate] AS DATE) AS Call_Date
                        ,  SUM(Revenue) as RevenueTotals
                        ,  SUM(Cost) as CostTotals
                        ,  SUM(GROSSPROFIT) AS Gross_Profit
                  FROM     [mediaalpha].[PublisherCallByDay] t
                  WHERE     CAST(t.[CallDate] AS DATE) > CAST(GETDATE() - 90 AS DATE)
                  GROUP BY  CAST(t.[CallDate] AS DATE)
                ) x
     )

SELECT  c.Call_Date
   ,c.RevenueTotals
   ,c.Avg7Days
   ,c.Gross_Profit

FROM    Calls c

WHERE   c.Call_Date > CAST(GETDATE() - 90 AS DATE)

ORDER BY c.Call_Date desc;

Tags sql-serversql-server-2014 Edit Summary

I believe you need to check out [linked servers](http://stackoverflow.com/questions/4091960/sql-server-linked-server-example-query) — TTeeple, Apr 25 '16 at 16:42
I tried your suggestion,however, it doesn't like the single quotes around 2nd portion of the select statement. SELECT * FROM OPENQUERY(sqlplus3, SELECT * FROM abs212.[dbo].[dw_rpt_traffic] — Karen Schaefer, Apr 25 '16 at 16:52
Looks like you're missing the first single-quote, before `SELECT` — Tab Alleman, Apr 25 '16 at 17:44
Thanks for the suggestion, however, I do not have admin rights to add new linked servers. so see my revised post. — Karen Schaefer, Apr 25 '16 at 18:32

score 0 · Accepted Answer · answered Apr 25 '16 at 17:47

I think the best way to solve this is to add a linked server in the server, so you can call the other database.

For this, you go to one of the servers, and in the folders that appear within the root, there is a "Server Objects" folder, once inside, expand the Linked servers folders. There you can add a new linked server by right clicking this folder and selecting the option "New Linked Server".

You have to complete the information, like if it's a SQL Server instance, or another.

Finally, in the Security tab, you must enter the credentials (Username and password to login the server), like it's shown in the next image:

Once you are ready with this, you can test the new linked server by right clicking it, and selecting "Test Connection".

If it works correctly, you can call any table from that database by just specifying the server and table, like this:

SELECT Column1, Column2 FROM [ServerName].[DatabaseName].[Schema].[TableName]

In your case, should be something like

    SELECT CAST(t.Create_DTG AS DATE) AS CreateDate
                            ,  SUM([AGENT_REV]+[ANCHOR_REV]+[CORP_REV]+[OFFSITE_REV]) as RevenueTotals
                            ,  SUM([MEDIA_EST_COST]+[OTHER_COST]) as COSTTOTALS
 FROM [sqlclus3].[ABC342].[dbo].[dw_rpt_traffic]

USE 2 different sql databases within 1 query

1 Answers1