get last 4 days not completed data from sql server

Question

1.00/5 (1 vote)

See more:

Hi all,

can anyone help me on this-

i want users data who don't complete their work sheet more than 4 days.
i store username in one table and work date on other table

table-
1)educator_master - eid,first_name,last_name
2)educator_call_dtls - call_id,eid,call_date

so if there are 5 users and they fill up information everyday.
i want report of users who dont enter information for last 4 days.

[moved from comment]

ok.

I am providing dummy data:-

1)educator_master data:-

eid     first_name   last_name
19	Tapan Kumar  Chhanda	
20	SK Mumtaz Ali	
21	Sanjoy Poddar	
22	Md.Mokibul Hoque	
23	Md.Atiqul   Ali	
24	Ganesh Chakraborty	
25	Dinesh Chakraborty

2)educator_call_dtls data:-

call_id	eid	call_date
108637	19	03/25/13
108639	19	03/26/13
108641	19	03/27/13
108642	19	03/28/13
109649	19	03/29/13
109650	19	03/30/13
109653	19	03/31/13
110389	19	04/01/13
110390	19	04/02/13
109663	20	03/28/13
109664	20	03/29/13
109665	20	03/30/13
109666	20	03/31/13
110079	20	04/01/13
110387	20	04/02/13
109901	22	03/27/13
109902	22	03/28/13
109907	22	03/29/13
109928	22	03/30/13
109941	22	03/31/13
107998	23	03/27/13
109124	23	03/28/13
110086	23	03/29/13
110087	23	03/30/13
110088	23	03/31/13
108646	24	03/27/13
108647	24	03/28/13
110028	24	03/29/13
110030	24	03/30/13
110032	24	03/31/13
103173	25	03/07/13
104101	25	03/08/13
104102	25	03/09/13
104103	25	03/10/13
104701	25	03/11/13

so if you look at eid =25 in 2nd table, he entered data till 11 amrch 2013 so his name shuld come in report

Posted 3-Apr-13 0:16am

Sweetynewb

Updated 3-Apr-13 0:53am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 3-Apr-13 6:21am

We need more informations, for example: table structure, relationships between tables, example data.

Aarti Meswania 3-Apr-13 6:23am

provide details of tables

Ankur^\m/ 3-Apr-13 6:24am

So write the query. You will have to join the tables.
Where is the problem?

Maciej Los 3-Apr-13 6:34am

Sweetynewb, do you think is it enough information to help you find solution? Read my past comment.

Sweetynewb 3-Apr-13 6:47am

ok.

I am providing dummy data:-

1)educator_master data:-

eid first_name last_name
19 Tapan Kumar Chhanda
20 SK Mumtaz Ali
21 Sanjoy Poddar
22 Md.Mokibul Hoque
23 Md.Atiqul Ali
24 Ganesh Chakraborty
25 Dinesh Chakraborty

2)educator_call_dtls data:-
call_id eid call_date
108637 19 03/25/13
108639 19 03/26/13
108641 19 03/27/13
108642 19 03/28/13
109649 19 03/29/13
109650 19 03/30/13
109653 19 03/31/13
110389 19 04/01/13
110390 19 04/02/13
109663 20 03/28/13
109664 20 03/29/13
109665 20 03/30/13
109666 20 03/31/13
110079 20 04/01/13
110387 20 04/02/13
109901 22 03/27/13
109902 22 03/28/13
109907 22 03/29/13
109928 22 03/30/13
109941 22 03/31/13
107998 23 03/27/13
109124 23 03/28/13
110086 23 03/29/13
110087 23 03/30/13
110088 23 03/31/13
108646 24 03/27/13
108647 24 03/28/13
110028 24 03/29/13
110030 24 03/30/13
110032 24 03/31/13
103173 25 03/07/13
104101 25 03/08/13
104102 25 03/09/13
104103 25 03/10/13
104701 25 03/11/13

so if you look at eid =25 in 2nd table, he entered data till 11 amrch 2013 so his name shuld come in report

2 solutions

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)

Aarti Meswania · Accepted Answer · 2013-04-03T00:59:00

Solution 1

SQL

select Distinct ed.eid, Max(call_date) as LastReportingDate ,first_name,last_name 
from educator_call_dtls ed
left join educator_master em on em.eid = ed.eid
group by ed.eid ,first_name,last_name 
Having Max(call_date)+4 < getdate()

Happy Coding!
:)

Posted 3-Apr-13 0:59am

Aarti Meswania

Updated 3-Apr-13 1:43am

v6

Comments

Sweetynewb 3-Apr-13 7:03am

it gets duplicate rows..

Aarti Meswania 3-Apr-13 7:04am

use "distinct" see updated solution

Maciej Los 3-Apr-13 7:23am

Aarti, in my opinion, your answer is wrong, because your query will return all records where call_date is less than 4 days ago (eid: 19, 20, 22, 23, 24), but it should returns only those eid which does not have any records in last 4 days. Do you know, what i mean?

Aarti Meswania 3-Apr-13 7:26am

oh yes thanks
I have edit it :)

Sweetynewb 3-Apr-13 7:17am

using distinct it gives unique values, but iwant to display call_date column too.. i.e. last call date of user with name and id

Aarti Meswania 3-Apr-13 7:19am

check solution again

Sweetynewb 3-Apr-13 7:32am

now i am able to get call_date but it is giving all users.
i mean for eg: user -2 has filled data upto 2/4/2013
still it shows in upper query result

Aarti Meswania 3-Apr-13 7:37am

this query is returning perfect result you looking for...
select Distinct ed.eid, Max(call_date) as LastReportingDate ,first_name,last_name
from educator_call_dtls ed
left join educator_master em on em.eid = ed.eid
group by ed.eid ,first_name,last_name
Having Max(call_date)+4 > getdate()

Aarti Meswania 3-Apr-13 7:38am

what is data type of call_date column?

Sweetynewb 3-Apr-13 7:42am

Its datetime. i just changed ">" sign to "<" and it worked.
Thank you very much.

Aarti Meswania 3-Apr-13 7:44am

Welcome!
Glad to help you! :)

Maciej Los 3-Apr-13 7:47am

It would be good to mark all valuable solutions as "solved" (green button). Please, do not forget to vote.

Maciej Los 3-Apr-13 7:45am

Now, it seems to be worked OK.
+5!

Aarti Meswania 3-Apr-13 7:46am

Thank you! :)

[no name] 3-Apr-13 7:50am

+5 aarti..as always.. :)

Aarti Meswania 3-Apr-13 7:51am

Thank you! :)

Maciej Los · Accepted Answer · 2013-04-03T01:39:00

Solution 2

Try this:

SQL

SELECT DISTINCT em.eid, em.first_name, em.last_name, ed.MaxOfCallDate AS LastModifDate
FROM educator_master AS em RIGHT JOIN (
              SELECT eid, MAX(call_date) AS MaxOfCallDate
              FROM educator_call_dtls 
              GROUP BY eid
              HAVING MAX(call_date)<getdate()-4) AS ed ON ed.eid = em.eid

Posted 3-Apr-13 1:39am

Maciej Los

Updated 3-Apr-13 1:42am

v2

Comments

Aarti Meswania 3-Apr-13 7:47am

5+

Maciej Los 3-Apr-13 7:48am

Thank you ;)

Sweetynewb 3-Apr-13 7:50am

Even this query is right.
Thank you for help :)

Maciej Los 3-Apr-13 7:59am

You're welcome ;)
Please, mark this answer as "solved" (formally).