here you go; fairly anonymised

INSERT INTO dbo.tblSales2

(

duedate

,gldate

,invoicedate

,PaidStatus

,OverdueStatus

,Days

,OverdueAmount

,InvoiceDate

,DueDate

,PaiementDate

,GLDate

,PayStatus

,DocCompany

,OrgID

,OrganisationDesc1

,HyperionCode

,AddressNum

,AddressBookSearchType

,CustomerID

,JDECustDesc

,EDBCustDesc

,DocNumber

,Doctype

,PayItem

,PayItemNumber

,PayItemDocType

,GrossAmount

,openamount

,TaxableAmount

,NonTaxableAmount

,Tax

,RPCRCDCurrencyCode

,CurrencyID

,MonthXRate

,ConstantXrate

,OpenAmountUSDMonthRateValue

,OpenAmountUSDConstantRateValue

,BankAccount

,ObjectAccount

,Subsidiary

,DateOfLastSentReminder

,InvoiceNumber

,remark

,NoOfPayments

,AlphaName

,ItemNumberShort

,LastUpdatedBy

,DateTimeLastUpdated

,Deleted

)

SELECT

F.duedate

,F.gldate

,F.invoicedate

,CASE

WHEN F.openamount <> 0 THEN 'Not yet Paid'

ELSE 'Paid'

END AS PaidStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'

ELSE 'Not yet Overdue'

END

END)

END AS OverdueStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian

--Overdue

THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate

--Paid but late

THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate

-- Paid on time or earlier

THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

--'Not yet Overdue'

DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

END

END)

END AS Days

,CASE

WHEN F.openamount <> 0 THEN F.openamount

ELSE F.GrossAmount*-1

END AS OverdueAmount

,CASE WHEN F.invoicedate <> 0

THEN

DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)

+DATEADD(yy, LEFT(F.invoicedate,

CASE WHEN LEN(F.invoicedate)=5

THEN 2

ELSE 3

END

)+0,0)

END AS InvoiceDate

,CASE

WHEN F.duedate <> 0

THEN

DATEADD(dd,RIGHT(F.duedate,3)-1,0)

+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS DueDate

,CASE

WHEN F.checkitemdate <> 0

THEN

DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)

+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS PaiementDate

,CASE

WHEN F.gldate <> 0

THEN

DATEADD(dd,RIGHT(F.gldate,3)-1,0)

+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS GLDate

,F.PayStatus

,F.DocCompany

--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)

,F.OrgID

,O.OrganisationDesc1

,O.HyperionCode

,F.AddressNum

,CS.[Ext/Int]

,C.CustomerID

,CS.CustomerName

,C.CustomerName

,F.DocNumber

,F.Doctype

,F.PayItem

,F.PayItemNumber

,F.PayItemDocType

,F.GrossAmount

,F.openamount

,F.TaxableAmount

,F.NonTaxableAmount

,F.Tax

,CURR.Currency--F.RPCRCDCurrencyCode

,O.CurrencyID

,E.MonthXRate

,E.ConstantXrate

,F.openamount * E.MonthXRate

,F.openamount * E.ConstantXrate

,F.BankAccount

,F.ObjectAccount

,F.Subsidiary

,F.DateOfLastSentReminder

,F.InvoiceNumber

,F.remark

,F.NoOfPayments

,F.AlphaName

,F.ItemNumberShort

,'sales2' AS LastUpdatedBy

,GETDATE() AS DateTimeLastUpdated

,0 AS Deleted

FROM

dbo.sales AS F WITH (NOLOCK)

LEFT OUTER JOIN customersource CS WITH (NOLOCK)

ON CS.ERPID = 1

AND F.AddressNum = CS.ERPCustomerCode

INNER JOIN customer C WITH (NOLOCK)

ON CS.CustomerID = C.CustomerID

INNER JOIN organisation O WITH (NOLOCK)

ON F.OrgID = O.OrgID

INNER JOIN currency CURR WITH (NOLOCK)

ON O.CurrencyID = CURR.CurrencyID

LEFT OUTER JOIN exchangerate E WITH (NOLOCK)

ON E.XrateScenarioTypeID = 1

AND E.Deleted = 0

AND E.XRateTypeID = 1

AND CURR.CurrencyID = E.XrateCurrID

AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear

AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth

WHERE

(F.gldate > @StartDate OR F.gldate = 0) AND (F.duedate > @StartDate OR F.duedate = 0)

AND F.Deleted = 0

AND O.Deleted = 0

UNION

SELECT

F.duedate

,F.gldate

,F.invoicedate

,CASE

WHEN F.openamount <> 0 THEN 'Not yet Paid'

ELSE 'Paid'

END AS PaidStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'

ELSE 'Not yet Overdue'

END

END)

END AS OverdueStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian

--Overdue

THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate

--Paid but late

THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate

-- Paid on time or earlier

THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

--'Not yet Overdue'

DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

END

END)

END AS Days

,CASE

WHEN F.openamount <> 0 THEN F.openamount

ELSE F.GrossAmount*-1

END AS OverdueAmount

,CASE WHEN F.invoicedate <> 0

THEN

DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)

+DATEADD(yy, LEFT(F.invoicedate,

CASE WHEN LEN(F.invoicedate)=5

THEN 2

ELSE 3

END

)+0,0)

END AS InvoiceDate

,CASE

WHEN F.duedate <> 0

THEN

DATEADD(dd,RIGHT(F.duedate,3)-1,0)

+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS DueDate

,CASE

WHEN F.checkitemdate <> 0

THEN

DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)

+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS PaiementDate

,CASE

WHEN F.gldate <> 0

THEN

DATEADD(dd,RIGHT(F.gldate,3)-1,0)

+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS GLDate

,F.PayStatus

,F.DocCompany

--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)

,F.OrgID

,O.OrganisationDesc1

,O.HyperionCode

,F.AddressNum

,CS.[Ext/Int]

,C.CustomerID

,CS.CustomerName

,C.CustomerName

,F.DocNumber

,F.Doctype

,F.PayItem

,F.PayItemNumber

,F.PayItemDocType

,F.GrossAmount

,F.openamount

,F.TaxableAmount

,F.NonTaxableAmount

,F.Tax

,CURR.Currency--F.RPCRCDCurrencyCode

,O.CurrencyID

,E.MonthXRate

,E.ConstantXrate

,F.openamount * E.MonthXRate

,F.openamount * E.ConstantXrate

,F.BankAccount

,F.ObjectAccount

,F.Subsidiary

,F.DateOfLastSentReminder

,F.InvoiceNumber

,F.remark

,F.NoOfPayments

,F.AlphaName

,F.ItemNumberShort

,'sales2' AS LastUpdatedBy

,GETDATE() AS DateTimeLastUpdated

,0 AS Deleted

FROM

dbo.sales_historicalAS F WITH (NOLOCK)

LEFT OUTER JOIN customerSource CS WITH (NOLOCK)

ON CS.ERPID = 1

AND F.AddressNum = CS.ERPCustomerCode

INNER JOIN customer C WITH (NOLOCK)

ON CS.CustomerID = C.CustomerID

INNER JOIN organisation O WITH (NOLOCK)

--ON ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID) = O.OrgID

ON F.OrgID = O.OrgID

INNER JOIN currency CURR WITH (NOLOCK)

ON O.CurrencyID = CURR.CurrencyID

LEFT OUTER JOIN exchangerate E WITH (NOLOCK)

ON E.XrateScenarioTypeID = 1

AND E.Deleted = 0

AND E.XRateTypeID = 1

AND CURR.CurrencyID = E.XrateCurrID

AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear

AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth

WHERE

(F.gldate <= @StartDate OR F.duedate <= @StartDate)

AND (F.openamount <> 0 AND duedate < @TodayinJulian)

AND F.Deleted = 0

AND O.Deleted = 0

UNION

SELECT

F.duedate

,F.gldate

,F.invoicedate

,CASE

WHEN F.openamount <> 0 THEN 'Not yet Paid'

ELSE 'Paid'

END AS PaidStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'

ELSE 'Not yet Overdue'

END

END)

END AS OverdueStatus

,CASE

WHEN openamount <> 0 AND duedate < @TodayinJulian

--Overdue

THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE (

CASE

WHEN openamount = 0 AND duedate < gldate

--Paid but late

THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

CASE

WHEN openamount = 0 AND duedate >= gldate

-- Paid on time or earlier

THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

ELSE

--'Not yet Overdue'

DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))

END

END)

END AS Days

,CASE

WHEN F.openamount <> 0 THEN F.openamount

ELSE F.GrossAmount*-1

END AS OverdueAmount

,CASE WHEN F.invoicedate <> 0

THEN

DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)

+DATEADD(yy, LEFT(F.invoicedate,

CASE WHEN LEN(F.invoicedate)=5

THEN 2

ELSE 3

END

)+0,0)

END AS InvoiceDate

,CASE

WHEN F.duedate <> 0

THEN

DATEADD(dd,RIGHT(F.duedate,3)-1,0)

+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS DueDate

,CASE

WHEN F.checkitemdate <> 0

THEN

DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)

+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS PaiementDate

,CASE

WHEN F.gldate <> 0

THEN

DATEADD(dd,RIGHT(F.gldate,3)-1,0)

+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)

ELSE NULL

END AS GLDate

,F.PayStatus

,F.DocCompany

--,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)

,F.OrgID

,O.OrganisationDesc1

,O.HyperionCode

,F.AddressNum

,CS.[Ext/Int]

,C.CustomerID

,CS.CustomerName

,C.CustomerName

,F.DocNumber

,F.Doctype

,F.PayItem

,F.PayItemNumber

,F.PayItemDocType

,F.GrossAmount

,F.openamount

,F.TaxableAmount

,F.NonTaxableAmount

,F.Tax

,CURR.Currency--F.RPCRCDCurrencyCode

,O.CurrencyID

,E.MonthXRate

,E.ConstantXrate

,F.openamount * E.MonthXRate

,F.openamount * E.ConstantXrate

,F.BankAccount

,F.ObjectAccount

,F.Subsidiary

,F.DateOfLastSentReminder

,F.InvoiceNumber

,F.remark

,F.NoOfPayments

,F.AlphaName

,F.ItemNumberShort

,'sales2' AS LastUpdatedBy

,GETDATE() AS DateTimeLastUpdated

,0 AS Deleted

FROM

sales AS F WITH (NOLOCK)

LEFT OUTER JOIN customerSource CS WITH (NOLOCK)

ON CS.ERPID = 1

AND F.AddressNum = CS.ERPCustomerCode

INNER JOIN customer C WITH (NOLOCK)

ON CS.CustomerID = C.CustomerID

INNER JOIN organisation O WITH (NOLOCK)

ON F.OrgID = O.OrgID

INNER JOIN currency CURR WITH (NOLOCK)

ON O.CurrencyID = CURR.CurrencyID

LEFT OUTER JOIN exchangerate E WITH (NOLOCK)

ON E.XrateScenarioTypeID = 1

AND E.Deleted = 0

AND E.XRateTypeID = 1

AND CURR.CurrencyID = E.XrateCurrID

AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear

AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth

WHERE

(F.gldate <= @StartDate OR F.duedate <= @StartDate)

AND (F.openamount <> 0 AND duedate < @TodayinJulian)

AND F.Deleted = 0

AND O.Deleted = 0

OPTION (MAXDOP 12)