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.




Sponsored



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?

See here:
http://social.Msdn.microsoft.com/Forums/en-US/transactsql/thread/6cff841c-5cde-496d-b913-2f009057793f#066d77de-f5dc-4ee5-babf-448aae556e1d

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.