June 10, 2015 at 4:30 am
Hi,
I have following query which is taking 30 sec to execute . Can anyone please suggest me that how can I handle these case statements I mean is there any better way to optimize the below query..
I have no missing indexes. execution plan is attached for reference.
with Paid_Cte(payableId,paidAmount)
as
(
select p.id,
sum(tdPaid.DebitAmount-tdPaid.CreditAmount) as paidAmount
from Payables p with (nolock)
left join TransactionDetail tdPaid with (nolock) on tdPaid.PayableID = p.id
left join Transactions tPaid with (nolock) on tPaid.id = tdPaid.TransactionID
left join TransactionTypes ttPaid with (nolock) on ttPaid.id = tPaid.TransactionTypeID
where
p.id=payableId
and p.IsPaidInFull=0
and (ttpaid.EnumKey='BillsPay' or (ttpaid.EnumKey='PayableNew' and p.InvoiceTypeID=16 ) )
and tdpaid.DiscountAmount=0
and tPaid.ReversedFromTransactionID is null
and (select count(*) from Transactions with (nolock) where ReversedFromTransactionID = tpaid.ID) =0
group by p.id
),
--Invoice Amount CTE
InvAmt_Cte(payableId,InvAmount)
as
(
select p.id,
sum(
case when p.InvoiceTypeID=16 then 0
else
case when tdInvAmt.TransactionTypeID is null
then tdInvAmt.CreditAmount-tdInvAmt.DebitAmount
else 0
end
end) as InvAmount
from Payables p with (nolock)
left join TransactionDetail tdInvAmt with (nolock) on tdInvAmt.PayableID = p.id
left join Transactions tInvAmt with (nolock) on tInvAmt.id = tdInvAmt.TransactionID
left join TransactionTypes ttInvAmt with (nolock) on ttInvAmt.id = tInvAmt.TransactionTypeID
where
p.id=payableId
and p.IsPaidInFull=0
and (select count(*) from Transactions with (nolock) where ReversedFromTransactionID = tInvAmt.ID) =0
and tInvAmt.ReversedFromTransactionID is null
and ttInvAmt.EnumKey in('BillsNew','OrderInvoiced','PayableNew')
group by p.id
)
select
case when sp.id is null then 0 else 1 end as Sort,
v.VendorName,
v.VendorNumber,
sum(case when DATEDIFF(hour,isnull(p.DueDate,getdate()),cast(GETDATE()as date))/24 <= 0 then isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0) else 0 end) as "Current",
sum(case when DATEDIFF(hour,isnull(p.DueDate,getdate()),cast(GETDATE()as date))/24 between 1 and 7 then isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0) else 0 end )as "PastDue1to7days",
sum(case when DATEDIFF(hour,isnull(p.DueDate,getdate()),cast(GETDATE()as date))/24 between 8 and 14 then isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0) else 0 end )as "PastDue1to7days",
sum(case when DATEDIFF(hour,isnull(p.DueDate,getdate()),cast(GETDATE()as date))/24 between 15 and 21 then isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0) else 0 end )as "PastDue1to7days",
sum(case when DATEDIFF(hour,isnull(p.DueDate,getdate()),cast(GETDATE()as date))/24 >21 then isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0) else 0 end )as "PastDueOver21",
sum(isnull(invAmt.InvAmount,0)-isnull(paid.paidamount,0)) as TotalDue,
v.id VendorId,
sp.id SalesPersonId
from vendors v with (nolock)
left join Salespersons sp with (nolock) on sp.VendorID = v.id
left join Payables p with (nolock) on p.SalespersonID = sp.id or p.VendorID=v.id
left join Paid_Cte paid on paid.payableId = p.id
left join InvAmt_Cte invAmt on invAmt.payableId=p.id
where
InvAmount is not null or paidamount is not null
group by case when sp.id is null then 0 else 1 end,sp.id, v.VendorName,v.VendorNumber,v.id
order by 1,2
June 10, 2015 at 4:36 am
Are you sure the case statements are the problem?
And why the nolocks? I'd have thought that the people getting data from a transactions table would like their data correct all the time, not with the duplicate rows and missing rows that nolock can cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2015 at 4:40 am
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots of deadlocks in production environment.
June 10, 2015 at 5:02 am
How big are the Vendors and Payables tables? It seems like most of the query is consumed with this join:
left join Payables p with (nolock) on p.SalespersonID = sp.id or p.VendorID=v.id
Joie Andrew
"Since 1982"
June 10, 2015 at 5:04 am
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots of deadlocks in production environment.
There's plenty of scope for improvement in the query. The CASE bits in the outputs are probably not making a detectable contribution to the execution time.
There are logical join errors which if corrected will maybe help a little
The two CTE's could be combined into one to reduce reads a lot.
Tuning a messy query is a frustrating exercise. Rewriting it "properly" is likely to give you the performance gains you are seeking. If you wish to pursue this, then adding table aliases to every column in the query would help immensely.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 5:11 am
ChrisM@Work (6/10/2015)
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots of deadlocks in production environment.There's plenty of scope for improvement in the query. The CASE bits in the outputs are probably not making a detectable contribution to the execution time.
There are logical join errors which if corrected will maybe help a little
The two CTE's could be combined into one to reduce reads a lot.
Tuning a messy query is a frustrating exercise. Rewriting it "properly" is likely to give you the performance gains you are seeking. If you wish to pursue this, then adding table aliases to every column in the query would help immensely.
Thanks for the reply. can you please point out the logical join errors which needs correction?
also the two CTE's can't be combined due to the difference in Where clause of both.
June 10, 2015 at 5:19 am
Joie Andrew (6/10/2015)
How big are the Vendors and Payables tables? It seems like most of the query is consumed with this join:left join Payables p with (nolock) on p.SalespersonID = sp.id or p.VendorID=v.id
Thanks for the reply count of all involved tables are given below
Vendors = 1796
Payables = 51376
Transactions = 72680
TransactionsDetail = 927164
TransactionTypes = 21
Sales person = 93
June 10, 2015 at 5:23 am
Zohaib Anwar (6/10/2015)
if we dont put no lock then we get lots of deadlocks in production environment.
You prefer to add a hint that causes incorrect results rather than address the cause of the deadlocks or looks for a proper solution?
Your users are happy with the intermittently incorrect data from the nolock queries? There haven't been business decisions made off incorrect results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2015 at 5:24 am
Zohaib Anwar (6/10/2015)
ChrisM@Work (6/10/2015)
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots of deadlocks in production environment.There's plenty of scope for improvement in the query. The CASE bits in the outputs are probably not making a detectable contribution to the execution time.
There are logical join errors which if corrected will maybe help a little
The two CTE's could be combined into one to reduce reads a lot.
Tuning a messy query is a frustrating exercise. Rewriting it "properly" is likely to give you the performance gains you are seeking. If you wish to pursue this, then adding table aliases to every column in the query would help immensely.
Thanks for the reply. can you please point out the logical join errors which needs correction?
If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:
AND (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16) )
AND tdpaid.DiscountAmount = 0
also the two CTE's can't be combined due to the difference in Where clause of both.
Of course they can be combined. The differences between them are very small:
WITH Paid_Cte(payableId,paidAmount) AS (
SELECT
p.id,
SUM(tdPaid.DebitAmount - tdPaid.CreditAmount) AS paidAmount
FROM Payables p WITH (nolock)
LEFT JOIN TransactionDetail tdPaid WITH (nolock) ON tdPaid.PayableID = p.id
LEFT JOIN Transactions tPaid WITH (nolock) ON tPaid.id = tdPaid.TransactionID
LEFT JOIN TransactionTypes ttPaid WITH (nolock) ON ttPaid.id = tPaid.TransactionTypeID
WHERE p.id = payableId -- table alias
AND p.IsPaidInFull = 0
AND (
SELECT COUNT(*)
FROM Transactions WITH (nolock)
WHERE ReversedFromTransactionID = tpaid.ID
) = 0
AND tPaid.ReversedFromTransactionID IS NULL
-- Filter unique to "paid"
AND (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16) )
AND tdpaid.DiscountAmount = 0
GROUP BY p.id
),
InvAmt_Cte(payableId,InvAmount) AS (
SELECT
p.id,
SUM(CASE WHEN p.InvoiceTypeID = 16 THEN 0
ELSE
CASE WHEN tdInvAmt.TransactionTypeID IS NULL
THEN tdInvAmt.CreditAmount-tdInvAmt.DebitAmount
ELSE 0
END
END) AS InvAmount
FROM Payables p WITH (nolock)
LEFT JOIN TransactionDetail tdInvAmt WITH (nolock) ON tdInvAmt.PayableID = p.id
LEFT JOIN Transactions tInvAmt WITH (nolock) ON tInvAmt.id = tdInvAmt.TransactionID
LEFT JOIN TransactionTypes ttInvAmt WITH (nolock) ON ttInvAmt.id = tInvAmt.TransactionTypeID
WHERE p.id = payableId
AND p.IsPaidInFull = 0
AND (
SELECT COUNT(*)
FROM Transactions WITH (nolock)
WHERE ReversedFromTransactionID = tInvAmt.ID
) = 0
AND tInvAmt.ReversedFromTransactionID IS NULL
-- Filter unique to "InvAmt"
AND ttInvAmt.EnumKey IN ('BillsNew','OrderInvoiced','PayableNew')
GROUP BY p.id
)
SELECT
CASE WHEN sp.id IS NULL THEN 0 ELSE 1 END AS Sort,
v.VendorName,
v.VendorNumber,
SUM(CASE WHEN DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE()AS date))/24 <= 0 THEN ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)
ELSE 0 END) AS "Current"
SUM(CASE WHEN DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE()AS date))/24 BETWEEN 1 AND 7 THEN ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)
ELSE 0 END ) AS "PastDue1to7days" -- hours not days
SUM(CASE WHEN DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE()AS date))/24 BETWEEN 8 AND 14 THEN ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)
ELSE 0 END ) AS "PastDue1to7days" -- 8 to 14 hours not days
SUM(CASE WHEN DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE()AS date))/24 BETWEEN 15 AND 21 THEN ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)
ELSE 0 END ) AS "PastDue1to7days" -- etc
SUM(CASE WHEN DATEDIFF(hour,ISNULL(p.DueDate,GETDATE()),CAST(GETDATE()AS date))/24 >21 THEN ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)
ELSE 0 END ) AS "PastDueOver21" -- etc
SUM(ISNULL(invAmt.InvAmount,0)-ISNULL(paid.paidamount,0)) AS TotalDue,
v.id VendorId,
sp.id SalesPersonId
FROM vendors v WITH (nolock)
LEFT JOIN Salespersons sp WITH (nolock)
ON sp.VendorID = v.id
LEFT JOIN Payables p WITH (nolock)
ON p.SalespersonID = sp.id
OR p.VendorID = v.id
LEFT JOIN Paid_Cte paid
ON paid.payableId = p.id
LEFT JOIN InvAmt_Cte invAmt
ON invAmt.payableId=p.id
WHERE InvAmount IS NOT NULL
OR paidamount IS NOT NULL
GROUP BY
CASE WHEN sp.id IS NULL THEN 0 ELSE 1 END,
sp.id,
v.VendorName,
v.VendorNumber,
v.id
ORDER BY 1,2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 5:34 am
GilaMonster (6/10/2015)
Zohaib Anwar (6/10/2015)
if we dont put no lock then we get lots of deadlocks in production environment.You prefer to add a hint that causes incorrect results rather than address the cause of the deadlocks or looks for a proper solution?
Your users are happy with the intermittently incorrect data from the nolock queries? There haven't been business decisions made off incorrect results?
Thanks for showing the concern Gila. The cause of the problem is LinqToSQL problem is we are using LinqTOSQL with .net and some of the queries are converted in stored procedure . There are lots of users that use this application so due to the max nuumber of LinqTOSQL Hits to the database we get lots of deadlocks. Yes, the management is agreed to view the dirty data until an unless we make changes in the architecture framework of the application.
Things will get better DAY BY DAY.. we are trying our best to resolve issues. We know hints are not proper solution
Thanks again for your response.
June 10, 2015 at 5:49 am
If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:
AND (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16) )
AND tdpaid.DiscountAmount = 0
Thanks Cris for identifying the issue can you please let me know how I can change it or what alternative way should I have to overcome above situation.
June 10, 2015 at 6:01 am
Zohaib Anwar (6/10/2015)
If you filter a column of an outer-joined table in the WHERE clause of a query, you turn the join into an INNER JOIN:AND (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16) )
AND tdpaid.DiscountAmount = 0
Thanks Cris for identifying the issue can you please let me know how I can change it or what alternative way should I have to overcome above situation.
TransactionTypes ttPaid and TransactionDetail tdPaid are both referenced in the WHERE clause. Except for the special case WHERE column IS NULL, this will convert an outer join into an inner join - because the WHERE clause will filter out NULL values. So change the join for these tables to an INNER JOIN.
Transactions tPaid is also outer joined. An explicit inner join to the child table ttPaid will also change the join to an INNER JOIN. Checking the execution plan confirms that SQL Server is treating each of these as an INNER JOIN. Which yields this for Paid_cte:
SELECT
p.id,
SUM(tdPaid.DebitAmount - tdPaid.CreditAmount) AS paidAmount
FROM Payables p WITH (nolock)
INNER JOIN TransactionDetail tdPaid
ON tdPaid.PayableID = p.id
INNER JOIN Transactions tPaid
ON tPaid.id = tdPaid.TransactionID
INNER JOIN TransactionTypes ttPaid
ON ttPaid.id = tPaid.TransactionTypeID
WHERE p.id = payableId -- table alias
AND p.IsPaidInFull = 0
AND (
SELECT COUNT(*)
FROM Transactions WITH (nolock)
WHERE ReversedFromTransactionID = tpaid.ID
) = 0
AND tPaid.ReversedFromTransactionID IS NULL
-- Filter unique to "paid"
AND (ttpaid.EnumKey = 'BillsPay' OR (ttpaid.EnumKey = 'PayableNew' AND p.InvoiceTypeID = 16) )
AND tdpaid.DiscountAmount = 0
GROUP BY p.id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 6:15 am
Zohaib Anwar (6/10/2015)
ChrisM@Work (6/10/2015)
Zohaib Anwar (6/10/2015)
I am not sure that case statements are problem may be you can see other area of query improvement also if we dont put no lock then we get lots of deadlocks in production environment.There's plenty of scope for improvement in the query. The CASE bits in the outputs are probably not making a detectable contribution to the execution time.
There are logical join errors which if corrected will maybe help a little
The two CTE's could be combined into one to reduce reads a lot.
Tuning a messy query is a frustrating exercise. Rewriting it "properly" is likely to give you the performance gains you are seeking. If you wish to pursue this, then adding table aliases to every column in the query would help immensely.
Thanks for the reply. can you please point out the logical join errors which needs correction?
also the two CTE's can't be combined due to the difference in Where clause of both.
Test and compare the output of this query against the output of your two CTE's:
SELECT
p.id,
paidAmount = SUM(CASE WHEN x.Paid = 1 THEN tdPaid.DebitAmount - tdPaid.CreditAmount ELSE 0 END),
InvAmount = SUM(CASE WHEN x.InvAmt = 1 THEN tdPaid.CreditAmount - tdPaid.DebitAmount ELSE 0 END)
FROM Payables p WITH (nolock)
INNER JOIN TransactionDetail tdPaid
ON tdPaid.PayableID = p.id
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 (
SELECT COUNT(*)
FROM Transactions WITH (nolock)
WHERE ReversedFromTransactionID = tpaid.ID
) = 0
AND tPaid.ReversedFromTransactionID IS NULL
GROUP BY p.id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 6:40 am
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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2015 at 7:09 am
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply