Question
Sid Williams on Sat, 06 Aug 2016 07:08:25
Please consider the following query, I am a bit confused
WITH ctePurchase
AS ( SELECT [Vendor] ,
ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' THEN -[Inflow]
ELSE [Outflow]
END), 0.0) AS [BalancePur]
FROM tblPurchaseStatement
GROUP BY [Vendor]
),
cteSale
AS ( SELECT [CustomerName] ,
ISNULL(SUM(CASE WHEN [VoucherType] = 'Sales' THEN [Outbound] - [Inbound]
ELSE -[Inbound] + [Outbound]
END), 0) AS [BalanceSal]
FROM tblSaleStatementCustomer
WHERE CustomerType = 'Dealer'
GROUP BY [CustomerName]
)
SELECT a.[Name] ,
CASE WHEN [Type] = 'Vendor'
AND b.[BalancePur] < c.[BalanceSal] THEN -b.[BalancePur] - c.[BalanceSal] + a.[OpeningBalance]
WHEN [Type] = 'Dealer'
AND b.[BalancePur] < c.[BalanceSal] THEN b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance]
ELSE a.[OpeningBalance]
END AS [Balance] ,
a.[ContactNumber]
FROM tblVendors a
LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
WHERE Type = 'Dealer';
I want you to focus on the lines below -
CASE WHEN [Type] = 'Vendor'
AND b.[BalancePur] < c.[BalanceSal] THEN -b.[BalancePur] - c.[BalanceSal] + a.[OpeningBalance]
WHEN [Type] = 'Dealer'
AND b.[BalancePur] < c.[BalanceSal] THEN b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance]
Consider Case When [Type] = 'Dealer'
If b.[BalancePur] is Null and c.[BalanceSal] is Not Null then my calculation should be
0 + c.[BalanceSal] + a.[OpeningBalance]
If b.[BalancePur] is Not Null and c.[BalanceSal] Null then my calculation should be
b.[BalancePur] + 0 + a.[OpeningBalance]
If b.[BalancePur] is Not Null and c.[BalanceSal] is Not Null then my calculation should be
b.[BalancePur] + c.[BalanceSal] + a.[OpeningBalance]
If b.[BalancePur] is Null and c.[BalanceSal] is Null then my calculation should be
0 + 0 + a.[OpeningBalance]
Can someone rearrange my query so I could learn it better.
Replies
Enric Vives on Sat, 06 Aug 2016 08:20:08
Hi Sid, would it be possible to have tblPurchaseStatement and tblVendors DDL stuff and just a few data for testing?
Erland Sommarskog on Sat, 06 Aug 2016 09:22:52
Hi Sid, would it be possible to have tblPurchaseStatement and tblVendors DDL stuff and just a few data for testing?
For some reason Sid starts a new thread rather than continuing in the existing ones. Also, for some reason he keeps working with his old non-working query, instead of the one that Tom Cooper wrote for him.
I would like to remind Sid that people who answer questions where do that in their free time, and there is all reason to show them respect. That is not exactly what you do when you start a new thread and don't include important information from the old thread. People who did not see your previous thread will then have to start from zero, and you are wasting their time.
Sid Williams on Sat, 06 Aug 2016 10:32:04
Hello Erland !
I do understand your point. However, if you see my previous forums, I regularly appreciate the responses to the MSDN members and mark as answers. I will keep that in mind in near future, sorry about that. :(
Sid Williams on Sat, 06 Aug 2016 10:33:41
I had figured it out by the help and support of several members, here is the working script now
WITH ctePurchase
AS ( SELECT [Vendor] ,
ISNULL(SUM(CASE WHEN [Voucher] = 'Purchase' THEN -IsNull([Inflow],0)
ELSE IsNull([Outflow],0)
END), 0.0) AS [BalancePur]
FROM tblPurchaseStatement
GROUP BY [Vendor]
),
cteSale
AS ( SELECT [CustomerName] ,
ISNULL(SUM(CASE WHEN [VoucherType] = 'Sales' THEN IsNull([Outbound],0) - IsNull([Inbound],0)
ELSE -IsNull([Inbound],0) + IsNull([Outbound],0)
END), 0) AS [BalanceSal]
FROM tblSaleStatementCustomer
WHERE CustomerType = 'Dealer'
GROUP BY [CustomerName]
)
SELECT a.[Name] ,
CASE WHEN [Type] = 'Vendor'
AND IsNull(b.[BalancePur], 0) < IsNull(c.[BalanceSal],0) THEN - IsNull(b.BalancePur, 0) - IsNull(c.BalanceSal, 0) + IsNull(a.OpeningBalance, 0)
WHEN [Type] = 'Dealer'
AND IsNull(b.[BalancePur],0) < IsNull(c.[BalanceSal],0) THEN IsNull(b.BalancePur,0) + IsNull(c.BalanceSal, 0) + IsNull(a.OpeningBalance, 0)
ELSE a.[OpeningBalance]
END AS [Balance] ,
a.[ContactNumber]
FROM tblVendors a
LEFT JOIN ctePurchase b ON a.[Name] = b.[Vendor]
LEFT JOIN cteSale c ON a.[Name] = c.[CustomerName]
WHERE Type = 'Dealer';
Thank you all for your kind assistance.