3 year cycle in query (1 Viewer)

[Chintsapete]

Hi all

I need to calculate a 3 years cycle for sick leave from Date of engagement. I tried with DateAdd and DateDiff, but somehow got stuck. How do I get to start the cycle over? Is there a way to do that in a query?
I have a table Salaries YTD where all the data is collected. I'm trying to do a query. I have so far a query called SickPrep:
SCStart: DateOfEngagment
SCEnd: DateAdd("m",36,[DateOfEngagment])

I made another query called SickLeave where I re-query the above:
Restart: DateAdd("m",36,[SCEnd])
But how can I get that to loop indefinite? I'm completely stuck with this and can't seem to find a solution.
Anyone's got an idea?
Thanks
Pete

 

[pr2-eugin]

Hello Chintsapete, Could you give the logic in words instead of formulas?

What I mean is instead of saying 5-x=3 find x, can you say something like Charlie had 5 apples, he ate one gave one to Paul how many does he have left? It sometimes is easier in words/using a real example.

 

[Chintsapete]

Hi Paul

hehehe, I got you. Thanks for coming back to me again. It seems to be I having all the funny problems.
OK in South Africa we got this silly law that an employee has 30 days sick leave due in 36 months. What I'm trying to do is to work out a cycle from date of engagement over a period of 36 months and than the cycle restarts from 0. I'd like to extract out of that the Sick days paid and sick days remaining in that period. I hope that makes more apples than before.
Cheers
Pete

 

[pr2-eugin]

I would say you need a function to achieve this. Although the questions still is a bit fuzzy to me, as I cannot understand how exactly your tables are set up or how you enter data into the tables !

 

[Chintsapete]

Hi all

I got a bit further in my seemingly impossible quest. I made one query to work out the dates looking as follows.

SELECT EmployeesDetailQ.[Emp#], EmployeesDetailQ.NickName, Sum([Salaries YTD].DaysSick) AS SumOfDaysSick, EmployeesDetailQ.YearsEmploy, (DateDiff("m",[DateofEngagment],Date())) AS MonthsEmpl, (DateDiff("m",[DateofEngagment],Date()))/36 AS [Cycle#], EmployeesDetailQ.DateOfEngagment, DateAdd("m",36*Round((DateDiff("m",[DateofEngagment],Date()))/36,0),[DateOfEngagment]) AS SCStart, DateAdd("m",36*Round((DateDiff("m",[DateofEngagment],Date()))/36,0),DateAdd("m",36,[DateOfEngagment])) AS SCEnd
FROM EmployeesDetailQ INNER JOIN [Salaries YTD] ON EmployeesDetailQ.[Emp#] = [Salaries YTD].[Emp#]
GROUP BY EmployeesDetailQ.[Emp#], EmployeesDetailQ.NickName, EmployeesDetailQ.YearsEmploy, (DateDiff("m",[DateofEngagment],Date())), (DateDiff("m",[DateofEngagment],Date()))/36, EmployeesDetailQ.DateOfEngagment, DateAdd("m",36*Round((DateDiff("m",[DateofEngagment],Date()))/36,0),[DateOfEngagment]), DateAdd("m",36*Round((DateDiff("m",[DateofEngagment],Date()))/36,0),DateAdd("m",36,[DateOfEngagment])), EmployeesDetailQ.LeftCompany
HAVING (((EmployeesDetailQ.LeftCompany)=No));

This gives me a start and end date I want. I made a second query which is querying the query above

SELECT SickLeavePrep.[Emp#], SickLeavePrep.NickName, SickLeavePrep.SCStart, SickLeavePrep.SCEnd, Sum(SickLeavePrep.DaysSick) AS SumOfDaysSick
FROM SickLeavePrep
GROUP BY SickLeavePrep.[Emp#], SickLeavePrep.NickName, SickLeavePrep.SCStart, SickLeavePrep.SCEnd
HAVING (((Sum(SickLeavePrep.DaysSick)) Between [SCStart] And [SCEnd]));

I thought it should work but it returns no result. Anyone has an idea why?

 

[namliam]

I guess the having is wrong... Sum(dayssick) should return an integer/double between 0 and max 1096 where your SCStart and SCEnd are dates, that having will never be right.

 

[Chintsapete]

Thanks for getting back to me. When I change the code to

SELECT SickLeavePrep.[Emp#], SickLeavePrep.NickName, SickLeavePrep.SCStart, SickLeavePrep.SCEnd, Sum(SickLeavePrep.DaysSick) AS SumOfDaysSick, (((Sum([SickLeavePrep].[DaysSick])) Between [SCStart] And [SCEnd])) AS SickDays
FROM SickLeavePrep
GROUP BY SickLeavePrep.[Emp#], SickLeavePrep.NickName, SickLeavePrep.SCStart, SickLeavePrep.SCEnd;

than it returns 0, instead of the correct number. The dates I calculate in the first query as you can see above and the property in the query is set as "short date".

 

[namliam]

0 is the same as False, which is proof that the sum..Between... will return a false, thus no records.

Why would you need such a Having anyways that says Random number between 0 and 1096 between date1 and date2
That will simply never work

What beyond the first query are you trying to achieve with the second?

 

[namliam]

I was thinking perhaps you are looking for something along these lines?

SELECT SickLeavePrep.[Emp#]
     , SickLeavePrep.NickName
     , SickLeavePrep.SCStart
     , SickLeavePrep.SCEnd
     , Sum(SickLeavePrep.DaysSick) AS SumOfDaysSick
     , Iif(Sum(SickLeavePrep.DaysSick)>30,0, 30-SumOfDaysSick) SickDaysLeftOver
     , Iif(Sum(SickLeavePrep.DaysSick)>30,SumOfDaysSick-30, 0) SickDaysTooMany
FROM SickLeavePrep
GROUP BY SickLeavePrep.[Emp#], SickLeavePrep.NickName, SickLeavePrep.SCStart, SickLeavePrep.SCEnd;

 

[Chintsapete]

Thanks a lot for that. There is one more problem though. In the table with the sick days there are 8 years of history. And I only need the days within SCStart and SCEnd. I tried to fiddle in Between SCStart and SCEnd but as soon as I do that there is no result.

Thanks a lot for your time, I do appreciate it. This is a way bigger headache than anticipated.

 

[CJ_London]

just trying to clarify what your require

What I'm trying to do is to work out a cycle from date of engagement over a period of 36 months and than the cycle restarts from 0

This says to me that an employee who started on 1 Jan 2010 and used 5 days sick on 10-15 Jan 2012 now has 30 days allowance because the new cycle started on 1 Jan 2013. Is this correct?

The alternative interpretation is that they are entitled to 30 days sick in any 36 month cycle - i.e. in the above example, his current allowance is 25 days

In either event, are you only interested in the current entitlement?

 

[namliam]

Presumably, that date is contained within the Salary table? Thus you should put that restriction in the first query?

Here is how I would do it I think... draw back is it will only show periods with only actual sick days in them

Select [Emp#]
     , int(dateDiff("m",DateofEngagement, [Salaries YTD].[FullDateofSalaryPayment]) / 36) + 1 as PeroidNumber
     , sum([Salaries YTD].dayssick) as SumOfDaysSick
FROM       EmployeesDetailQ 
INNER JOIN [Salaries YTD]     ON EmployeesDetailQ.[Emp#] = [Salaries YTD].[Emp#]
GROUP BY  [Emp#]
        , int(dateDiff("m",DateofEngagement, [Salaries YTD].[FullDateofSalaryPayment]) / 36) + 1
HAVING    EmployeesDetailQ.LeftCompany=No

If you prefer you can use the PeriodNumber column easily to convert that into a "From Date" and "To Date" to show the date period dependant upon the DateOfEngagement using Dateadd function

 

[Chintsapete]

The employee is entitled to 30 days sick leave in a period of 36 months from the date of employment. After that the slate should be wiped clean and start over with 30 days over 36 months. So yes your first statement correct.
If there is a way to have all the history cycles considered would be great. But current one is sufficient.
All the data since 2007 is in the one table "Salaries YTD". I have columns (besides all the others) for DaysSick, Date (pay run date), FinancialYear, Period (month in the financial year) to work with.
In a table "EmployeesDetails" I got the date of engagement etc which I append with the pay run into "Salaries YTD".

Up until now I had a separate table with sick leave history and appended to it separate. But it's tedious because the queries had to be altered every 3 years. What I'm trying to achieve is the payroll to run without having to that.

 

[namliam]

This should list all periods with start and end dates... for which there are salary slips...

Select [Emp#]
     , int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1 as PeroidNumber
     , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36)    ) as PeriodStartDate
     , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1) as PeriodEndDate
     , sum([Salaries YTD].dayssick) as SumOfDaysSick
FROM       EmployeesDetailQ 
INNER JOIN [Salaries YTD]     ON EmployeesDetailQ.[Emp#] = [Salaries YTD].[Emp#]
GROUP BY  [Emp#]
        , int(dateDiff("m",DateofEngagement, [Salaries YTD].[FullDateofSalaryPayment]) / 36) + 1
        , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36)    ) 
        , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1) 
HAVING    EmployeesDetailQ.LeftCompany=No

 

[Chintsapete]

I had to change your code slightly, the select Emp# bit. I got a complaint about could refer to more than 1 table.

Select EmployeesDetailQ.[Emp#]
     , int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1 as PeroidNumber
     , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36)    ) as PeriodStartDate
     , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1) as PeriodEndDate
     , sum([Salaries YTD].dayssick) as SumOfDaysSick
FROM       EmployeesDetailQ 
INNER JOIN [Salaries YTD]     ON EmployeesDetailQ.[Emp#] = [Salaries YTD].[Emp#]
GROUP BY  EmployeesDetailQ.[Emp#]
        , int(dateDiff("m",DateofEngagement, [Salaries YTD].[FullDateofSalaryPayment]) / 36) + 1
        , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36)    ) 
        , dateadd("Y", DateOfEngagement, int(dateDiff("m",DateofEngagement, [Salaries YTD].[Date]) / 36) + 1) 
HAVING    EmployeesDetailQ.LeftCompany=No

But even so or because of it? I get error message
"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)" Any ideas?
I'm sorry I'm taking up a huge amount of your time, but do appreciate it.

 

[CJ_London]

try this - you may need to change your table/field names to match your tables

SELECT Employees.EmpID, Employees.EmpName, Employees.DateofEngagement, DateAdd('yyyy',(DateDiff('yyyy',[dateofengagement],Date())\3)*3,[dateofengagement]) AS CycleStart, 30-Nz((SELECT sum(DaysSick) FROM [Salaries YTD] WHERE EmpID=Employees.EmpID AND PayRunDate >DateAdd('yyyy',(DateDiff('yyyy',[Employees].[dateofengagement],Date())\3)*3,[Employees].[dateofengagement]))) AS SickdaysLeft
FROM Employees

 

[Chintsapete]

Sorry I think I got it, looks like it was one of my spelling mistakes. Just have to double check the numbers. Will let you know. Thanks a lot that's amazing. I've been raking my pea brain for weeks about this one.

 

[namliam]

I had to change your code slightly, the select Emp# bit.
....

Any ideas?

Sorry about the Emp#, but the same goes for the second error... it is kindoff hard to debug things without the database.

Without said database you will probably have to tweak most any code you will get including SQL

 

[CJ_London]

just noticed about the left company field so use this code instead which incorporates it

SELECT Employees.EmpID, Employees.EmpName, Employees.DateofEngagement, DateAdd('yyyy',(DateDiff('yyyy',[dateofengagement],Date())\3)*3,[dateofengagement]) AS CycleStart, 30-Nz((SELECT sum(DaysSick) FROM [Salaries YTD] WHERE EmpID=Employees.EmpID AND PayRunDate >DateAdd('yyyy',(DateDiff('yyyy',[Employees].[dateofengagement],Date())\3)*3,[Employees].[dateofengagement]))) AS SickdaysLeft
FROM Employees
WHERE LeftCompany=No

 

[namliam]

CJ, the Inline sql you are suggesting is a huge drain on resources....assuming there is a few records in the database.