| |

VerySource

 Forgot password?
 Register
Search
View: 535|Reply: 3

SQL Statistics Problems

[Copy link]

2

Threads

5

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 China

Post time: 2020-3-6 01:00:01
| Show all posts |Read mode
--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 .
Reply

Use magic Report

0

Threads

35

Posts

22.00

Credits

Newbie

Rank: 1

Credits
22.00

 China

Post time: 2020-5-23 13:30:01
| Show all posts
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)
Reply

Use magic Report

0

Threads

35

Posts

22.00

Credits

Newbie

Rank: 1

Credits
22.00

 China

Post time: 2020-5-23 16:00:01
| Show all posts
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)
Reply

Use magic Report

0

Threads

35

Posts

22.00

Credits

Newbie

Rank: 1

Credits
22.00

 China

Post time: 2020-5-23 18:30:01
| Show all posts
The premise is that the gzgl_gzb table has a column of ‘year and month’ in the format of ‘201605’
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list