从sql server获取最近4天未完成的数据 [英] get last 4 days not completed data from sql server
问题描述
大家好，
任何人都可以帮我这个 -
i想要用户数据谁超过4天完成工作表。
i存储用户名在一个表中，工作日期在另一个表上
table-
1）educator_master - eid，first_name，last_name
2）educator_call_dtls - call_id，eid，call_date
so如果有5个用户并且他们每天填写信息。
i想要报告过去4天没有输入信息的用户。
[转自评论]
ok。
我提供虚拟数据： -
1）educator_master数据： -
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数据： -
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
所以，如果你看看第二张表中的eid = 25，他输入数据直到2013年11月11日，所以他的n ame shuld进入报告
选择 Distinct ed.eid，Max（call_date） as LastReportingDate，first_name，last_name
来自 educator_call_dtls ed
left join educator_master em on em.eid = ed.eid
group by ed.eid，first_name，last_name
Max（call_date）+4< getdate（）
快乐编码！
：）
试试这个：
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
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解决方案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!
:)
Try this:
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
这篇关于从sql server获取最近4天未完成的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
