DECLARE @AsAtDate date = GETDATE();
DECLARE @FundId int = 54; -- 38 or 31
SELECT
@AsAtDate AsAtDate
-- INSERT INTO FundReportingSnapshot
SELECT
@AsAtDate AsAtDate,
@FundId FundId,
MAX(AC_F.NominalAmount) FundCommittedAmount,
MAX(AC_F.BufferAmount) FundBufferAmount,
MAX(AC_F.RetainedProfitAmount) FundRetainedProfit,
AC_Ct.Reference ContractRef,
AC_Ct.Id ContractId,
MAX(AC_Ct.DiscountCode) DiscountCode,
MAX(AC_Ct.LatePaymentDiscountCode) LatePaymentDiscountCode,
MAX(AC_Ct.CommittedAmount) ContractCommittedAmount,
MAX(AC_Ct.CommittedAmountLowerBound) ContractCommittedAmountLowerBound,
MAX(AC_Ct.CommittedAmountUpperBound) ContractCommittedAmountUpperBound,
ISNULL((SUM(TR.HeldPurchasePrice) / MAX(AC_Ct.CommittedAmount)), 0) ContractFillRate,
(SUM(TR.ContractAccruedLatePayment)) ContractAccruedLatePayment,
MAX(TR.HeldVATIncl) HeldVATIncl,
MAX(TR.HeldCreditNoteTotal) HeldCreditNoteTotal,
MAX(TR.AgingDays) AgingDays,
SUM(TR.HeldPurchasePrice) HeldPurchasePrice,
SUM(TR.InvoicesHeldCount) InvoicesHeldCount
FROM AC_Fund AC_F
LEFT OUTER JOIN AC_Contract AC_Ct
ON AC_F.Id = AC_Ct.FundId
LEFT OUTER JOIN (-- JOIN 1: DECLARE @AsAtDate Date = GETDATE(); DECLARE @FundId Int = 54; -- 38 or 31
SELECT
AC_TR.Id,
AC_TR_R.AC_Contract_Id,
MAX(AllI.Reference) Reference,
SUM(HI.PurchasePrice) SumPurchasePrice,
SUM(WeightedIRR) / SUM(PurchasePrice) ContractHeldIRR,
SUM(WeightedIRR2) / SUM(PurchasePrice2) ContractIRR,
SUM(LatePaymentAccrued) ContractAccruedLatePayment,
MAX(VATIncl) HeldVATIncl,
MAX(VATExcl) HeldVATExcl,
MAX(PurchasePrice) HeldPurchasePrice,
MAX(CreditNoteTotal) HeldCreditNoteTotal,
MAX(AgingDays) AgingDays,
MAX(HeldCount) InvoicesHeldCount,
MAX(AC_CoS.Id) SupplierId,
MAX(AC_CoS.CompanySizeId) SupplierSizeId,
MAX(AC_CoS.IndustryId) SupplierIndustryId,
MAX(AC_CoS.CountryId) SupplierCountryId,
MAX(AC_CoB.Id) BuyerId,
MAX(AC_CoB.CompanySizeId) BuyerSizeId,
MAX(AC_CoB.IndustryId) BuyerIndustryId,
MAX(AC_CoB.CountryId) BuyerCountryId
FROM AC_TradingRelationship AC_TR
LEFT OUTER JOIN AC_Company AC_CoS
ON AC_CoS.Id = AC_TR.SupplierCompanyId
LEFT OUTER JOIN AC_Company AC_CoB
ON AC_CoB.Id = AC_TR.BuyerCompanyId
LEFT OUTER JOIN AC_ContractAC_TradingRelationship AC_TR_R
ON AC_CoB.Id = AC_TR.Id
LEFT OUTER JOIN (--JOIN 2: DECLARE @AsAtDate Date = GETDATE(); DECLARE @FundId Int = 54; -- 38 or 31
SELECT
AC_SI.CX_SupplierId,
AC_SI.CX_BuyerId,
ISNULL(SUM(SaleIRR * InvoiceValue / NULLIF(InvoiceValue, 0)), 0) WeightedIRR,
ISNULL(SUM(LatePaymentAccrued), 0) LatePaymentAccrued,
ISNULL(SUM(InvoiceValue), 0) VATIncl,
ISNULL(SUM((InvoiceValue - Vat)), 0) VATExcl,
ISNULL(SUM(SaleValue), 0) PurchasePrice,
ISNULL(SUM(IIF(InvoiceValue < 0, InvoiceValue, 0)), 0) CreditNoteTotal,
ISNULL(DATEDIFF(D, @AsAtDate, ExpectedPaymentDate), 0) AgingDays,
ISNULL(COUNT(*), 0) HeldCount
FROM AC_SoldInvoice AC_SI
JOIN AC_Contract AC_Ct
ON AC_Ct.Id = AC_SI.ContractId
WHERE ISNULL(ActualPaymentDate, @AsAtDate) >= @AsAtDate
AND SaleDate <= @AsAtDate
AND AC_Ct.FundId = @FundId
GROUP BY CX_SupplierId,
CX_BuyerId,
ExpectedPaymentDate) HI
ON CX_SupplierId = AC_CoS.CLeXCompanyId
AND CX_BuyerId = AC_CoB.CLeXCompanyId
LEFT OUTER JOIN (--JOIN 3: DECLARE @AsAtDate Date = GETDATE(); DECLARE @FundId Int = 54; -- 38 or 31
SELECT
AC_Ct.Reference,
CX_SupplierId SupplierId2,
CX_BuyerId BuyerId2,
ISNULL(SUM(SaleIRR * InvoiceValue / InvoiceValue), 0) WeightedIRR2,
ISNULL(SUM(SaleValue), 0) PurchasePrice2
FROM AC_SoldInvoice AC_SI
JOIN AC_Contract AC_Ct
ON AC_Ct.Id = AC_SI.ContractId
WHERE AC_Ct.FundId = @FundId
GROUP BY AC_Ct.Reference,
CX_SupplierId,
CX_BuyerId,
ExpectedPaymentDate) AllI
ON AllI.SupplierId2 = AC_CoS.CLeXCompanyId
AND AllI.BuyerId2 = AC_CoB.CLeXCompanyId
GROUP BY AC_TR_R.AC_Contract_Id,
AC_TR.Id --,Reference
) TR
ON TR.AC_Contract_Id = AC_Ct.Id
WHERE AC_F.Id = @FundId
GROUP BY AC_F.Name,
AC_Ct.Id,
TR.Id,
AC_Ct.Reference,
SumPurchasePrice
nothing is showing for ContractFillRate,
ContractAccruedLatePayment,
HeldVATIncl,
HeldCreditNoteTotal,
AgingDays,
HeldPurchasePrice,
InvoicesHeldCount but i run the query marked JOIN 2: i get records back any help would be much apreciated
