Zohaib Anwar (6/10/2015)

---

ChrisM@Work (6/10/2015)

---

You should be expecting a final query something like this:

;

WITH PreAgg AS (

SELECT

p.id,

HourBucket = DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE() AS DATE))/24,

paidAmount = ISNULL(SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount - tdPaid.CreditAmount ELSE 0 END),0),

InvAmount = ISNULL(SUM(CASE WHEN x.InvAmt = 1 THEN tdPaid.CreditAmount - tdPaid.DebitAmount ELSE 0 END),0)

FROM Payables p WITH (nolock)

INNER JOIN TransactionDetail tdPaid

ON tdPaid.PayableID = p.id

AND tPaid.ReversedFromTransactionID IS NULL

INNER JOIN Transactions tPaid

ON tPaid.id = tdPaid.TransactionID

INNER JOIN TransactionTypes ttPaid

ON ttPaid.id = tPaid.TransactionTypeID

CROSS APPLY (

SELECT

Paid= CASE WHEN (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16))

AND tdpaid.DiscountAmount = 0

THEN 1 ELSE NULL END,

InvAmt= CASE WHEN ttpaid.EnumKey IN ('BillsNew','OrderInvoiced','PayableNew')

AND p.InvoiceTypeID <> 16

AND tdPaid.TransactionTypeID IS NULL

THEN 1 ELSE NULL END

) x

WHERE p.id = payableId -- table alias

AND p.IsPaidInFull = 0

AND NOT EXISTS (SELECT 1 FROM Transactions ti WHERE ti.ReversedFromTransactionID = tpaid.ID)

GROUP BY p.id, p.DueDate

)

SELECT

CASE WHEN sp.id IS NULL THEN 0 ELSE 1 END AS Sort,

v.VendorName,

v.VendorNumber,

v.id VendorId,

sp.id AS SalesPersonId,

SUM(CASE WHEN p.HourBucket <= 0 THEN p.InvAmount - p.paidamount ELSE 0 END) AS [Current],

SUM(CASE WHEN p.HourBucket BETWEEN 1 AND 7 THEN p.InvAmount - p.paidamount ELSE 0 END) AS [PastDue1to7days], -- hours not days

SUM(CASE WHEN p.HourBucket BETWEEN 8 AND 14 THEN p.InvAmount - p.paidamount ELSE 0 END) AS [PastDue1to7days], -- 8 to 14 hours not days

SUM(CASE WHEN p.HourBucket BETWEEN 15 AND 21 THEN p.InvAmount - p.paidamount ELSE 0 END) AS [PastDue1to7days], -- etc

SUM(CASE WHEN p.HourBucket > 21 THEN p.InvAmount - p.paidamount ELSE 0 END) AS [PastDueOver21], -- etc

SUM(p.InvAmount - p.paidamount) AS TotalDue

FROM PreAgg p

LEFT JOIN vendors v

ON v.id = p.VendorID

LEFT JOIN Salespersons sp

ON sp.VendorID = v.id OR sp.id = p.SalespersonID

GROUP BY

CASE WHEN sp.id IS NULL THEN 0 ELSE 1 END,

sp.id,

v.VendorName,

v.VendorNumber,

v.id

ORDER BY

CASE WHEN sp.id IS NULL THEN 0 ELSE 1 END,

v.VendorName

I tried this but getting following error

Msg 4104, Level 16, State 1, Line 11

The multi-part identifier "tPaid.ReversedFromTransactionID" could not be bound.