/
Number of Payroll Days in the Month
Number of Payroll Days in the Month
- Valentyna Herasymova
- Daria Chebotaieva
Owned by Valentyna Herasymova, created
1 Description | 2 SQL Statement | 3 Other
Summary | The view to check the total of payroll/salary days in the month |
|---|---|
Keywords | Payroll days, Salary days |
Description
The view will select and sum the salary days from the hardcoded list of Entry Codes defined by the customer.
SQL Statement
CREATE VIEW dbo.PW001SRV534 AS
SELECT
X.PIN,
X.NAME,
X.DAYS,
X.[Payscale Table],
X.[Payscale Code],
X.Numorgid,
X.EMPLOYMENTSTARTDATE,
X.EMPLOYMENTENDDATE,
PW.NAME AS 'VESSEL'
FROM
( SELECT
P01.PIN ,
P01.NAME,
--sum(dbo.ad_StrToFloat(entryfield2)) as Days,
SUM(SUM(dbo.ad_StrToFloat(entryfield2))) OVER(PARTITION BY p01.PIN) AS 'DAYS',
--ORG.NAME AS Vessel,
p0p.PAYSCALETABLE as 'Payscale Table',
p0p.PAYSCALECODE as 'Payscale Code',
P01.CLIENT Numorgid,
P01.EMPLOYMENTSTARTDATE ,
P01.EMPLOYMENTENDDATE
FROM PW001PAY PAY
LEFT JOIN PW001P01 P01 ON P01.PIN=PAY.PIN
LEFT JOIN PWORG ORG ON PAY.ACCOUNTDIM2=ORG.NUMORGID
LEFT JOIN PW001P0P P0P ON (P01.PIN=P0P.PIN AND (P0P.PNUMBER='A'))
where pay.entrycode in (100, 102, 103, 104, 105, 107, 108, 109, 110)
group by P01.PIN ,
P01.NAME,
P01.CLIENT,
p0p.PAYSCALETABLE,
p0p.PAYSCALECODE,
P01.CLIENT,
--ORG.NAME,
P01.EMPLOYMENTSTARTDATE ,
P01.EMPLOYMENTENDDATE
) X
JOIN PW001PAY PAYx
ON PAYX.PIN=X.PIN
JOIN PWORG PW
ON PW.NUMORGID=PAYX.ACCOUNTDIM2
AND NOT EXISTS
(SELECT PAYT.FromDate FROM PW001PAY PAYT
WHERE PAYT.PIN=PAYX.PIN
--AND PAYT.P03_CODE IN ('ON', 'ILL ON')
AND PAYT.ENTRYCODE IN (100, 102, 103, 104, 105, 107, 108, 109, 110)
AND PAYT.P03_DATEFROM > PAYX.P03_DATEFROM)
where payX.entrycode in (100, 102, 103, 104, 105, 107, 108, 109, 110)
GROUP BY
X.PIN,
X.NAME,
X.DAYS,
X.[Payscale Table],
X.[Payscale Code],
X.Numorgid,
EMPLOYMENTSTARTDATE,
X.EMPLOYMENTENDDATE,
PW.NAME
Other