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