Shulou(Shulou.com)05/31 Report--

Editor to share with you the use of SQL case study, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

Demand

The score of the results summarized by all departments. Most indicators are graded based on departmental aggregate (SUM or AVG) results.

However, the interview rate and the average number of overtime hours in the top 10% are not scored based on the aggregate (SUM or AVG) results of all departments (TEAM). It needs to be based on each department's

Score the results after the data are sorted or accumulated.

Interview rate calculation method: TEAM1 mid-quarter January, the target number of interviews is 33%-"Target number of interviewers = number of departments * 33%

Actual number of interviewees = 10

In the middle quarter of TEAM1 in February, the target number of interviews is 60%-"Target number of interviewers = number of departments * 60%

Actual number of interviewees = 2 + 10 (actual number of interviewers in January)

TEAM1 mid-quarter March, the target number of interviewers is 100%-"Target number of interviewers = number of departments * 100%

Actual number of interviewees = 1 + 12 (actual number of interviewers in February)

The number of departments here is the total number of departments on a quarterly basis (excluding leaders, the number of departments has been determined in the previous stored procedure, CVS_DEPT_PAX_CNT said), because the number of departments or departmental relationships may change from quarter to quarter.

Calculate the actual monthly interview rate = actual number of interviewees / CAST (target number of interviewers AS NUMERIC (19pd2)) * 100

Note that this is the conversion of integers to decimals to ensure that there is no need to divide. The requirement is a decimal

The specific SQL is as follows

/ * Statistics of each group * /

DECLARE @ CVS_SING_STAT AS TABLE

(

TEAM_CD VARCHAR (20)

, GRP_CD VARCHAR (20)

, CHECK_RADIO numeric (19pr 2)

)

INSERT INTO @ CVS_SING_STAT

SELECT T.TEAM_CD,T.DEPT_CD GRP_CD,T.CHECK_RADIO

FROM / * the following inner query is used to do the accumulation of each row on SQL SERVER 2008 * /

SELECT SUBSTRING (BASE.DHSTC_CD,0,5) DH_YEAR, BASE.STC_MONTH, SUBSTRING (BASE.DHSTC_CD,5,8) DH_MONTH

, BASE.TEAM_CD,BASE.DEPT_CD,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT

, SUM (ROLL.H09_CNT) ACC

, SUM (ROLL.H09_CNT) / BASE.CVS_DEPT_PAX_CNT CVS_RAT

--, 33.0 Universe 100.0

--, CAST (33.0 AS NUMERIC 100.0)

--, SUM (ROLL.H09_CNT) / (BASE.CVS_DEPT_PAX_CNT* (33.0 Universe 100.0)) is the actual number of interviewees / the target number of interviewers

--, CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_DEPT_PAX_CNT* (33.0 to 100.0)) AS NUMERIC (19Magne2)) FLOATTEST

, CASE WHEN BASE.STC_MONTH = '01' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_DEPT_PAX_CNT* (33.0 Universe 100.0)) AS NUMERIC (19pen2))

WHEN BASE.STC_MONTH = '02' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_DEPT_PAX_CNT* (66.0 / 100.0)) AS NUMERIC (19pen2))

WHEN BASE.STC_MONTH = '03' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_DEPT_PAX_CNT* (100.0plus 100.0)) AS NUMERIC (19mem2))

END CHECK_RADIO

FROM T_DM_DHSTC_DEPT BASE

, T_DM_DHSTC_DEPT ROLL

WHERE SUBSTRING (BASE.DHSTC_CD,0,5) = SUBSTRING (ROLL.DHSTC_CD,0,5)-YEAR

AND BASE.STC_QUATR = ROLL.STC_QUATR

AND BASE.STC_MONTH > = ROLL.STC_MONTH

AND BASE.TEAM_CD = ROLL.TEAM_CD

AND BASE.DEPT_CD = ROLL.DEPT_CD

-- AND BASE.DEPT_CD = 'EA190086'

GROUP BY SUBSTRING (BASE.DHSTC_CD,0,5), BASE.TEAM_CD,BASE.DEPT_CD,SUBSTRING (BASE.DHSTC_CD,5,8), BASE.STC_MONTH,BASE.H09_CNT,BASE.CVS_DEPT_PAX_CNT

) T

WHERE T.DH_YEAR = @ YEAR AND T.DH_MONTH = @ MONTH

/ * Statistics of each TEAM * /

DECLARE @ CVS_TEAM_STAT AS TABLE

(

TEAM_CD VARCHAR (20)

, GRP_CD VARCHAR (20)

, CHECK_RADIO numeric (19pr 2)

)

DECLARE @ CVS_TEAM_BASE AS TABLE

(

DH_YEAR VARCHAR (20)

, STC_QUATR VARCHAR (20)

, STC_MONTH VARCHAR (20)

, DH_MONTH VARCHAR (20)

, TEAM_CD VARCHAR (20)

, DEPT_CD VARCHAR (20)

, CVS_GRP_PAX_CNT numeric (19pr 2)

, H09_CNT numeric (19pr 2)

)

INSERT INTO @ CVS_TEAM_BASE

SELECT T1.DHANGYEARrect T2.STCymQUATR T1.DHYATR QUATR T1.DHANGMONTH MONTHI T2.STCING MONTHMIT T2.TEAMOMYEARCHEREL T2.STCINGQUATR QUATR T1.STCING MONTHM GRP_CD

, SUM (CVS_DEPT_PAX_CNT) CVS_GRP_PAX_CNT

, SUM (T2.H09_CNT) ACC_H09_CNT

FROM T_DM_DHSTC T1

, T_DM_DHSTC_DEPT T2

WHERE

-- and TEAM_CD = 'EA190001'

T1.DHSTC_CD = T2.DHSTC_CD

GROUP BY T1.DH_YEAR,T2.STC_QUATR,T1.DH_MONTH,T2.STC_MONTH

, T2.TEAM_CD

INSERT INTO @ CVS_TEAM_STAT

SELECT T.TEAMCO CDJ Murray GRP_CD,T.CHECK_RADIO

FROM (

SELECT BASE.DH_YEAR,MAX (BASE.STC_QUATR) STC_QUATR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT

, SUM (ROLL.H09_CNT) ACC

, BASE.CVS_GRP_PAX_CNT

, SUM (ROLL.H09_CNT) / BASE.CVS_GRP_PAX_CNT CVS_RAT

, CASE WHEN BASE.STC_MONTH = '01' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_GRP_PAX_CNT* (33.0 Universe 100.0)) AS NUMERIC (19pen2))

WHEN BASE.STC_MONTH = '02' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_GRP_PAX_CNT* (66.0 / 100.0)) AS NUMERIC (19pen2))

WHEN BASE.STC_MONTH = '03' THEN CAST (SUM (ROLL.H09_CNT) / (BASE.CVS_GRP_PAX_CNT* (100.0plus 100.0)) AS NUMERIC (19mem2))

END CHECK_RADIO

FROM @ CVS_TEAM_BASE BASE

, @ CVS_TEAM_BASE ROLL

WHERE BASE.DH_YEAR = ROLL.DH_YEAR

-- AND BASE.DH_MONTH = ROLL.DH_MONTH

AND BASE.STC_QUATR = ROLL.STC_QUATR

AND BASE.DH_MONTH > = ROLL.DH_MONTH

AND BASE.TEAM_CD = ROLL.TEAM_CD

GROUP BY BASE.DH_YEAR,BASE.DH_MONTH,BASE.STC_MONTH,BASE.TEAM_CD,BASE.H09_CNT,BASE.CVS_GRP_PAX_CNT

) T

WHERE T.DH_YEAR = @ YEAR AND T.DH_MONTH = @ MONTH

The above is all the content of this article "case study of using SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.