I have a couple of SQL databases that i am trying to link results from.

My first database is calendar that i need to return the details for today so current date and targets for sales and orders that works

my second data base is for orders which i can sum the total orders for today and add that to a column on the select statement (code below) but the trouble started when i want to add another column with total orders for the period so far using periodstartdate and currentdate from database 1. any help would be great

USE [FFP_WebServices]
GO
/****** Object:  StoredProcedure [dbo].[WEBSERV_DAILY_FIGURES_SELECT]    Script Date: 11/10/2018 11:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==============================================
-- Author:  Matthew Clay
-- Create date: 11 October 2018
-- Description: Data for Customer Services Screen
-- ==============================================

ALTER PROCEDURE [dbo].[WEBSERV_DAILY_FIGURES_SELECT]
AS
BEGIN

-- EXEC dbo.WEBSERV_DAILY_FIGURES_SELECT

     SELECT Period as Per,
            TradingDayInPeriod as PDay,
            CurrentDate as CDate,
            WeekStartDate as WSDate,    
            PeriodStartDate as PSDate,  
            DailyOrdersTarget as OrdersTD,   
            WeeklyOrdersTarget as OrdersTW,
            PeriodOrdersTarget as OrdersTP, 
            DailySalesTarget as SalesTD,    
            WeeklySalesTarget as SalesTW,   
            PeriodSalesTarget as SalesTP,
            SUM (CASE WHEN BP.CUR_0='GBP' THEN (SOQ.YORIQTY_0 * SOP.GROPRI_0) ELSE ((SOQ.YORIQTY_0 * SOP.GROPRI_0) * SO.CHGRAT_0) END) as OrdersDaily
       FROM [dbo].[Calendar] as A

 INNER JOIN [FFP-X3DB\X3].[ffp].[FFP].[SORDER] as SO ON A.CurrentDate = SO.ORDDAT_0
 INNER JOIN [FFP-X3DB\X3].[ffp].[FFP].[BPARTNER] as BP ON SO.BPCORD_0 = BP.BPRNUM_0 
 INNER JOIN [FFP-X3DB\X3].[ffp].[FFP].[SORDERP] as SOP ON SO.SOHNUM_0 = SOP.SOHNUM_0 
 INNER JOIN [FFP-X3DB\X3].[ffp].[FFP].[SORDERQ] as SOQ ON SO.SOHNUM_0 = SOQ.SOHNUM_0 
      WHERE SOP.SOPLIN_0='1000' and SOQ.SOPLIN_0='1000' and SO.ORDDAT_0 = (Convert(date, GetDate(),110)) and SO.SOHTYP_0!='SO6'

   GROUP BY A.Period, A.TradingDayInPeriod, A.CurrentDate, A.WeekStartDate, A.PeriodStartDate, A.DailyOrdersTarget, A.WeeklyOrdersTarget, A.PeriodOrdersTarget, A.DailySalesTarget, A.WeeklySalesTarget, A.PeriodSalesTarget
END