看起来很多余。我所做的是为特定的员工和他们所在的部门分配任务,并根据datediff和follow up date相等的情况分配他们在两周内的任务,赋值为1(为真),最后将总数相加。
$_2weeks = "select
isnull(b.employee, 'Event Total') as Employee,
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))<='-2' then 1 else 0 end) '2+ Days Behind',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='-1' then 1 else 0 end) '1 Day Behind',
sum(case when cast(a.follow_up as date)=cast(GETDATE() as date) then 1 else 0 end) 'Today<br> " . date('m/d') . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='1' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+1 day')), 0, 1).'<br>' . date('m/d', strtotime('+1 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='2' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+2 day')), 0, 2).'<br>' . date('m/d', strtotime('+2 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='3' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+3 day')), 0, 1).'<br>' . date('m/d', strtotime('+3 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='4' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+6 day')), 0, 1).'<br>' . date('m/d', strtotime('+6 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='5' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+7 day')), 0, 1).'<br>' . date('m/d', strtotime('+7 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='6' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+8 day')), 0, 1).'<br>' . date('m/d', strtotime('+8 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='7' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+9 day')), 0, 2).'<br>' . date('m/d', strtotime('+9 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='8' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+10 day')), 0, 1).'<br>' . date('m/d', strtotime('+10 day')) . "',
sum(case when DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date))='9' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+13 day')), 0, 1).'<br>' . date('m/d', strtotime('+13 day')) . "',
sum(1) AS 'Total'
from " . event_table('event') . " a
left outer join " . event_table('employee') . " b on a.employee_id=b.id
where a.task_id like '%$task_id%'
and b.department_id like '$dept_id'
and a.status=1
and a.task_id<>''
group by b.employee with rollup";
以及一个更简洁的方法为substr和strtotime php函数?任何帮助都将非常感激。
您可以扩展group by
以包含日差。例如:
select b.employee
, datediff(dd,cast(getdate() as date),cast(a.follow_up as date)) as DaysInPast
, count(a.task_id) as TaskCount
group by
b.employee
, datediff(dd,cast(getdate() as date),cast(a.follow_up as date))
必须在客户端生成列名,并可能对结果进行透视。最后,这可能比您当前的解决方案更复杂(就代码行数而言)。
。如果您只是需要避免这种重复的"视觉问题",您可以创建一个php函数来输出正确的字符串,如…
function sqlline($i, $n) {
$timestr = strtotime('+'. $i .' day');
$d = date('l/m/d', $timestr);
$d2 = date('m/d', $timestr);
return 'SUM '.
'(CASE WHEN '.
'DATEDIFF (dd, cast(GETDATE() as date), cast(a.follow_up as date)) = "'. $i .'" '.
'THEN 1 ELSE 0 END) '.
'"'. substr($d, 0, $n) .'<br>'. $d2 .
', '
}
。用sqlline(1, 1) . sqlline(2, 1) (...)
表示
。但是我认为你应该更多地考虑一下代码的目的以及如何实现这些自动操作
不一定是您要求的(简化PHP部分),但我认为如果您的数据库支持像这样的子表,您可以减少SQL代码的"视觉混乱":
select
isnull(b.employee, 'Event Total') as Employee,
sum(case when sub.dat_diff<='-2' then 1 else 0 end) '2+ Days Behind',
sum(case when sub.dat_diff='-1' then 1 else 0 end) '1 Day Behind',
sum(case when cast(a.follow_up as date)=cast(GETDATE() as date) then 1 else 0 end) 'Today<br> " . date('m/d') . "',
sum(case when sub.dat_diff='1' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+1 day')), 0, 1).'<br>' . date('m/d', strtotime('+1 day')) . "',
sum(case when sub.dat_diff='2' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+2 day')), 0, 2).'<br>' . date('m/d', strtotime('+2 day')) . "',
sum(case when sub.dat_diff='3' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+3 day')), 0, 1).'<br>' . date('m/d', strtotime('+3 day')) . "',
sum(case when sub.dat_diff='4' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+6 day')), 0, 1).'<br>' . date('m/d', strtotime('+6 day')) . "',
sum(case when sub.dat_diff='5' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+7 day')), 0, 1).'<br>' . date('m/d', strtotime('+7 day')) . "',
sum(case when sub.dat_diff='6' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+8 day')), 0, 1).'<br>' . date('m/d', strtotime('+8 day')) . "',
sum(case when sub.dat_diff='7' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+9 day')), 0, 2).'<br>' . date('m/d', strtotime('+9 day')) . "',
sum(case when sub.dat_diff='8' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+10 day')), 0, 1).'<br>' . date('m/d', strtotime('+10 day')) . "',
sum(case when sub.dat_diff='9' then 1 else 0 end) '" . substr(date('l/m/d', strtotime('+13 day')), 0, 1).'<br>' . date('m/d', strtotime('+13 day')) . "',
sum(1) AS 'Total'
FROM (
SELECT a.employee_id, a.task_id, DATEDIFF(dd,cast(GETDATE() as date),cast(a.follow_up as date)) as dat_diff
FROM " . event_table('event') . " a
WHERE a.status=1
) sub
LEFT JOIN ... (etc.)
同样,您可能应该避免多次调用"getdate"——最可能的是调用一次,将值存储在变量中并将其粘贴到代码中就足够了,并且会稍微快一些。