注册 登录
编程论坛 → SQL Server论坛

求助，谁能帮我改下这个程序，得出周六上班时间，拜托啦！

哭哭哭 发布于 2012-09-04 10:45， 734 次点击
拜托了！这个是以前的工资计算，其中有时间，我想提取周六时间怎么办呀。 具体看附件的文件呀 。。加我Q好不 258471811  拜托啦· 真心求助~~~拜托大侠们了
程序代码：
SELECT serialnumber, (CASE WHEN
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.workhourid <> 'rest' AND ats_ioimport.workhourid <> '' AND
               ats_ioimport.flag = '1' AND datepart(dw, ats_ioimport.att_date) > 1 AND
               datepart(dw, ats_ioimport.att_date) < 7 AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) IS NOT NULL
      THEN
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.workhourid <> 'rest' AND ats_ioimport.workhourid <> '' AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) ELSE 0 END) AS ychuqin,
          ((SELECT COUNT(*)
          FROM ats_ioimport, control
          WHERE ats_ioimport.serialnumber = employee1.serialnumber AND
                ats_ioimport.worktime < 6 AND ats_ioimport.worktime >= 3 AND
                ats_ioimport.flag = '1' AND
                ats_ioimport.att_date >= control.pay_curperiodbegin AND
                ats_ioimport.att_date <= control.pay_curperiodend AND workhourid <> 'rest')
      * 0.5 +
          (SELECT COUNT(*)
         FROM ats_ioimport, control
         WHERE ats_ioimport.serialnumber = employee1.serialnumber AND
               ats_ioimport.worktime >= 6 AND flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND workhourid <> 'rest'))
      AS act_days,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.latearrive <> 0 AND ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS latetime,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.latearrive > 0 AND ats_ioimport.latearrive <= 30 AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS latetime30,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.latearrive > 30 AND ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS latetime31,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.earlyleave <> 0 AND ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS earlytime,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.earlyleave > 0 AND ats_ioimport.earlyleave <= 30 AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS earlytime30,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.earlyleave > 30 AND ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) AS earlytime31,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.flag = 1 AND ats_ioimport.workhourid <> 'tsbc' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND
               ats_ <> '1900-1-1' AND ats_ioimport.latearrive = 0 AND
               ats_ioimport.earlyleave = 0 AND DATEPART(hh, ats_) < 17)
      AS shichu,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND datediff(mi,
               ats_, ats_) > 0 AND datediff(mi, ats_,
               ats_) <= 30 AND ats_ioimport.flag = 1 AND
               workhourid <> 'tsbc' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND
               ats_ <> '1900-1-1' AND ats_ioimport.latearrive = 0 AND
               ats_ioimport.earlyleave = 0 AND DATEPART(hh, ats_) < 17)
      AS shichu30,
          (SELECT COUNT(ats_ioimport.att_date)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND datediff(mi,
               ats_, ats_) > 30 AND ats_ioimport.flag = 1 AND
               workhourid <> 'tsbc' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND
               ats_ <> '1900-1-1' AND ats_ioimport.latearrive = 0 AND
               ats_ioimport.earlyleave = 0 AND DATEPART(hh, ats_) < 17)
      AS shichu31, (CASE WHEN
          (SELECT SUM(ats_ioimport.latearrive + ats_ioimport.earlyleave)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) IS NOT NULL
      THEN
          (SELECT SUM(ats_ioimport.latearrive + ats_ioimport.earlyleave)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) ELSE 0 END) AS qktime,
      (CASE WHEN
          (SELECT SUM(datediff(mi, ats_, ats_))
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               (ats_ioimport.flag = '1') AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND
               (ats_ioimport.latearrive = 0) AND (ats_ioimport.earlyleave = 0) AND
               (ats_ <> '1900-1-1') AND (DATEPART(hh, ats_) < 17))
      IS NOT NULL THEN
          (SELECT SUM(datediff(mi, ats_, ats_))
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               (ats_ioimport.flag = '1') AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend AND
               (ats_ioimport.latearrive = 0) AND (ats_ioimport.earlyleave = 0) AND
               (ats_ <> '1900-1-1') AND (DATEPART(hh, ats_) < 17))
      ELSE 0 END) AS shichutime,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '02' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS shijia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '03' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS binjia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '04' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS bujia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '05' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS hunjia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '06' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS bubinjia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '07' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend)
      AS gongshang,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '08' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS taiqin,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '09' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend)
      AS kuanggong,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype = '10' AND ats_empleavesheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empleavesheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empleavesheet.endtime, 11) <= control.pay_curperiodend) AS shangjia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype IN ('03', '06') AND
               ats_empleavesheet.flag = 1 AND CONVERT(nvarchar(10),
               ats_empleavesheet.begintime, 11) >= control.pay_curperiodbegin AND
               CONVERT(nvarchar(10), ats_empleavesheet.endtime, 11)
               <= control.pay_curperiodend) AS zongbinjia,
          (SELECT SUM(ats_empleavesheet.leavetime)
         FROM ats_empleavesheet, control
         WHERE employee1.serialnumber = ats_empleavesheet.serialnumber AND
               ats_empleavesheet.leavetype IN ('05', '07', '08', '10') AND
               ats_empleavesheet.flag = 1 AND CONVERT(nvarchar(10),
               ats_empleavesheet.begintime, 11) >= control.pay_curperiodbegin AND
               CONVERT(nvarchar(10), ats_empleavesheet.endtime, 11)
               <= control.pay_curperiodend) AS qitajia, (CASE WHEN
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '01' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) IS NULL
      THEN 0 ELSE
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '01' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) END)
      AS pingshijiaban, ((CASE WHEN
          (SELECT COUNT(*)
         FROM ats_ioimport, control
         WHERE ats_ioimport.serialnumber = employee1.serialnumber AND (datepart(dw,
               att_date) = 7 OR
               datepart(dw, att_date) = 1) AND workhourid <> 'rest' AND
               ats_ioimport.worktime < 6 AND ats_ioimport.worktime >= 3 AND
               ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) IS NULL
      THEN 0 ELSE round
          ((SELECT COUNT(*)
          FROM ats_ioimport, control
          WHERE ats_ioimport.serialnumber = employee1.serialnumber AND (datepart(dw,
                att_date) = 7 OR
                datepart(dw, att_date) = 1) AND workhourid <> 'rest' AND
                ats_ioimport.worktime < 6 AND ats_ioimport.worktime >= 3 AND
                ats_ioimport.flag = '1' AND
                ats_ioimport.att_date >= control.pay_curperiodbegin AND
                ats_ioimport.att_date <= control.pay_curperiodend), 2) END)
      * 0.5 + (CASE WHEN
          (SELECT COUNT(*)
         FROM ats_ioimport, control
         WHERE ats_ioimport.serialnumber = employee1.serialnumber AND (datepart(dw,
               att_date) = 7 OR
               datepart(dw, att_date) = 1) AND workhourid <> 'rest' AND
               ats_ioimport.worktime >= 6 AND ats_ioimport.flag = '1' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend) IS NULL
      THEN 0 ELSE round
          ((SELECT COUNT(*)
          FROM ats_ioimport, control
          WHERE ats_ioimport.serialnumber = employee1.serialnumber AND (datepart(dw,
                att_date) = 7 OR
                datepart(dw, att_date) = 1) AND workhourid <> 'rest' AND
                ats_ioimport.worktime >= 6 AND ats_ioimport.flag = '1' AND
                ats_ioimport.att_date >= control.pay_curperiodbegin AND
                ats_ioimport.att_date <= control.pay_curperiodend), 2) END)
      + (CASE WHEN
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '02' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) IS NULL
      THEN 0 ELSE
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '02' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) END))
      AS zhoumojiaban, (CASE WHEN
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '03' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) IS NULL
      THEN 0 ELSE
          (SELECT round(SUM(ats_empotsheet.ottime) / 8, 2)
         FROM ats_empotsheet, control
         WHERE employee1.serialnumber = ats_empotsheet.serialnumber AND
               ats_empotsheet.ottype = '03' AND ats_empotsheet.flag = 1 AND
               CONVERT(nvarchar(10), ats_empotsheet.begintime, 11)
               >= control.pay_curperiodbegin AND CONVERT(nvarchar(10),
               ats_empotsheet.endtime, 11) <= control.pay_curperiodend) END)
      AS jiejiajiaban, round
          ((SELECT SUM(ats_ioimport.worktime + ats_ioimport.otoffdutycal)
          FROM ats_ioimport, control
          WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
                ats_ioimport.flag = '1' AND workhourid <> 'rest' AND
                ats_ioimport.att_date >= control.pay_curperiodbegin AND
                ats_ioimport.att_date <= control.pay_curperiodend) +
          (SELECT SUM(ats_ioimport.worktime)
         FROM ats_ioimport, control
         WHERE employee1.serialnumber = ats_ioimport.serialnumber AND
               ats_ioimport.flag = '1' AND workhourid = 'rest' AND
               ats_ioimport.att_date >= control.pay_curperiodbegin AND
               ats_ioimport.att_date <= control.pay_curperiodend), 0) AS Act_time
FROM employee1
WHERE serialnumber IN
          (SELECT serialnumber
         FROM [all active employees])






5 回复
#2
zklhp2012-09-04 10:50
这是SQL？
#3
哭哭哭2012-09-04 10:55
回复 2楼 zklhp
对的· 求助呀·
#4
netlin2012-09-05 08:47
路过，现在没空，中午休息时再看看！
#5
哭哭哭2012-09-05 11:45
回复 4楼 netlin
谢谢啦~~~
#6
netlin2012-09-06 08:37
楼主，初略看了一下，这个查询涉及以下4个表：
employee1、ats_ioimport、control、ats_empleavesheet
为了方便分析这个查询，请楼主提供一些相关信息：
    1.以上4个表的大概结构，如果对关键字段有说明最好了。
    2.以上这个工资查询的结果，对结果字段做个说明。

还有一点，也是最重要的一点：
   你问题中说：“得出周六上班时间”，是什么意思，能说得具体点吗？
1
©2025, BC-CN.NET
电脑版