How do I insert into database last day of month before new month transaction

Question

0.00/5 (No votes)

See more:

Date         next date.   Qty.  Item1.  Item2
2020-08-01.  2020-08-15.   2.     1.      1
2020-08-15.  2020-08-28.   3.     1.      2
2020-08-28.  ????
2020-09-01

Insert the last day of prev month before inserting new month into db.

SQL

Select distinct t1.AccNo,t1.in date,ifnull((select min(t1.min.in date) from t1 as t1min where t1min.in date>t1.in date and t1.AccNo = t1min.AccNo),
Date(t1.in date,'start of month', '+1 month', '0 day')) as nextdate

What I have tried:

I don't know where to start. The db gets its data from form1. Which is inserted into t1. A create query in sqlite does the next date so I can work out the no of days between each next date and charge per days.

Posted 23-Sep-20 9:49am

Member 14563525

Updated 23-Sep-20 10:43am

Maciej Los

v2

Add a Solution

Comments

j snooze 23-Sep-20 17:21pm

generally you can just take the 1st day of the next month and subtract a day to get the last day of any particular month. in c# you could take the date of 9/1 and do a .AddDays(-1) on the DateTime Object.

Member 14563525 23-Sep-20 17:41pm

OK. My apologies. The next transaction for the new month can be on the 2020-09-05. The query will automatically take this date as the next date which then is incorrect .eg in the above from 28/8 to 31/08 is 4days of hiring. I need to automatically insert a next date on the last transaction for previous month as the last date of that month before the new hits the db.

Richard MacCutchan 24-Sep-20 3:50am

So take the entered date, set the day to 1 and subtract 1. That will give you the last day of the previous month.

Member 14563525 25-Sep-20 4:48am

That would not work. Some transaction will take place on the 06 of the following month.
At the moment a forced monthend run is done dating all customers with a zero bal sale. That then puts the last day. The custoner is charged per day of usage. So if he had 10 items on the 15/08 he will be charged for 15 days up to tye 15th. But on the 16th he returned 4 of that items . So on the 16th he will be charged for 6items. And onthe last day of the month it has to be for that month otherwise the days charged at the end of the month will be wrong

Richard MacCutchan 25-Sep-20 5:05am

OK, so add 1 to the month, set the day to 1 and subtract 1 from the days. Whichever way you look at it it is a couple of simple statements.

Member 14563525 26-Sep-20 15:56pm

Where would I do that. On the form when date is more than last date or run a stored procedure on the date box changed.

Richard MacCutchan 27-Sep-20 3:19am

I don't know, as I have no knowledge of your application. But it should be a simple matter for you to figure out.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)