How do I write sql query to get "days since"

Question

0.00/5 (No votes)

See more:

Hi, I am new to SQL but I think I'm nearly there with this one. I have an ms access database with a table called SicknessLog. The fields are ID, StaffName, [Start/Return], Date.

When a member of staff is off work for sickness then a record is added to the table and the value in the [Start/Return] field is 1. When they return to work a new record is added with the same details except the [Start/Return] field is 0.

I am trying to write a query that shows the number of days between when a member of staff went sick ([Start/Return] value of -1) and when they returned to work ([Start/Return] value of 0)

So far I have this

SELECT [staffname], DATEDIFF("d", min([date]), max([date])) as [Days Sick]
FROM sicknesslog
WHERE [date] > (now()-30)
GROUP BY [staffname]

This shows how many days off each staff member has had in the last 30 days. The only problem I have is that it is also listing 0 days for staff who are currently away. If possible I would like it to show the number of days since they went sick up intil now for staff who have not returned.

Thanks in advance for any help

Posted 14-Jun-14 0:44am

Member 10884049

Updated 14-Jun-14 1:19am

v3

Add a Solution

Comments

Peter Leow 14-Jun-14 7:55am

What if a staff went for 2 sick leaves in a month, say from 1 June 2014 to 3 June 2014, and then again from 11 June 2014 to 12 June 2014, your query will output the result of a total of 11 days, is that what you want?

Member 10884049 14-Jun-14 8:09am

Hi, thanks for reply. Yes I would want it to count total days off for all instances of sickness within that month. I just noticed another problem with my query. It will not work for staff who returned from sickness in last month but left earlier than that. Trying to figure out how to re-write it. I've had no sql training so learning as I go but it's very tricky

1 solution

Add a Solution

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)

Estys · Answer 1 · 2014-06-14T01:40:00

Solution 1

Substtute now() for max([date] when min and max are equal :

IIF(min([date])=max([date]),now(),max([date]))

Cheers

Posted 14-Jun-14 1:40am

Estys

Comments

Member 10884049 14-Jun-14 8:14am

Hi Estys. Thanks for reply. I tried your code as below

SELECT [staffname], DATEDIFF("d",min([date]),max([date])) AS [Days Sick]
FROM sicknesslog
WHERE [date]>(IIF(min([date])=max([date]),now(),max([date]))-30)
GROUP BY [staffname];

But I am getting error message "Cannot have aggregate function in Where clause"?

Member 10884049 14-Jun-14 8:27am

Oh wait, I think you meant like this? SELECT [staffname], DATEDIFF("d",min([date]),IIF(min([date])=max([date]),now(),max([date]))) AS [Days Sick]
FROM sicknesslog
WHERE [date]>(now()-30)
GROUP BY [staffname];

Member 10884049 14-Jun-14 8:29am

Now I just need to account for instances where staff returned from sickness within last 30 days but had left before then. (they will have one entry but this needs to be ignored)

Estys 14-Jun-14 9:53am

You also need to consider the situation that someone has been absent in more than one period the last 30 days.