-1

! - 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)
  • something is missed in your business process! you should revisit what you capture from both Shops and Banks. Like orderID or ReceiptID or something that will be more deterministic to compare with fuzzy logic you are getting yourself into. Sure, formally JOIN with those three conditions can be written easily - so what?! You can imagine - it is not going to be one-to-one, most likely it will be many-to-many matches. What you will be doing then? No. Bad Idea! – Mikhail Berlyant Oct 26 '18 at 15:15
  • @MikhailBerlyant This do not a point to discuss what is wrong, this is where need to find a solution which will work. Btw I found something closer to my solution. –  Oct 29 '18 at 09:59
  • meant to pick your attention to potential serious issue in design. Ignore if you think it does not apply to your case. – Mikhail Berlyant Oct 29 '18 at 10:59
  • @MikhailBerlyant I understand it will be many-to-many here is the issue, as all what I have it shop_name, date, and cash_amount. If I will have like any sort of unique ID, this will not be an issue and I will not raise any question here )) –  Oct 29 '18 at 11:22

1 Answers1

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
AlienDeg
  • 1,288
  • 1
  • 13
  • 23
  • 1
    I have to add similar to your answer but a bit more updated. (sorry that have not provide sample data) –  Oct 29 '18 at 10:01