Group: General Forum Members
Points: 43
Visits: 540

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.CurrencyF.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.CurrencyF.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.CurrencyF.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)
