用于确定非出席是否为“DROPOUT”的SQL？ [英] SQL to determine if a non-attendance is a ?DROPOUT?
问题描述
我有一个每周出席的出勤表现在或者缺席
并且需要更改如下附带的理由：
如果一个人没有参加整个月，他被改为DROPOUT。他/她在那个月之前缺席了几个星期，直到他重新参加。
我尝试使用SQL来检查Streaks通过综合出勤检查几个月，但无法获得退出是正确的。任何帮助都将受到高度赞赏。
使用Sample SourceData，其中1为Absent &安培; 0表示现在 ，我使用了以下SQL但有一些错误
I have an attendance table with attendance per week either "Present" or "Absent"
and need to have it changed as follows with reasons attached:
If a person does not attend for an entire month he is changed to a "DROPOUT" for the weeks he/she was absent before that month until the time he re-attended.
I''ve tried using SQL that check for "Streaks" checking for months with Combined Attendances but cannot get the "Dropped out from" to be correct. Any help will be highly appreciated.
Using Sample SourceData having 1 as "Absent" & 0 as "Present" , I''ve used the following SQL but have some bugs
推荐答案
您能否更改样本数据并告诉我们哪个WeekNum该人将被视为DROP OUT？您的样本数据没有一个月全部缺席。这一切都至少有一周的时间。
~~ CK
Can you change your sample data and tell us which WeekNum the person will be considered a DROP OUT? Your sample data does not have a month that''s all absent. It all has at least 1 week present.
~~ CK
我不确定我理解你的具体要求。
您说：
I''m not sure I understand your exact requirements.
You said:
...整整一个月没有参加...
... does not attend for an entire month ...
这意味着如果某人在5月的最后3周缺席并且在6月的前3周缺席。然后他们不符合你的标准，没有一个标记辍学。
你说：
Which means that if someone is absent for the last 3 weeks of May and absent for the first 3 weeks of June. Then they don''t fall into your criteria and none of it gets marked dropout.
You said:
。 ..他被改为DROPOUT他/她在那个月之前缺席的几个星期...
... he is changed to a "DROPOUT" for the weeks he/she was absent before that month ...
这意味着如果有人在5月上半月缺席，在5月下半月出现6月份全部缺席，然后5月份的前半周显着辍学，但6月份的几周没有显示出来。这对我来说听起来不对，但这就是你所说的你想要的。
话虽如此，你的SQL中没有任何地方可以查看"条纹"实际上等于一个月。查询是否从表示周数和月/年的字符串切换到实际日期也会更好。
Which means if someone is absent for the first half of May, present for the last half of May, and absent for all of June, Then the first half of weeks in May are marked dropout but the weeks in June are marked absent. Which doesn''t sound right to me, but it''s what you are saying you want.
Having said that, no where in your SQL do you check to see that the "streak" is actually equal to a month. It would also be better for querying if you switch from strings representing week numbers and month / year to an actual date.
输出应该是
月份周状况
2013/01 2013周01 -1
2013/01 2013周02 -1
2013/01 2013第03周 - 1
2013/01 2013第04周0
2013/01 2013第05周1 -
2013/02 2013第06-1周/>
2013/02 2013第07周周期-1
2013/02 2013第08周周期-1
2013/02 2013周09 0
2013/03 2013第10周0
2013/03 2013第11周0
2013/03 2013第12周0
2013 / 03 2013年第13周0
2013/04 2013第14周-1
2013/04 2013第15周1
2013/04 2013周16 -1
2013/04 2013第17周0
2013/05 2013第18周1 -
2013/05 2013第19周-1
2013/05 2013第20周-1
2013/05 2013第21周0
2013/05 2013第22周第1期
2013/06 2013第2周3 -1
2013/06 2013第24周 -
2013/06 2013第25周0
2013/06 2013第26周0
2013/07 2013第27周第1期
2013/07 2013第28周1 -
2013/07 2013第29周
2013/07 2013第30周0
2013/08 2013第31周期间
2013/08 2013第32周-1
2013/08 2013第33周0
2013/08 2013第34周-1
2013/08 2013第35周 -
2013 / 09 2013第36周
2013/09 2013第37周 -
2013/09 2013第38周 -
2013/09 2013年第39周0
2013/10 2013周40 -1
2013/10 2013周41 -1
2013/10 2013第42周-1
2013/10 2013周43 0
2013/10 2013周44 -1
2013/11 2013第45周-1
2013/11 2013周46 -1
2013/11 2013第47周 -
2013/11 2013第48周0
2013/12 2013 Week 49 -1
2013/12 2013 Week 50 -1
2013/12 2013 Week 51 0
2013/12 2013第52周-2
2013/12 2013第53周-2
2014/01 2014 2014第2周
2014/01 2014第02周-2
2014/01 2014第03周-2
2014/01 2014 2014第2周 -
2014/01 2014第05周-2
2014/02 2014 2014第2周-2
2014/02 2014 2014第2周 -
2014/02 2014 2014第0周0 >
2014/02 2014 2014第2周-2
2014/03 2014第10周-2
2014/03 2014第11周-2
2014/03 2014第12周-2
2014/03 2014第13周-2
Output should be
Month Week Status
2013/01 2013 Week 01 -1
2013/01 2013 Week 02 -1
2013/01 2013 Week 03 -1
2013/01 2013 Week 04 0
2013/01 2013 Week 05 -1
2013/02 2013 Week 06 -1
2013/02 2013 Week 07 -1
2013/02 2013 Week 08 -1
2013/02 2013 Week 09 0
2013/03 2013 Week 10 0
2013/03 2013 Week 11 0
2013/03 2013 Week 12 0
2013/03 2013 Week 13 0
2013/04 2013 Week 14 -1
2013/04 2013 Week 15 -1
2013/04 2013 Week 16 -1
2013/04 2013 Week 17 0
2013/05 2013 Week 18 -1
2013/05 2013 Week 19 -1
2013/05 2013 Week 20 -1
2013/05 2013 Week 21 0
2013/05 2013 Week 22 -1
2013/06 2013 Week 23 -1
2013/06 2013 Week 24 -1
2013/06 2013 Week 25 0
2013/06 2013 Week 26 0
2013/07 2013 Week 27 -1
2013/07 2013 Week 28 -1
2013/07 2013 Week 29 -1
2013/07 2013 Week 30 0
2013/08 2013 Week 31 -1
2013/08 2013 Week 32 -1
2013/08 2013 Week 33 0
2013/08 2013 Week 34 -1
2013/08 2013 Week 35 -1
2013/09 2013 Week 36 -1
2013/09 2013 Week 37 -1
2013/09 2013 Week 38 -1
2013/09 2013 Week 39 0
2013/10 2013 Week 40 -1
2013/10 2013 Week 41 -1
2013/10 2013 Week 42 -1
2013/10 2013 Week 43 0
2013/10 2013 Week 44 -1
2013/11 2013 Week 45 -1
2013/11 2013 Week 46 -1
2013/11 2013 Week 47 -1
2013/11 2013 Week 48 0
2013/12 2013 Week 49 -1
2013/12 2013 Week 50 -1
2013/12 2013 Week 51 0
2013/12 2013 Week 52 -2
2013/12 2013 Week 53 -2
2014/01 2014 Week 01 -2
2014/01 2014 Week 02 -2
2014/01 2014 Week 03 -2
2014/01 2014 Week 04 -2
2014/01 2014 Week 05 -2
2014/02 2014 Week 06 -2
2014/02 2014 Week 07 -2
2014/02 2014 Week 08 0
2014/02 2014 Week 09 -2
2014/03 2014 Week 10 -2
2014/03 2014 Week 11 -2
2014/03 2014 Week 12 -2
2014/03 2014 Week 13 -2
这篇关于用于确定非出席是否为“DROPOUT”的SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
