Please provide an algorithm implementation, thank you!

zijidexingfu

There are two types of records when entering and leaving the warehouse: incoming and outgoing.
Storage: from number to date
There are also out of the warehouse: starting number to date.
It is now necessary to merge the receipts and outbound receipts, that is, remove the outbound records from the inbound records.
There are the following situations:
Condition one:
     Before the merger:
            Date from date to date
     In 11/1 1 10
     In 11/2 11 15
     In 11/3 16 20
     After the merger:
         No. to No. of sheets
           1 10 10
          11 15 5
          16 20 5
Situation two:
    Before the merger:
           Date from date to date
    In 11/1 1 10
    In 11/2 11 15
    In 11/3 16 20
    Out 11/4 1 10
    After the merger:
          No. to No. of sheets
           11 15 5
           16 20 5
Situation three:
    Before the merger:
           Date from date to date
    In 11/1 1 10
    In 11/2 11 15
    In 11/3 16 20
    Out 11/4 8 13
   After the merger:
           No. to No. of sheets
             1 7 7
            14 15 2
            16 20 5
Situation four:
    Before the merger:
            Date from date to date
     In 11/1 1 10
     In 11/2 11 15
     In 11/3 16 20
     Out 11/4 8 17
    After the merger:
            No. to No. of sheets
              1 7 7
             18 20 3
Situation five:
           Date from date to date
     In 11/1 1 10
     In 11/2 11 15
     In 11/3 16 20
     Out 11/4 8 9
After the merger:
           No. to No. of sheets
             1 7 7
            10 10 1
            18 20 3
            11 15 5
            16 20 5
Wait. Please provide an algorithm implementation, thank you!

tellyou

Can I change the table structure?

Date number
Into 11/1 1
In 11/1 2
In 11/1 3
....
In 11/1 10
Out 11/2 1
Out 11/2 2
....

On the surface, this increases the amount of data, but the business logic of the insert, update, and merge queries is the simplest and most in line with the actual situation. I guess you just need the date from the last merged report-like thing, and the previous journal table is not necessary (even if the user enters the date from the UI, insert / update is only a cycle)

Otherwise, your design can save a lot of data, but it may cause a lot of calculation problems. The actual cost performance should be lower.
such as
In 11/1 1 10
Out 11/2 10 10
In 11/2 11 15
Out 11/3 4 5
Out 11/4 1 1
Out 11/5 12 13
Out 11/6 2 3
...

Moreover, even if you can come up with an algorithm, if the business logic changes slightly, it may be overturned.

Your existing table structure algorithm is thinking ...

zijidexingfu

Hi!tellyouThank you.
I don't know what you want to change the table structure.
In fact, in this problem, it is not insert or update the table in the database,
This use case simply queries the tables in the database to perform the above operations and displays them to the user.
It is the record that is queried from the first day of the month to that day.

omgcai

A bit difficult

tellyou

Yeah, of course I know what you want!

I mean, you have to choose the structure of the table from, it is difficult to get the results you want. Therefore, the method I propose to solve this problem is not just how to select, but the structure of the "from" table should not be what it is now (if you can change it).

BTW, mind if not using TC?
Although I can understand it, it really can't cause the "resonance" of my brain cells like SC, it looks really hard :)

zijidexingfu

hi ~tellyou. Very helpless. We do not have permission to change the table structure. But I want to know what you want to change.

tellyou

Reply to:tellyou() Reputation: 125 2017-01-04 10:13:34 Score: 0


Can I change the table structure?

Date number
Into 11/1 1
In 11/1 2
In 11/1 3
....
In 11/1 10
Out 11/2 1
Out 11/2 2
....

=========================
It is to put the original 1-10 into 1 record and turn it into 10 records.

After thinking about it for a long time, your original approach can only write stored procedures

zijidexingfu

Thank you!
In fact, if you change the structure of the table like this, I feel that it will also increase the burden of database operations.
If you want to come, you need to insert one, and now you need to insert 10 pens. Of course, the specific scenario needs to be measured.
Stored procedures are not allowed and can only be implemented in AP programs.
Is there any mathematical method that Dongdong can provide a good solution?