I need some SQL scripting assistance.
We have a manual AR payment entry process where the end user manually creates a batch id based upon certain transactional data.
I am creating an integration and I need to mimic in SQL what the end user currently does.
For example, purposes - here's a data table and some data to use for illustration:
CREATE TABLE BATCH (
BATCHID varchar (20),
POSTINGDATE date,
CUSTNAME varchar (100),
TRANSACTIONNUMBER varchar (30)
)
INSERT INTO BATCH
VALUES
('', '2024-01-12', 'JOHNSON', 'YP001'),
('', '2024-01-12', 'JOHNSON', 'YP001'),
('', '2024-01-12', 'WILLIAMS', 'YP002'),
('', '2024-01-12', 'TYLER', 'YP003'),
('', '2024-01-12', 'JOHNSON', 'YP004');
which gives us this:
I need to create the Batchid using the following logic/schema (15 Characters)
date (no hyphens 8 characters) (plus)
CUSTNAME (first 6 characters) (plus)
Sequential (single number) based on transaction grouping - getting this last value is tricky.
ROW1 and ROW2 are same transaction, posting date and therefore BATCHID=20240112JOHNSO1
ROW3 straightforward BATCHID=20240112WILLIA1
ROW4 straightforward BATCHID=20240112TYLER 1
ROW5 Tricky - second transaction for JOHNSON - BATCHID=20240112JOHNSO2
Basically a unique Batch for each Customer/Transaction grouping
I can get most of it but trying to get the sequential transaction part of it has me stumped.
Appreciate any assistance - thank you in advance.