Solved Average Date Setting (1 Viewer)

[smtazulislam]

We have 1297+ foreigner workers who is work with us as work resident permit (WRP) . We called this resident permit card "IQAMA". Iqama have valid date and it is can renew by 3 Months / 6 Months / 9 month and 12 month (1yr) upto 3 yrs .

We facing a problem that some month we have 250+ Iqama's for renew list and some month is 50 + AND some month is NULL.

So, we would like to a query that all employee EXPIRED date / 12 Months = if we wish divide to [By 3 Months] or [By 6 Months] Or [By 9 Months] Or [By 12 Months]
Then lists become average same QUANTITY in per month/Year.

MONTH NAME	NOW [QUANTITY]	EXPIRE DATE	WE LOOKING
JAN	0	1-1-2023 TO 31-01-2023
FEB	258	1-2-2023 TO 28-02-2023
MAR	308
APR	15
MAY	25
JUN	168
JUL	354
AUG	2
SEP	5
OCT	125
NOV	32
DEC	5
TOTAL	1297

Here we need same quantity by 12 Month BY DIVIDE EXPIRE_DATE

 

[CJ_London]

A date is a date so your description does not make sense - provide an example of what you want for February

 

[GPGeorge]

We have 1297+ foreigner workers who is work with us as work resident permit (WRP) . We called this resident permit card "IQAMA". Iqama have valid date and it is can renew by 3 Months / 6 Months / 9 month and 12 month (1yr) upto 3 yrs .

We facing a problem that some month we have 250+ Iqama's for renew list and some month is 50 + AND some month is NULL.

So, we would like to a query that all employee EXPIRED date / 12 Months = if we wish divide to [By 3 Months] or [By 6 Months] Or [By 9 Months] Or [By 12 Months]
Then lists become average same QUANTITY in per month/Year.

MONTH NAME	NOW [QUANTITY]	EXPIRE DATE	WE LOOKING
JAN	0	1-1-2023 TO 31-01-2023
FEB	258	1-2-2023 TO 28-02-2023
MAR	308
APR	15
MAY	25
JUN	168
JUL	354
AUG	2
SEP	5
OCT	125
NOV	32
DEC	5
TOTAL	1297

Here we need same quantity by 12 Month BY DIVIDE EXPIRE_DATE

I think what you want is not an average of dates. I think what you want is an average of the number of renewals for each month. That is represented by the column "Now [Quantity]" in your screenshot.

Please confirm if that guess is correct as to what you want.

 

[Pat Hartman]

What is the purpose of the calculation? Are you trying to even out your workload? An average isn't going to help. You need to "create" the average by distributing the expiration dates as you assign the work visas. So, if you issue a new 3-month visa today but your workload is full up for the third month out, then either the visa has to be one month longer or one month shorter to have it expire in a different month. To get yourself a starting point, you will need to recalculate the expiration dates for all existing visas.

 

[smtazulislam]

A date is a date so your description does not make sense - provide an example of what you want for February

Thanks for your comment.
JAN TO DEC is starting date 01 to end of the month total quantity we have 1297.

MONTH NAME	TOTAL QUANTITY	EXPIRE DATE	WE LOOKING
JAN	0	[starting date 01/01/2023] to [31/01/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
FEB	258	[starting date 01/02/2023] to [28/02/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
MAR	308	[starting date 01/03/2023] to [31/03/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
APR	15	[starting date 01/04/2023] to [30/04/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
MAY	25	[starting date 01/05/2023] to [31/05/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
JUN	168	[starting date 01/06/2023] to [30/06/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
JUL	354	[starting date 01/07/2023] to [31/07/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
AUG	2	[starting date 01/08/2023] to [31/08/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
SEP	5	[starting date 01/09/2023] to [30/09/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
OCT	125	[starting date 01/10/2023] to [31/10/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
NOV	32	[starting date 01/11/2023] to [30/11/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
DEC	5	[starting date 01/12/2023] to [31/12/2023 end of the month]	TOTAL EMPLOYEE / 12 MONTHS
TOTAL	1297

normally, IF i do it by manually then result is 108 + worker per month.
But problem is you have giving perfect calculation within renewal conditions system by 3 months or 6 months or 9 Months,

 

[smtazulislam]

I explain it here a months.

EmployeeID	Expire Date		Renewal Method
		You can renewal 3 Months	Or You can renewal 6 Months	Or You can renewal 9 Months
JAN
1325​	06/01/2023​	06/03/2023	06/06/2023	06/09/2023
891​	09/01/2023​
1034​	10/01/2023​
1272​	11/01/2023​
1037​	14/01/2023​
41​	15/01/2023​
835​	19/01/2023​
1202​	20/01/2023​
1201​	20/01/2023​
786​	22/01/2023​
388​	24/01/2023​
391​	24/01/2023​
1159​	27/01/2023​
1291​	28/01/2023​
646​	01/02/2023​
FEB
1408​	02/02/2023​
1318​	06/02/2023​
1492​	07/02/2023​
332​	09/02/2023​
1142​	09/02/2023​
148​	11/02/2023​
796​	12/02/2023​
455​	15/02/2023​
1604​	21/02/2023​
476​	21/02/2023​
454​	21/02/2023​
1497​	22/02/2023​
1498​	22/02/2023​
1499​	22/02/2023​
1500​	22/02/2023​
1076​	22/02/2023​
1071​	22/02/2023​
1496​	22/02/2023​
1250​	23/02/2023​
1414​	23/02/2023​
798​	24/02/2023​
560​	25/02/2023​
559​	25/02/2023​
1630​	25/02/2023​
1102​	25/02/2023​
1059​	25/02/2023​
1303​	25/02/2023​
1028​	25/02/2023​
1227​	26/02/2023​
1130​	26/02/2023​
1226​	26/02/2023​
1178​	26/02/2023​
974​	26/02/2023​
1187​	26/02/2023​
1176​	26/02/2023​
1188​	26/02/2023​
1433​	26/02/2023​
1182​	26/02/2023​
1184​	26/02/2023​
1179​	26/02/2023​
1185​	26/02/2023​
1019​	27/02/2023​
1229​	27/02/2023​
186​	27/02/2023​
1505​	28/02/2023​
1506​	28/02/2023​
1507​	28/02/2023​
1030​	28/02/2023​
1508​	28/02/2023​
1502​	28/02/2023​
1610​	28/02/2023​
1503​	28/02/2023​
613​	28/02/2023​
1501​	28/02/2023​
608​	28/02/2023​
1504​	28/02/2023​

We looking average QUANITY each months.

 

[smtazulislam]

I think what you want is not an average of dates. I think what you want is an average of the number of renewals for each month. That is represented by the column "Now [Quantity]" in your screenshot.

Please confirm if that guess is correct as to what you want.

if you're make average of dates by condition method of renewal that I explain (3m / 6m / 9m ) then quantity will average came.

 

[smtazulislam]

What is the purpose of the calculation? Are you trying to even out your workload? An average isn't going to help.

Its not like workload, But it is moneyload, ha ha ha
A worker visa renewal most money. some month is over the budget for renewal Resident CARD. And some month is NULL.
We need it to do by average calculation to get each month is same quantity.

You need to "create" the average by distributing the expiration dates as you assign the work visas. So, if you issue a new 3-month visa today but your workload is full up for the third month out, then either the visa has to be one month longer or one month shorter to have it expire in a different month. To get yourself a starting point, you will need to recalculate the expiration dates for all existing visas.

We have Visa or Resident CARD expire date. And available method with 3m or 6m or 9m to renewable. We can 12 Months renew also, its lead same date/Month.

 

[CJ_London]

We need it to do by average calculation to get each month is same quantity.

so that is the 108 you calculated.

So are you trying to determine the variance - difference between 0 and 108 for jan, 258 and 108 for Feb, etc?

or are you trying to work out that if an employee renews in Jan, then the next renewal date will be whatever based on the the renewal period for that employee?

you description of the requirement is confusing, - provide some example results and show how you calculate that result

 

[smtazulislam]

so that is the 108 you calculated.

So are you trying to determine the variance - difference between 0 and 108 for jan, 258 and 108 for Feb, etc?

or are you trying to work out that if an employee renews in Jan, then the next renewal date will be whatever based on the the renewal period for that employee?

you description of the requirement is confusing, - provide some example results and show how you calculate that result

I am sorry for delay response. This is the sample data.
By Month

Here Renew Method

 

[CJ_London]

getting the data but still don't understand the requirement.

For an employee you have an expiring date, but nothing to indicate whether they are on a 3, 6, 9 or 12 month contract. If there was I could hazard a guess on the requirement which is to map all future 'rehire' dates within the next 12 months or perhaps just the remainder of this year

And why are you determining this based on expiry date +90 etc rather that adding 3/6/9/months? using days could provide misleading results.

your union query will eliminate duplicate dates, giving false results. I suspect you need to use UNION ALL but still not clear to me the required result.

 

[smtazulislam]

getting the data but still don't understand the requirement.

For an employee you have an expiring date, but nothing to indicate whether they are on a 3, 6, 9 or 12 month contract. If there was I could hazard a guess on the requirement which is to map all future 'rehire' dates within the next 12 months or perhaps just the remainder of this year

First all, it not contract Expire Date. It was RESIDENT CARD expiry date. Government method by renew within 3/6/9/12 M

And why are you determining this based on expiry date +90 etc rather that adding 3/6/9/months? using days could provide misleading results.

I tried to showed that If I will RENEW 3/6/9/12 M then what is date exactly became the Expiry Date.
You can see there have a CURRENTDATE what have the present date. And We should use DAYS here, because it had converts to ARABIC To ENGLISH date, I knew, maybe misleading result given 1 or 2 days more extra. But we dont have any others option.

your union query will eliminate duplicate dates, giving false results. I suspect you need to use UNION ALL but still not clear to me the required result.

I did UNION ALL for 12m including for TEST a query by 12 M. I tried it myself another away but fail.

 

[CJ_London]

ok so resident card, not contract. You still have not explained what result you actually want. There has to be some basis for knowing what period you are renewing each employee for - 3 6, 9 or 12 months and I don't see it. Or perhaps you want to try to balance things out by saying that for an employee this time we will renew for 3 months, next time for 6 months and for another employee we will renew for 9 months and the next time for more than 3 months.

So do as I requested many posts ago - show what calculation you require to get the required result.

If you are unable to clearly explain the requirement, I am unable to help,

 

[smtazulislam]

ok so resident card, not contract. You still have not explained what result you actually want. There has to be some basis for knowing what period you are renewing each employee for - 3 6, 9 or 12 months and I don't see it. Or perhaps you want to try to balance things out by saying that for an employee this time we will renew for 3 months, next time for 6 months and for another employee we will renew for 9 months and the next time for more than 3 months.

Look the tblEmployee - ExpiredDateEn field date is PRESENT DATE. Now need to renew for example JANUARY Or FEBRUARY 2023.
Open QUERY "Count_By_Month", you can see the MONTHLY quantity available

If you looks MARCH 2023 is 200 Quantity.
If you looks SEPTEMBER 2023 is 9 quantity only.

So, I need every month have equal same quantity. NOT like the Query "Count_By_Month"
With have a condition : 3 / 6 / 9 / 12 M can be renew. For that I do this Query "Probable_Renew_Date" to show the result.

Example:
Say you have 84 Employee. if you divide by 12 then it is result each month "7"
Hope you understand now.

One more example :
Say employee : 3 is Expire 05/03/2023
You can renew that guy with 3 Months or 6 Months Or 9 Months Or 12 Months.

Thank you very much.

 

[ebs17]

You can renew that guy with 3 Months or 6 Months Or 9 Months Or 12 Months.

Why not always 12 months? That would reduce the administrative burden.

 

[smtazulislam]

Why not always 12 months? That would reduce the administrative burden.

Thanks for your response. We can, But problem is money crisis. If it is average every month equal same quantity then we maybe do it.

 

[ebs17]

The question you should answer out loud would be: does the renewal over 12 months cost the same as over 3 months?
If not, what is the plan of distribution over 3, 6 or 12 months?

Additional problem: You are counting on months, but in the database you have data from 2022, 2023, 2024. A month repeats itself every year, a month data is only meaningful in connection with the year.

What exactly you want, I have also not understood - which is bad after the already many contributions.

What is simple: you can count the number of people in a period from - to and break that number down to a monthly average.
You could additionally generate a running number by expiration date, and then always select those people who are closest to expiration and would need to be renewed first. The sequential number can be used to update this on a monthly basis. This would be a logical process for me.

But if I now have renewals over 3 months and that in an uncertain number and allocation to persons, it is difficult to plan for a year. In any case, it becomes more complicated and you need a reliable and fixed plan.

I miss such a plan. If you can't present such a thing coherently and logically (because you don't know it yourself), then you won't be helped.

 

[CJ_London]

Example:
Say you have 84 Employee. if you divide by 12 then it is result each month "7"

why can't you use examples that relate to your data - it might make more sense to those trying to help

you have 696 employees so the average is 58.

So are you saying you have 46 in Jan, so bring forward 12 employees from a later month and in Feb you have 63 so push forward 5 employees to a later month and in March push forward 142 etc If so, on what basis do you choose the employees to move. I really don't see how any of this can work without knowing the renewal period.

Employee 40 has a renewal date of 4/1/2024 so is presumably out of scope for 2023 a count of employees with renewals for Feb 2023 is 57, not 63 as you show above and Jan 2023 shows 14 not 46. So presumably 32 of those have already been assigned a later renewal date.

And what basis are you going to use to 'move' a renewal date - bring forward so there is an overlap, push back in which case the employee is presumably not able to work without a valid resident card? Or perhaps there is a period of grace where a there can be a delay, in which case how much

 

[Josef P.]

I think once quite simply:
There is a sequence of items to be processed.
X points can be processed per month.
=>

select TOP x from ...  order by [next theoretical date]

.. These are the points to be processed from the current/next month.

Is this too simple thinking?

 

[smtazulislam]

The question you should answer out loud would be: does the renewal over 12 months cost the same as over 3 months?
If not, what is the plan of distribution over 3, 6 or 12 months?

Additional problem: You are counting on months, but in the database you have data from 2022, 2023, 2024. A month repeats itself every year, a month data is only meaningful in connection with the year.

What exactly you want, I have also not understood - which is bad after the already many contributions.

What is simple: you can count the number of people in a period from - to and break that number down to a monthly average.
You could additionally generate a running number by expiration date, and then always select those people who are closest to expiration and would need to be renewed first. The sequential number can be used to update this on a monthly basis. This would be a logical process for me.

But if I now have renewals over 3 months and that in an uncertain number and allocation to persons, it is difficult to plan for a year. In any case, it becomes more complicated and you need a reliable and fixed plan.

I miss such a plan. If you can't present such a thing coherently and logically (because you don't know it yourself), then you won't be helped.

No sense that why you don't understood, If you are looking POST # 10 and # 14. There clear presentation what I would like to do.

About renew :
There I saying that YOU CAN RENEW 3m Or 6m Or 9m Or 12m. You can't renew : 1m or 2m or 4m or 5m or 7m or 8m or 10m or 11m renew.

AND ExpireDateEN = Present Expire Date .
So, You can CHOOSE MONTH FROM 3M OR 6M OR 9M OR 12M For RENEW To adjust the date average Quantity equal 12 MONTHS.

We have to do ExpireDateEn field + choose anyone condition (3M OR 6M OR 9M OR 12M) for became result average equal 12 months. Do mind if it was going 2024 Or 2025 Or 2026