Remote query running way to slow

Transact-SQL
1

I am querying across servers to pull data out of accounting system. this is a view that resides on my production server. it runs extremely slow. I did try to use subquery's but when running they both run about the same time and both execution plans show 'remote query' as 100% dedication of the time. I talked to the accounting DBA's about indexes, etc. on the accounting software but got no where. can anyone just look at this and see something that is horribly wrong and another way to approach this? I appreciate any help. here is the View script:

WITH DATA AS(
SELECT
[Customer No_], sum(Amount)TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow,
0 as CurrentBalance, 0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_],
dtl.[Transaction No_], dtl.[Entry No_], hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

select
[Customer No_],0 AS TotalARBalance, ROUND(AVG(DaysToPay),0) AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow,
0 as CurrentBalance, 0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
from(
SELECT
[Customer No_],[Posting Date], [Document No_], [Closed at Date],[Open],
CASE
WHEN year([Posting Date]) = year(getdate()) AND [Closed At Date]>[Posting Date]
THEN CONVERT(FLOAT,(datediff(dd, [Posting Date], [Closed at Date])))
ELSE 0 END AS DaysToPay
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry]
WHERE [Document Type]=2 AND [Open]=0 AND year([Posting Date]) = year(getdate())
) as x
GROUP BY [Customer No_]

UNION ALL

select
[Customer No_],0 AS TotalARBalance, 0 AS DaysToPay, ROUND(AVG(DaysToPayLastYr),0) DaysToPayLastYr, 0 as DaysSlow,
0 as CurrentBalance, 0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
from(
SELECT
[Customer No_],[Posting Date], [Document No_], [Closed at Date],[Open],
CASE
WHEN year([Posting Date]) = year(dateadd(year,-1,getdate())) AND [Closed At Date]>[Posting Date]
THEN CONVERT(FLOAT,(datediff(dd, [Posting Date], [Closed at Date])))
ELSE 0 END AS DaysToPayLastYr
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry]
WHERE [Document Type]=2 AND [Open]=0 AND year([Posting Date]) = year(dateadd(year,-1,getdate()))
) as x
GROUP BY [Customer No_]

UNION ALL

select
[Customer No_],0 AS TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr,
CASE WHEN (ROUND(AVG(DaysToPay),0)-30) >=0 THEN (ROUND(AVG(DaysToPay),0)-30) ELSE 0 END AS DaysSlow,
0 as CurrentBalance, 0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
from(
SELECT
[Customer No_],[Posting Date], [Document No_], [Closed at Date],[Open],
CASE WHEN [Closed At Date]>[Posting Date] AND [Posting Date]>=DATEADD(YEAR,-1,GETDATE())
THEN CONVERT(FLOAT,(datediff(dd, [Posting Date], [Closed at Date])))
ELSE 0 END AS DaysToPay
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry]
WHERE [Document Type]=2 AND [Open]=0
) as x
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, sum(CurrentBalance)CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_], dtl.[Transaction No_],
CASE WHEN datediff(dd, hdr.[Posting Date], GETDATE()) BETWEEN 0 AND 30 THEN CONVERT(MONEY, dtl.Amount,1)
ELSE 0 END AS CurrentBalance, hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
sum(PastDue1To30)PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_], dtl.[Transaction No_],
CASE WHEN datediff(dd, hdr.[Posting Date], GETDATE()) BETWEEN 31 AND 60 THEN CONVERT(MONEY, dtl.Amount,1)
ELSE 0 END AS PastDue1To30, hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, sum(PastDue31To60)PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_], dtl.[Transaction No_],
CASE WHEN datediff(dd, hdr.[Posting Date], GETDATE()) BETWEEN 61 AND 90 THEN CONVERT(MONEY, dtl.Amount,1)
ELSE 0 END AS PastDue31To60, hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, sum(PastDue61To90)PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_], dtl.[Transaction No_],
CASE WHEN datediff(dd, hdr.[Posting Date], GETDATE()) BETWEEN 91 AND 120 THEN CONVERT(MONEY, dtl.Amount,1)
ELSE 0 END AS PastDue61To90, hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, sum(PastDueOver90)PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], hdr.[Closed at Date], hdr.[Closed by Entry No_],
CONVERT(MONEY, dtl.Amount,1) AS Amount, hdr.[Document No_], dtl.[Transaction No_],
CASE WHEN datediff(dd, hdr.[Posting Date], GETDATE()) > 121 THEN CONVERT(MONEY, dtl.Amount,1)
ELSE 0 END AS PastDueOver90, hdr.[Open]
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
[Posting Date] AS FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type],
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[posting date]) AS FirstPaymentNum
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE SUBSTRING(hdr.[Document No_], 1, 1) NOT IN ('P', 'F') AND CONVERT(NUMERIC,dtl.[Initial Document Type])=1
) AS X
WHERE FirstPaymentNum=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL AS FirstPaymentDate, amount AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], dtl.amount,
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[posting date]) AS FirstPaymentNum
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE SUBSTRING(hdr.[Document No_], 1, 1) NOT IN ('P', 'F') AND CONVERT(NUMERIC,dtl.[Initial Document Type])=1
) AS X
WHERE FirstPaymentNum=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
[Posting Date] AS LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type],
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[posting date] DESC) AS LastPaymentNum
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE SUBSTRING(hdr.[Document No_], 1, 1) NOT IN ('P', 'F') AND CONVERT(NUMERIC,dtl.[Initial Document Type])=1
) AS X
WHERE LastPaymentNum=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, amount AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], dtl.amount,
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[posting date] DESC) AS LastPaymentNum
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE SUBSTRING(hdr.[Document No_], 1, 1) NOT IN ('P', 'F') AND CONVERT(NUMERIC,dtl.[Initial Document Type])=1
) AS X
WHERE LastPaymentNum=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
[Document Date] as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], hdr.[Document Date],
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[Document Date] ) AS FirstInvoiceDate
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE CONVERT(NUMERIC,dtl.[document type])=2
) AS X
WHERE FirstInvoiceDate=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, amount as FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], dtl.amount, hdr.[document date],
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[Document Date] ) AS FirstInvoiceDate
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE CONVERT(NUMERIC,dtl.[document type])=2
) AS X
WHERE FirstInvoiceDate=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
[Document Date] AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], hdr.[Document Date],
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[Document Date] DESC) AS FirstInvoiceDate
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE CONVERT(NUMERIC,dtl.[document type])=2
) AS X
WHERE FirstInvoiceDate=1

UNION ALL

SELECT
[Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 AS FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, amount AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT
hdr.[Customer No_], hdr.[Posting Date], dtl.[Document Type], dtl.[Entry Type], dtl.[Journal Batch Name], dtl.[Reason Code], hdr.[Document No_],
dtl.[Transaction No_], hdr.[Reason Code] AS Expr1, dtl.[Initial Document Type], hdr.[Document Date], dtl.amount,
ROW_NUMBER()OVER(PARTITION BY hdr.[customer no_] ORDER BY hdr.[Document No_] DESC) AS FirstInvoiceDate
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Cust_ Ledger Entry] AS hdr INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS dtl ON hdr.[Entry No_] = dtl.[Cust_ Ledger Entry No_]
WHERE CONVERT(NUMERIC,dtl.[document type])=2 AND dtl.[Initial Document Type]='2'
) AS X
WHERE FirstInvoiceDate=1

UNION ALL

SELECT [Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
MAX(RunningTotal)HighCredit, 0 AS SalesYTD, 0 AS LastYearSales
FROM(
SELECT DISTINCT a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount, SUM(b.Amount) AS RunningTotal
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS a INNER JOIN
[remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS b ON a.[Entry No_] >= b.[Entry No_]
and a.[customer no_]=b.[customer no_]
WHERE YEAR(a.[posting date])=YEAR(GETDATE())
GROUP BY a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT [Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, SUM(Amount)SalesYTD, 0 AS LastYearSales
FROM(
SELECT DISTINCT a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS a
WHERE YEAR(a.[posting date])=YEAR(GETDATE()) AND CONVERT(NUMERIC,a.[document type])=2
) AS X
GROUP BY [Customer No_]

UNION ALL

SELECT [Customer No_], 0 as TotalARBalance, 0 AS DaysToPay, 0 AS DaysToPayLastYr, 0 as DaysSlow, 0 as CurrentBalance,
0 as PastDue1To30, 0 as PastDue31To60, 0 as PastDue61To90, 0 as PastDueOver90,
NULL as FirstPaymentDate, 0 as FirstPaymentAmount,
NULL as LastPaymentDate, 0 AS LastPaymentAmount,
NULL as FirstInvoiceDate, 0 AS FirstInvoiceAmount,
NULL AS LastInvoiceDate, 0 AS LastInvoiceAmount,
0 AS HighCredit, 0 AS SalesYTD, SUM(Amount)LastYearSales
FROM(
SELECT DISTINCT a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount
FROM [remoteserver].[Navision_Prod].dbo.[MyCompany Ops$Detailed Cust_ Ledg_ Entry] AS a
WHERE YEAR(a.[posting date])=YEAR(DATEADD(YEAR,-1,GETDATE())) AND CONVERT(NUMERIC,a.[document type])=2
) AS X
GROUP BY [Customer No_]
)
SELECT
[Customer No_],SUM(TotalARBalance)TotalARBalance,SUM(DaysToPay)DaysToPay,SUM(DaysToPayLastYr)DaysToPayLastYr,
SUM(DaysSlow)DaysSlow,SUM(CurrentBalance)CurrentBalance,
SUM(PastDue1To30)PastDue1To30, SUM(PastDue31To60)PastDue31To60, SUM(PastDue61To90)PastDue61To90, SUM(PastDueOver90)PastDueOver90,
MAX(ISNULL(CONVERT(VARCHAR,FirstPaymentDate),''))FirstPaymentDate,SUM(FirstPaymentAmount)FirstPaymentAmount,
MAX(ISNULL(CONVERT(VARCHAR,LastPaymentDate),''))LastPaymentDate,SUM(LastPaymentAmount)LastPaymentAmount,
MAX(ISNULL(CONVERT(VARCHAR,FirstInvoiceDate),''))FirstInvoiceDate,SUM(FirstInvoiceAmount)FirstInvoiceAmount,
MAX(ISNULL(CONVERT(VARCHAR,LastInvoiceDate),''))LastInvoiceDate,SUM(LastInvoiceAmount)LastInvoiceAmount,
SUM(HighCredit)HighCredit,SUM(SalesYTD)SalesYTD,SUM(LastYearSales)LastYearSales
FROM DATA
GROUP BY [Customer No_]

GO

2

Try INNER REMOTE JOIN where appropriate

3

This made no difference, in fact, it ran slower. Any other options that you can think of?

4

Have you tried creating a temp table doing inserts?
I do not know how many tables you are reading from the remote server but what about taking a "snapshot" from it then using that?

5

haven't tried temp table inserts. our IT dept. used to place a snapshot or an extract nightly but our AP/AR dept. wanted real-time data hence the View which is basically a remote query. the culprit in all of this is the 'running total' script. within this View:

SELECT DISTINCT a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount, SUM(b.Amount) AS RunningTotal
FROM [nashoudb2].[Nav_IPS_Prod].dbo.[IPS Ops$Detailed Cust_ Ledg_ Entry] AS a INNER JOIN
[nashoudb2].[Nav_IPS_Prod].dbo.[IPS Ops$Detailed Cust_ Ledg_ Entry] AS b ON a.[Entry No_] >= b.[Entry No_]
and a.[customer no_]=b.[customer no_]
WHERE YEAR(a.[posting date])=YEAR(GETDATE())
GROUP BY a.[customer no_], a.[Posting Date], a.[Entry No_], a.Amount

would it make sense to create a Function and then call this in my View? I am not sure if calling a Function the data is there or it has to run so we will run into the same problem.