SQL Statistics Problems

gaofei1114

--1\statistics of all employees in 2016, each month, overtime every year. The requirement is to output the total employee gh of each month's jbgs greater than 36, and the monthly output.

select gh, convert (char (7), rq, 111) YF, SUM (zbs) zbs, sum (jbgs) jbgs
--into GZB_MONTH
FROM GZMXB WHERE YEAR (rq) = '2016'
group by gh, convert (char (7), rq, 111)
having sum (jbgs)> 36

--2\outputs the gh output of employees whose total jbgs exceeded (36 * 12) 432 in 2016.
select gh, YEAR (rq) NF, SUM (zbs) zbs, sum (jbgs) jbgs
--into GZB_YEAR
FROM GZMXB WHERE YEAR (rq) = '2016'
group by gh, YEAR (rq)
having sum (jbgs)> 432

--3\compares the result of 2 with zgs.jbs in another table (gzb). The GH of different employees and the month of the current month are output.

SELECT A.gh, B.gh, A.YF, B.YF, A.zbs, B.zbs, A.jbgs, B.jbgs
FROM GZB_MONTH A FULL JOIN gzb B ON A.gh = B.gh and A.YF = B.YF
WHERE A.zbs <> B.zbs OR A.jbgs <> B.jbgs





The first two have been done. The third one seems to be a problem. Help me again. Thank you
Two tables: gzgl_gzb kqgl_Gs
There are two columns in the kqgl_gs table: zbgs jbgs records the working hours and overtime working hours of each person for each month per day.
The zbs jbs in the gzgl_gzb table are for all employees who work and work overtime in the month. That is, zbs = the sum of zbgs per day. jbs = the sum of jbgs per day. The two columns in the second table are like this Calculated. But some data was manually changed in the middle. So what I want to do now is to change the result. That is, the output is changed. It also needs the month. The work number. The data before the change. The data after the change .

sinexe

select _s. employee, convert (char (4), _s.date, 112), sum (_s.zbgs), sum (_s.jbgs)
      , Min (_b.zbs), min (_b.jbs)
from kqgl_gs _s
join gzgl_gzb _b
     on convert (char (4), _ s.date, 112) = _b.year month
     and _s. employee = _b. employee
group by employee, convert (char (4), date, 112)

sinexe

The above is wrong
select _s. employee, convert (char (6), _s.date, 112), sum (_s.zbgs), sum (_s.jbgs)
      , Min (_b.zbs), min (_b.jbs)
from kqgl_gs _s
join gzgl_gzb _b
     on convert (char (6), _ s.date, 112) = _b.year month
     and _s. employee = _b. employee
group by employee, convert (char (6), date, 112)

sinexe

The premise is that the gzgl_gzb table has a column of ‘year and month’ in the format of ‘201605’