SQL | Match Shop cash with Bank Cash

Question

! - I'm not looking for paid software which will do this job (as too expensive)

We have an issue with cash management to match the values.

I have two SQL Tables, let's call it SHOP_CASH and BANK_CASH

1) The matching should be happens based on ShopName-CashAmount-Date.

2) Here I faced two issues

1. The cash should be round up to nearest £50, ideally, 12 400 and 12 499 should round up to 12 450, OR this just IDEAL is a match based on cash difference which less than 50, dry to match different value if the difference is less than 50, match them, but here is the question how to match the value up.. this is just the stupid ideas))??? Hmmm...stuck.

2. Dates, the shop can cash up a few days later, so need to join based on cash-up date (for example 2018-10-26) with bank date RANGE 2018-10-26 to (+7 days) 2018-11-02

Currently, I do not understand the possible way (logical) of matching in this circumstance. Any logical path of calculation/joining will be extremely appreciated

TRY: Let's say I can join two tables by SHOPNAME - Cool Then I will try to join by date, which potentially will be:

SELECT * FROM SHOP_CASH AS SC
LEFT JOIN BANK_CASH AS BC
ON SC.SHOP_NAME_SC = BC.SHOP_NAME_BC
AND SC.DATE_SC = (ANY DATE FROM SC.DATE_SC TO SC.DATE_SC (+7 DAYS) = TO DATE_BC - not sure how)
AND FLOOR(SC.CASH_SC / 50) * 50 = FLOOR(BC_CASH_BC / 50) * 50

P.S. For this project will be using the Google Big Query.

This is my (temporary solution)

WITH MAIN AS(SELECT 
CMS.Store_name AS STORE_NAME,
CMS.Date AS SHOP_DATE,
CMB.ENTRY_DATE AS BANK_DATE,
SUM(CMS.Cash) AS STORE_CASH,
SUM(CMB.AMOUNT) AS BANK_CASH
FROM `store_data` CMS
LEFT JOIN `bank_data` AS CMB
ON CMS.store_name = CMB.STRAIGHT_LOOKUP
AND FLOOR(CMS.Cash / 50) * 50 = FLOOR(CMB.AMOUNT / 50) * 50
AND CAST(FORMAT_DATE("%F",CMB.ENTRY_DATE) AS STRING) > CAST(FORMAT_DATE("%F",CMS.Date) AS STRING)
AND CAST(FORMAT_DATE("%F",CMB.ENTRY_DATE) AS STRING) <= CAST(FORMAT_DATE("%F",DATE_ADD(CMS.Date, INTERVAL 4 day)) AS STRING)
GROUP BY STORE_NAME,SHOP_DATE,BANK_DATE)


SELECT 
    MAIN2.*
FROM (
  SELECT
  ARRAY_AGG(MAIN ORDER BY MAIN.SHOP_DATE ASC LIMIT 1)[OFFSET(0)] AS MAIN2
  FROM
    MAIN AS MAIN
    GROUP BY MAIN.SHOP_DATE, MAIN.STORE_CASH)

Answer 1

this is quite interesting case.

You haven't provided any sample data so I'm not able to test it, but this may work. Some modification may be required since not sure about date format. Let me know if there is an issue.

SELECT * FROM SHOP_CASH AS SC
LEFT JOIN BANK_CASH AS BC
ON SC.SHOP_NAME_SC = BC.SHOP_NAME_BC
AND  SC.DATE_SC BETWEEN BC.DATE_BC  AND DATE_ADD(BC.DATE_BC, DAY 7)
AND trunc(SC.CASH_SC, -2) + 50  = trunc(BC.CASH_BC,2) + 50