1
votes

comment générer un serveur sql total de salaire

J'ai ci-dessous des tableaux appelés dailyshift, au format 24 heures

   select employeeid,sum(d.hours*salary) as weektotal from dailyshift as d
   inner join employeesalary as e
   on d.employeeid=e.employeeid
   group by employeeid

nous devons avoir un total de salaire hebdomadaire de sortie comme, nous devons faire le salaire total par employé par semaine, selon leur salaire par heure configuré.

empleeid      week               weektotal
  1         12th to 18th Aug       210
  1         19th to 25th Aug       140
  2         12th to 18th Aug       100

J'ai essayé ci-dessous mais il ne donne pas le total exact

1 dailyshift
shiftid,employeeid,shiftdate        ,starttime    ,endtime hours
1        1           16th Aug 2019 ,  08:00         15:00   7
2        1           18th Aug 2019 ,  08:00         15:00   7
2        1           22th Aug 2019 ,  08:00         15:00   7
3        2           17th Aug 2019 ,  16:00         20:00  4

2 employeesalary

employeeid,salary, startdate      , enddate
 1         ,10,  , 1st Aug 2019  , 16th Aug 2019
 1         ,20,  , 17st Aug 2019 , 20th Aug 2019
 2         ,25,  , 15st Aug 2019 , 20th Aug 2019

pouvons-nous avoir un moyen faire ça?

sql-server

1 commentaires

Vous devez ajouter la date de début et de fin dans votre sélection et grouper par ...

3 Réponses :

0
votes

Vous avez besoin du salaire applicable pour la date du quart de travail, vous devez donc effectuer les opérations suivantes:

select d.employeeid, sum(d.hours * e.salary) as weektotal
from dailyshift as d
inner join employeesalary as e
on d.employeeid = e.employeeid
and d.shiftdate >= e.startdate 
and d.shiftdate <= e.endate
group by d.employeeid

0 commentaires

0
votes

En supposant que les tables EmployeeSalary contiennent le salaire horaire des employés, chaque employé peut avoir un salaire différent pour une période différente.

La requête suivante doit vous donner le résultat attendu:

   select   employeeid, 
            DATEADD(day, -(DATEPART(dw, d.shiftdate)-1), d.shiftdate) [WeekStart],
            DATEADD(day, 7-(DATEPART(dw, d.shiftdate)), d.shiftdate) [WeekEnd],
            sum(d.hours*salary) as Weektotal 
   from     dailyshift as d
        inner join employeesalary as e
            on d.employeeid=e.employeeid and d.shiftdate between e.startdate and e.enddate
   group by employeeid, DATEADD(day, -(DATEPART(dw, d.shiftdate)-1), d.shiftdate), DATEADD(day, 7-(DATEPART(dw, d.shiftdate)), d.shiftdate)

0 commentaires

1
votes

La requête peut sembler complexe, mais si vous la parcourez partie par partie, vous comprendrez tout. CTE et CTE2 sont simplement utilisés pour la manipulation de la date pour obtenir le résultat souhaité.

employeeid  Week                    eekTotal
1           2th To 18th Aug 2019    210
2           12th To 18th Aug 2019   100

La sortie est-

 WITH CTE AS(
    SELECT *,
     DATEADD(DAY, 1 - IIF(DATEPART(WEEKDAY, CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME))-1 = 0,7,
            DATEPART(WEEKDAY, CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME))-1), 
        CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME)) [Week_Start_Date],
     DATEADD(DAY, 7 - IIF(DATEPART(WEEKDAY, CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME))-1 = 0,7,
            DATEPART(WEEKDAY, CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME))-1),
        CAST(REPLACE(REPLACE(REPLACE(REPLACE(shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME)) [Week_End_Date]
     FROM dailyshift
 ),
 CTE2 AS(
    SELECT A.shiftid,A.employeeid,A.shiftdate,A.starttime,A.endtime,A.hours,
     CAST(DAY(Week_Start_Date) AS VARCHAR)+
     CASE
            WHEN DAY(Week_Start_Date) % 100 IN (11,12,13) THEN 'th ' --first checks for exception
            WHEN DAY(Week_Start_Date) % 10 = 1 THEN 'st '
            WHEN DAY(Week_Start_Date) % 10 = 2 THEN 'nd '
            WHEN DAY(Week_Start_Date) % 10 = 3 THEN 'rd '
            ELSE 'th ' --works for num % 10 IN (4,5,6,7,8,9,0)
        END
    +'To '+
    CAST(DAY(Week_End_Date) AS VARCHAR) +
     CASE
            WHEN DAY(Week_End_Date) % 100 IN (11,12,13) THEN 'th ' --first checks for exception
            WHEN DAY(Week_End_Date) % 10 = 1 THEN 'st '
            WHEN DAY(Week_End_Date) % 10 = 2 THEN 'nd '
            WHEN DAY(Week_End_Date) % 10 = 3 THEN 'rd '
            ELSE 'th ' --works for num % 10 IN (4,5,6,7,8,9,0)
        END +
    LEFT(DATENAME(Month,Week_End_Date),3) +' '+
    CAST(YEAR(Week_End_Date) AS VARCHAR) grp_clm_name
     FROM CTE A
 )

 SELECT A.employeeid,
 A.grp_clm_name Week,
 SUM(A.hours*B.salary) WeekTotal
 FROM CTE2 A
 INNER JOIN employeesalary B 
 ON A.employeeid = B.employeeid
 AND  CAST(REPLACE(REPLACE(REPLACE(REPLACE(A.shiftdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME)
     BETWEEN  CAST(REPLACE(REPLACE(REPLACE(REPLACE(B.startdate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME)
     AND  CAST(REPLACE(REPLACE(REPLACE(REPLACE(B.enddate,'st ','-'),'nd ','-'),'rd ','-'),'th ','-') AS DATETIME)
 GROUP BY A.employeeid, A.grp_clm_name
 ORDER BY 1

La ligne avec la valeur "1 du 19 au 25 août 140" est manquante car vous n'avez pas de définition de salaire pour l'employé 1 pour la date du 22 août 2019

0 commentaires