The select DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) as the MONTH, sname, t.t ime, tc hecktime, sum, t.i downer, tc mount the from (select
Case the when DATEDIFF (MINUTE, '08:00:00, CONVERT (varchar (100), checktime, 108)) & lt; 0 then 0
The when DATEDIFF (MINUTE, '08:00:00, CONVERT (varchar (100), checktime, 108)) & gt; 0 and checktime between (select JBQSSJ From hga_jbtjb_v where XWSP='2 c9cd8196ddc4923016dfb4148b41192'
And DJZT='2 c9cd87d6ca784ca016cb2ecdb000371 and
DATENAME (year, JBQSSJ) + '-' + DATENAME (MONTH, JBQSSJ) + '-' +
RIGHT (' 00 '+ CAST (day (JBQSSJ) AS VARCHAR (2)), 2)=
DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) and idowner=(select id from hga_employee_v where sname=t.s name
)) and (select JBJZSJ From hga_jbtjb_v where XWSP='2 c9cd8196ddc4923016dfb4148b41192'
And DJZT='2 c9cd87d6ca784ca016cb2ecdb000371 and
DATENAME (year, JBJZSJ) + '-' + DATENAME (MONTH, JBJZSJ) + '-' +
RIGHT (' 00 '+ CAST (day (JBJZSJ) AS VARCHAR (2)), 2)=
DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) and idowner=(select id from hga_employee_v where sname=t.s name)
) then 0
The else DATEDIFF (MINUTE, '08:00:00, CONVERT (varchar (100), checktime, 108)) end sum,
Case the when DATEDIFF (MINUTE, '08:00:00, CONVERT (varchar (100), checktime, 108)) & lt; 0 then 0
The when DATEDIFF (MINUTE, '08:00:00, CONVERT (varchar (100), checktime, 108)) & gt; 0 and checktime between (select JBQSSJ From hga_jbtjb_v where XWSP='2 c9cd8196ddc4923016dfb4148b41192'
And DJZT='2 c9cd87d6ca784ca016cb2ecdb000371 and
DATENAME (year, JBQSSJ) + '-' + DATENAME (MONTH, JBQSSJ) + '-' +
RIGHT (' 00 '+ CAST (day (JBQSSJ) AS VARCHAR (2)), 2)=
DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) and idowner=(select id from hga_employee_v where sname=t.s name
)) and (select JBJZSJ From hga_jbtjb_v where XWSP='2 c9cd8196ddc4923016dfb4148b41192'
And DJZT='2 c9cd87d6ca784ca016cb2ecdb000371 and
DATENAME (year, JBJZSJ) + '-' + DATENAME (MONTH, JBJZSJ) + '-' +
RIGHT (' 00 '+ CAST (day (JBJZSJ) AS VARCHAR (2)), 2)=
DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) and idowner=(select id from hga_employee_v where sname=t.s name)
) then 0
The else 1 end count
, sname, checktime, id, idowner DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) AS the time From hga_zkkq t where DATENAME (year, checktime) +' - '+ DATENAME (MONTH, checktime) +' - '+
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2) in (
Select the convert (varchar (10), dateadd (DAY, t2. Number, t1. DAY), 120) DAY from
(select substring (convert (varchar, GETDATE (), 120), 1, 7) + '- 01' day), t1,
(select number from the MASTER.. Spt_values WHERE TYPE='P' AND number>=0 and numberWhere the convert (varchar (10), dateadd (DAY, t2. Number, t1. DAY), 120) like the substring (convert (varchar, GETDATE (), 120), 1, 7) + '%')) t
Where id=(select top (1) id from hga_zkkq where sname=t.s name and DATENAME (year, checktime) + '-' + DATENAME (MONTH, checktime) + '-' +
RIGHT (' 00 '+ CAST (day (checktime) AS VARCHAR (2)), 2)=t.t ime order by checktime)
And t.i downer!
='null'

CodePudding user response:

Suggest amend the subquery to temporary table,

CodePudding user response:

You so it is better to stick a few tables, say your demand, may be someone to help you write a concise SQL