PipRon7 PipRon7 - 3 months ago 12
SQL Question

replacing data on sql condition

I have the below query:

SELECT

CAST( COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) AS DATE) AS Date_Transacted
,CASE
WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
AND rp.PaymentMethodId in (4,5)
AND pos.CurrencyId = 2
AND rp.countryid = 2
THEN 'WorldPay'

WHEN pm.PaymentDescription IN ('Sofort', 'iDEAL')
THEN 'Direct Transfers'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode IN ('AUD')
THEN 'NAB'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode NOT IN('AUD')
THEN 'Barclays'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode NOT IN('AUD') AND cb.Reference IS NULL
THEN 'Voucher'

ELSE pm.PaymentDescription
END AS Cash_Acquirer
,c2.CurrencyCode
,cb.Reference AS OA_Cash_Book



------------------------------------------------------------------------------------------------
--------------------------------Cash In--------------------------------------------------------

,(
SUM(
CASE
WHEN (
pos.VoidHeaderId IS NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('I')

AND cb.CashBookReferenceId IS NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity


WHEN (
pos.VoidHeaderId IS NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('I')

AND cb.CashBookReferenceId IS NOT NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- New_Billed_Orders_Curr
+SUM(
CASE
WHEN (
(pos.VoidHeaderId IS NULL OR pos.VoidHeaderId = 0)
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection = 'I'
AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
(pos.VoidHeaderId IS NULL OR pos.VoidHeaderId = 0)
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection = 'I'
AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NOT NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Voucher_Purchased_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NULL
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('I')

AND pos.PriceIncTax < 0
AND cb.CashBookReferenceId IS NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NULL
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('I')

AND pos.PriceIncTax < 0
AND cb.CashBookReferenceId IS NOT NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Voucher_Redeemed_Curr
+SUM(
CASE
WHEN (
pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('I','V')

AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT

AND cfd.CashFeedTypeId IN (8) -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax / (1 + CT.TaxRate) * pos.Quantity

WHEN (
pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('I','V')

AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NOT NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT

AND cfd.CashFeedTypeId IN (8) -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax / (1 + CT.TaxRate) * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Premier_Subscription_Curr
+SUM(
CASE
WHEN (
pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('I','V')

AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT

AND cfd.CashFeedTypeId IN (11) -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax / (1 + CT.TaxRate) * CT.TaxRate * pos.Quantity

WHEN (
pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('I','V')

AND pos.PriceIncTax > 0
AND cb.CashBookReferenceId IS NOT NULL
AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT

AND cfd.CashFeedTypeId IN (11) -- added to combine Cash IN & Cash OUT
)
THEN pos.PriceIncTax / (1 + CT.TaxRate) * CT.TaxRate * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Premier_Subscription_Vat_Curr

+SUM(
CASE
WHEN cfd.CashFeedTypeId = 17
AND pos.CashDirection IN ('I')

THEN (pos.PriceIncTax * (pos.Quantity*-1))*2
ELSE 0
END) --ALIST_Script_Correction1

+SUM(
CASE
WHEN cfd.CashFeedTypeId = 19
AND pos.CashDirection IN ('I','V')

THEN (pos.PriceIncTax * (pos.Quantity*-1))*2
ELSE 0
END) --ALIST_Script_Correction2

)AS Cash_In_Curr


------------------------------------------------------------------------------------------------
--------------------------------Cash Out--------------------------------------------------------


,(
SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O')

AND pos.DateCancelled IS NULL
AND (CAST (pos.DateBilled AS DATE) = CAST (cfd.DateTransacted AS DATE) OR CAST (pos.DateRefunded AS DATE) = CAST (cfd.DateTransacted AS DATE) )
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O')

AND pos.DateCancelled IS NULL
AND (CAST (pos.DateBilled AS DATE) = CAST (cfd.DateTransacted AS DATE) OR CAST (pos.DateRefunded AS DATE) = CAST (cfd.DateTransacted AS DATE) )
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Basket_Refunded_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O','V')

--AND pos.DateCancelled IS NOT NULL
AND pos.DateCancelled >= '20160731'
AND pos.DateCancelled < '20160810'
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O','V')

--AND pos.DateCancelled IS NOT NULL
AND pos.DateCancelled >= '20160731'
AND pos.DateCancelled < '20160810'
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Basket_Cancelled_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL AND pos.VoidHeaderId <> 0
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.PriceIncTax > 0
AND pos.CashDirection IN ('O')

AND pos.DateBilled IS NOT NULL
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL AND pos.VoidHeaderId <> 0
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.PriceIncTax > 0
AND pos.CashDirection IN ('O')

AND pos.DateBilled IS NOT NULL
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Voucher_Reissued_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O')

AND pos.DateCancelled IS NOT NULL
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptVoucherId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NULL
AND pos.CashDirection IN ('O')

AND pos.DateCancelled IS NOT NULL
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Voucher_Cancelled_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('O','V')

AND pos.DateRefunded IS NOT NULL
AND cfd.CashFeedTypeId = 9
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax / (1 + ct.TaxRate) * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('O','V')

AND pos.DateRefunded IS NOT NULL
AND cfd.CashFeedTypeId = 9
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax / (1 + ct.TaxRate) * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Premier_Subscription_Refund_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('O','V')

AND pos.DateRefunded IS NOT NULL
AND cfd.CashFeedTypeId = 12
AND cb.CashBookReferenceId IS NULL
)
THEN pos.PriceIncTax / (1 + ct.TaxRate) * ct.TaxRate * pos.Quantity

WHEN (
pos.VoidHeaderId IS NOT NULL
AND pos.ReceiptShippingSubscriptionId IS NOT NULL
AND pos.CashDirection IN ('O','V')

AND pos.DateRefunded IS NOT NULL
AND cfd.CashFeedTypeId = 12
AND cb.CashBookReferenceId IS NOT NULL
)
THEN pos.PriceIncTax / (1 + ct.TaxRate) * ct.TaxRate * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Premier_Subscription_Refund_Vat_Curr
+SUM(
CASE
WHEN (
pos.VoidHeaderId IS NULL
AND pos.IsChequeRefund IN (0,1)
AND pos.DateRefunded IS NOT NULL
AND cb.CashBookReferenceId IS NULL

AND pos.CashDirection IN ('O')

)
THEN pos.PriceIncTax * pos.Quantity

WHEN (
pos.VoidHeaderId IS NULL
AND pos.IsChequeRefund IN (0,1)
AND pos.DateRefunded IS NOT NULL
AND cb.CashBookReferenceId IS NOT NULL

AND pos.CashDirection IN ('O')

)
THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate

ELSE 0
END) -- Goodwill_Curr

+SUM(
CASE
WHEN cfd.CashFeedTypeId = 17
AND pos.CashDirection IN ('Z') -- Ignore and set to 0
THEN (pos.PriceIncTax * (pos.Quantity*-1))*2
ELSE 0
END) --ALIST_Script_Correction1

+SUM(
CASE
WHEN cfd.CashFeedTypeId = 19
AND pos.CashDirection IN ('O','V')
THEN (pos.PriceIncTax * (pos.Quantity*-1))*2
ELSE 0
END) --ALIST_Script_Correction2


) AS Cash_Out_Curr

FROM
dbo.POSItem (NOLOCK) POS
LEFT OUTER JOIN dbo.ReceiptPayment (NOLOCK) RP ON rp.ReceiptPaymentId = pos.ReceiptPaymentId
LEFT OUTER JOIN dbo.CountryTax (NOLOCK) CT ON rp.CountryId = ct.CountryId AND COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) >= ct.DateFrom AND (COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) < ct.DateTo OR ct.DateTo IS NULL) --amended to combine Cash IN & Cash OUT
LEFT OUTER JOIN dbo.State (NOLOCK) S ON rp.CountryId = s.CountryId AND rp.County = s.Code
LEFT OUTER JOIN dbo.Country (NOLOCK) C1 ON rp.CountryId = c1.CountryId
LEFT OUTER JOIN dbo.Country (NOLOCK) C2 ON pos.CurrencyId = c2.CountryId
--LEFT OUTER JOIN dbo.OpenAccountsCurrency (NOLOCK) OACURR ON pos.CurrencyId = oacurr.CurrencyId --Not required for the query
LEFT OUTER JOIN dbo.CashFeedPOSItem (NOLOCK) CFP ON pos.POSItemId = cfp.POSItemId
LEFT OUTER JOIN dbo.CashFeedDetail (NOLOCK) CFD ON cfp.CashFeedDetailId = cfd.CashFeedDetailId
LEFT OUTER JOIN dbo.PaymentMethodAcquirer (NOLOCK) PMA ON rp.PaymentMethodId = pma.PaymentMethodId
LEFT OUTER JOIN dbo.CashBookReference (NOLOCK) CB ON pma.AcquirerId = cb.AcquirerId AND CB.BaseCountryId = POS.CurrencyId
LEFT OUTER JOIN dbo.Receipt (NOLOCK) R ON POS.receiptid = r.receiptid -- added in v3 for PaymentMethodAcquirer CASE clause
LEFT OUTER JOIN dbo.PaymentMethod (NOLOCK) PM ON pm.PaymentMethodId = rp.PaymentMethodId -- required for Payment Description
LEFT OUTER JOIN
(SELECT
pos2.BaseReceiptId
,MIN(pos2.DateBilled)AS DateBilled

FROM POSItem (NOLOCK) POS2

GROUP BY
pos2.BaseReceiptId
)MIN ON min.BaseReceiptId = pos.BaseReceiptId -- added to account for timing differences on refunds for WorldPay and VAT on Premier


WHERE
COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) >= '20160731' --COALESCE added to insure DateTransacted falls into the period
AND COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) < '20160810'
AND (
PMA.AcquirerId = CASE WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
THEN CASE when rp.PaymentMethodId in (4,5)
then CASE when pos.CurrencyId = 2
and rp.countryid = 2
then 8
else 1
END
else PMA.Acquirerid
END
ELSE CASE when rp.PaymentMethodId in (4,5)
then CASE when pos.CurrencyId = 2
and rp.countryid = 2
then 1
else 1
END
else PMA.Acquirerid
END
END
OR rp.PaymentMethodId = 11) -- to filter out duplication caused by WorldPay



GROUP BY
CAST( COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) AS DATE)
,CASE
WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
AND rp.PaymentMethodId in (4,5)
AND pos.CurrencyId = 2
AND rp.countryid = 2
THEN 'WorldPay'

WHEN pm.PaymentDescription IN ('Sofort', 'iDEAL')
THEN 'Direct Transfers'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode IN ('AUD')
THEN 'NAB'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode NOT IN('AUD')
THEN 'Barclays'

WHEN pm.PaymentDescription NOT IN ('PayPal', 'American Express', 'Laser', 'Sofort', 'iDEAL', 'Klarna')
AND c2.CurrencyCode NOT IN('AUD') AND cb.Reference IS NULL
THEN 'Voucher'

ELSE pm.PaymentDescription
END
,c2.CurrencyCode
,cb.Reference


That provides the below output.....
Current Output

what I would like to do is in every case that the OA cash book is blank that it will change the Cash Acquirer to 'Voucher'

Can anyone advise on the best way to do this?

Apologies for the lengthy code but I thought it best to include it all.

Required output would be for all cash books that are blank the Cash Acquirer would be vouchers as below:

Required Output

Answer

You have to include a condition (OR cb.Reference = '') in the Case when part that returns the string 'Voucher'. Try like this,

SELECT CAST(COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) AS DATE) AS Date_Transacted
    ,CASE 
        WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
            AND rp.PaymentMethodId IN (
                4
                ,5
                )
            AND pos.CurrencyId = 2
            AND rp.countryid = 2
            THEN 'WorldPay'
        WHEN pm.PaymentDescription IN (
                'Sofort'
                ,'iDEAL'
                )
            THEN 'Direct Transfers'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode NOT IN ('AUD')
            AND (
                cb.Reference IS NULL
                OR cb.Reference = ''
                )
            THEN 'Voucher'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode IN ('AUD')
            THEN 'NAB'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode NOT IN ('AUD')
            THEN 'Barclays'
        ELSE pm.PaymentDescription
        END AS Cash_Acquirer
    ,c2.CurrencyCode
    ,cb.Reference AS OA_Cash_Book
    ------------------------------------------------------------------------------------------------
    --------------------------------Cash In--------------------------------------------------------
    ,(
        SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('I')
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('I')
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- New_Billed_Orders_Curr
        + SUM(CASE 
                WHEN (
                        (
                            pos.VoidHeaderId IS NULL
                            OR pos.VoidHeaderId = 0
                            )
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection = 'I'
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        (
                            pos.VoidHeaderId IS NULL
                            OR pos.VoidHeaderId = 0
                            )
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection = 'I'
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Voucher_Purchased_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('I')
                        AND pos.PriceIncTax < 0
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('I')
                        AND pos.PriceIncTax < 0
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Voucher_Redeemed_Curr
        + SUM(CASE 
                WHEN (
                        pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'I'
                            ,'V'
                            )
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        AND cfd.CashFeedTypeId IN (8) -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax / (1 + CT.TaxRate) * pos.Quantity
                WHEN (
                        pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'I'
                            ,'V'
                            )
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        AND cfd.CashFeedTypeId IN (8) -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax / (1 + CT.TaxRate) * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Premier_Subscription_Curr
        + SUM(CASE 
                WHEN (
                        pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'I'
                            ,'V'
                            )
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        AND cfd.CashFeedTypeId IN (11) -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax / (1 + CT.TaxRate) * CT.TaxRate * pos.Quantity
                WHEN (
                        pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'I'
                            ,'V'
                            )
                        AND pos.PriceIncTax > 0
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.DateBilled IS NOT NULL -- added to combine Cash IN & Cash OUT
                        AND cfd.CashFeedTypeId IN (11) -- added to combine Cash IN & Cash OUT
                        )
                    THEN pos.PriceIncTax / (1 + CT.TaxRate) * CT.TaxRate * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Premier_Subscription_Vat_Curr 
        + SUM(CASE 
                WHEN cfd.CashFeedTypeId = 17
                    AND pos.CashDirection IN ('I')
                    THEN (pos.PriceIncTax * (pos.Quantity * - 1)) * 2
                ELSE 0
                END) --ALIST_Script_Correction1
        + SUM(CASE 
                WHEN cfd.CashFeedTypeId = 19
                    AND pos.CashDirection IN (
                        'I'
                        ,'V'
                        )
                    THEN (pos.PriceIncTax * (pos.Quantity * - 1)) * 2
                ELSE 0
                END) --ALIST_Script_Correction2
        ) AS Cash_In_Curr
    ------------------------------------------------------------------------------------------------
    --------------------------------Cash Out--------------------------------------------------------
    ,(
        SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('O')
                        AND pos.DateCancelled IS NULL
                        AND (
                            CAST(pos.DateBilled AS DATE) = CAST(cfd.DateTransacted AS DATE)
                            OR CAST(pos.DateRefunded AS DATE) = CAST(cfd.DateTransacted AS DATE)
                            )
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('O')
                        AND pos.DateCancelled IS NULL
                        AND (
                            CAST(pos.DateBilled AS DATE) = CAST(cfd.DateTransacted AS DATE)
                            OR CAST(pos.DateRefunded AS DATE) = CAST(cfd.DateTransacted AS DATE)
                            )
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Basket_Refunded_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        --AND pos.DateCancelled  IS NOT NULL            
                        AND pos.DateCancelled >= '20160731'
                        AND pos.DateCancelled < '20160810'
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        --AND pos.DateCancelled  IS NOT NULL            
                        AND pos.DateCancelled >= '20160731'
                        AND pos.DateCancelled < '20160810'
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Basket_Cancelled_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.VoidHeaderId <> 0
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.PriceIncTax > 0
                        AND pos.CashDirection IN ('O')
                        AND pos.DateBilled IS NOT NULL
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.VoidHeaderId <> 0
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.PriceIncTax > 0
                        AND pos.CashDirection IN ('O')
                        AND pos.DateBilled IS NOT NULL
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Voucher_Reissued_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('O')
                        AND pos.DateCancelled IS NOT NULL
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptVoucherId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NULL
                        AND pos.CashDirection IN ('O')
                        AND pos.DateCancelled IS NOT NULL
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Voucher_Cancelled_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cfd.CashFeedTypeId = 9
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax / (1 + ct.TaxRate) * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cfd.CashFeedTypeId = 9
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax / (1 + ct.TaxRate) * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Premier_Subscription_Refund_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cfd.CashFeedTypeId = 12
                        AND cb.CashBookReferenceId IS NULL
                        )
                    THEN pos.PriceIncTax / (1 + ct.TaxRate) * ct.TaxRate * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NOT NULL
                        AND pos.ReceiptShippingSubscriptionId IS NOT NULL
                        AND pos.CashDirection IN (
                            'O'
                            ,'V'
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cfd.CashFeedTypeId = 12
                        AND cb.CashBookReferenceId IS NOT NULL
                        )
                    THEN pos.PriceIncTax / (1 + ct.TaxRate) * ct.TaxRate * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Premier_Subscription_Refund_Vat_Curr
        + SUM(CASE 
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.IsChequeRefund IN (
                            0
                            ,1
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cb.CashBookReferenceId IS NULL
                        AND pos.CashDirection IN ('O')
                        )
                    THEN pos.PriceIncTax * pos.Quantity
                WHEN (
                        pos.VoidHeaderId IS NULL
                        AND pos.IsChequeRefund IN (
                            0
                            ,1
                            )
                        AND pos.DateRefunded IS NOT NULL
                        AND cb.CashBookReferenceId IS NOT NULL
                        AND pos.CashDirection IN ('O')
                        )
                    THEN pos.PriceIncTax * pos.Quantity * pos.ExchangeRate
                ELSE 0
                END) -- Goodwill_Curr
        + SUM(CASE 
                WHEN cfd.CashFeedTypeId = 17
                    AND pos.CashDirection IN ('Z') -- Ignore and set to 0
                    THEN (pos.PriceIncTax * (pos.Quantity * - 1)) * 2
                ELSE 0
                END) --ALIST_Script_Correction1
        + SUM(CASE 
                WHEN cfd.CashFeedTypeId = 19
                    AND pos.CashDirection IN (
                        'O'
                        ,'V'
                        )
                    THEN (pos.PriceIncTax * (pos.Quantity * - 1)) * 2
                ELSE 0
                END) --ALIST_Script_Correction2
        ) AS Cash_Out_Curr
FROM dbo.POSItem(NOLOCK) POS
LEFT OUTER JOIN dbo.ReceiptPayment(NOLOCK) RP ON rp.ReceiptPaymentId = pos.ReceiptPaymentId
LEFT OUTER JOIN dbo.CountryTax(NOLOCK) CT ON rp.CountryId = ct.CountryId
    AND COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) >= ct.DateFrom
    AND (
        COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) < ct.DateTo
        OR ct.DateTo IS NULL
        ) --amended to combine Cash IN & Cash OUT
LEFT OUTER JOIN dbo.STATE (NOLOCK) S ON rp.CountryId = s.CountryId
    AND rp.County = s.Code
LEFT OUTER JOIN dbo.Country(NOLOCK) C1 ON rp.CountryId = c1.CountryId
LEFT OUTER JOIN dbo.Country(NOLOCK) C2 ON pos.CurrencyId = c2.CountryId
--LEFT OUTER JOIN dbo.OpenAccountsCurrency (NOLOCK) OACURR ON pos.CurrencyId = oacurr.CurrencyId --Not required for the query
LEFT OUTER JOIN dbo.CashFeedPOSItem(NOLOCK) CFP ON pos.POSItemId = cfp.POSItemId
LEFT OUTER JOIN dbo.CashFeedDetail(NOLOCK) CFD ON cfp.CashFeedDetailId = cfd.CashFeedDetailId
LEFT OUTER JOIN dbo.PaymentMethodAcquirer(NOLOCK) PMA ON rp.PaymentMethodId = pma.PaymentMethodId
LEFT OUTER JOIN dbo.CashBookReference(NOLOCK) CB ON pma.AcquirerId = cb.AcquirerId
    AND CB.BaseCountryId = POS.CurrencyId
LEFT OUTER JOIN dbo.Receipt(NOLOCK) R ON POS.receiptid = r.receiptid -- added in v3 for PaymentMethodAcquirer CASE clause
LEFT OUTER JOIN dbo.PaymentMethod(NOLOCK) PM ON pm.PaymentMethodId = rp.PaymentMethodId -- required for Payment Description
LEFT OUTER JOIN (
    SELECT pos2.BaseReceiptId
        ,MIN(pos2.DateBilled) AS DateBilled
    FROM POSItem(NOLOCK) POS2
    GROUP BY pos2.BaseReceiptId
    ) MIN ON min.BaseReceiptId = pos.BaseReceiptId -- added to account for timing differences on refunds for WorldPay and VAT on Premier
WHERE COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) >= '20160731' --COALESCE added to insure DateTransacted falls into the period
    AND COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) < '20160810'
    AND (
        PMA.AcquirerId = CASE 
            WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
                THEN CASE 
                        WHEN rp.PaymentMethodId IN (
                                4
                                ,5
                                )
                            THEN CASE 
                                    WHEN pos.CurrencyId = 2
                                        AND rp.countryid = 2
                                        THEN 8
                                    ELSE 1
                                    END
                        ELSE PMA.Acquirerid
                        END
            ELSE CASE 
                    WHEN rp.PaymentMethodId IN (
                            4
                            ,5
                            )
                        THEN CASE 
                                WHEN pos.CurrencyId = 2
                                    AND rp.countryid = 2
                                    THEN 1
                                ELSE 1
                                END
                    ELSE PMA.Acquirerid
                    END
            END
        OR rp.PaymentMethodId = 11
        ) -- to filter out duplication caused by WorldPay                    
GROUP BY CAST(COALESCE(pos.DateBilled, pos.DateRefunded, pos.DateCancelled) AS DATE)
    ,CASE 
        WHEN COALESCE(min.DateBilled, pos.DateBilled, pos.DateRefunded) > '2014-06-20 04:55:40:010'
            AND rp.PaymentMethodId IN (
                4
                ,5
                )
            AND pos.CurrencyId = 2
            AND rp.countryid = 2
            THEN 'WorldPay'
        WHEN pm.PaymentDescription IN (
                'Sofort'
                ,'iDEAL'
                )
            THEN 'Direct Transfers'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode IN ('AUD')
            THEN 'NAB'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode NOT IN ('AUD')
            THEN 'Barclays'
        WHEN pm.PaymentDescription NOT IN (
                'PayPal'
                ,'American Express'
                ,'Laser'
                ,'Sofort'
                ,'iDEAL'
                ,'Klarna'
                )
            AND c2.CurrencyCode NOT IN ('AUD')
            AND cb.Reference IS NULL
            THEN 'Voucher'
        ELSE pm.PaymentDescription
        END
    ,c2.CurrencyCode
    ,cb.Reference
Comments