SQL |将商店现金与银行现金匹配 [英] SQL | Match Shop cash with Bank Cash
问题描述
！ -我不是在寻找可以完成这项工作的付费软件(因为价格太贵)
! - 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.
我有两个SQL表，我们将其称为 SHOP_CASH 和 BANK_CASH
I have two SQL Tables, let's call it SHOP_CASH and BANK_CASH
1)匹配应基于ShopName-CashAmount-Date进行.
1) The matching should be happens based on ShopName-CashAmount-Date.
2)在这里我遇到了两个问题
2) Here I faced two issues
-
现金应四舍五入至最接近的£50，理想情况下，12400和12499应四舍五入至12450，或者这只是 IDEAL 是基于现金差额的匹配项小于50，干燥以匹配不同的值(如果差异小于50)，将它们匹配，但这是如何将值匹配起来的问题.这只是愚蠢的想法))???嗯...卡住了.
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.
日期，该商店可以在几天后兑现，因此需要根据兑现日期(例如2018-10-26)和银行日期为RANGE 2018-10-26到(+7)加入天)2018-11-02
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
尝试: 假设我可以按SHOPNAME联接两个表-酷 然后，我将尝试按日期加入，可能是:
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
PS .对于此项目，将使用Google Big Query.
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)
推荐答案
这是一个非常有趣的案例.
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
这篇关于SQL |将商店现金与银行现金匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
