...
| Cp panel macro | ||
|---|---|---|
| ||
Hi, Good day. Please see the list of [#VesselName]'s vessel location where dates are missing or has an overlapping dates for the [#CurrentPeriod] payroll period. Start Date End Date Vessel Location Kind Regards, |
All vessel location who has missing date or overlapping dates for the current payroll period.
Sample Output
...
SQL script
| Code Block | ||
|---|---|---|
| ||
;WITH cteSrc AS(
SELECT VESLOC.CODE
,CONVERT(VARCHAR ,VESLOC.STARTDATE ,110) AS STARTDATE
,CONVERT(VARCHAR ,VESLOC.ENDDATE ,110) AS ENDDATE
,C44.[NAME] AS VESLOCNAME
,VES.[NAME] AS VESSELNAME
,VES.NUMORGID AS VESSELNUMORGID
,PAY.COMPANYID
,CMP.[NAME] AS COMPANYNAME
,PAY.PREPARATIONDATE
,PAY.COMPLETIONDATE
,VESPAY.PERIODE
FROM PWVESCOSTPLACE VESLOC
JOIN PW001C44 C44
ON C44.CODE = VESLOC.CODE
JOIN PWORGVES VESPAY
ON VESPAY.NUMORGID = VESLOC.NUMORGID
LEFT JOIN PWORG VES
ON VES.NUMORGID = VESLOC.NUMORGID
JOIN PWCMPPAY PAY
ON PAY.COMPANYID = VES.NUMORGIDABOVE
AND PAY.[PERIOD] = VESPAY.PERIODE
AND (
(
VESLOC.STARTDATE BETWEEN PAY.PREPARATIONDATE AND PAY.COMPLETIONDATE
AND VESLOC.ENDDATE>PAY.COMPLETIONDATE
)
OR (
VESLOC.ENDDATE BETWEEN PAY.PREPARATIONDATE AND PAY.COMPLETIONDATE
AND VESLOC.STARTDATE<PAY.PREPARATIONDATE
)
OR (
VESLOC.STARTDATE<=PAY.PREPARATIONDATE
AND VESLOC.ENDDATE>=PAY.COMPLETIONDATE
)
)
LEFT JOIN PWORG CMP
ON CMP.NUMORGID = PAY.COMPANYID
UNION ALL
--start missing dates
SELECT VESLOC.CODE
,CASE
WHEN VESLOC.STARTDATE IS NULL THEN 'Missing Date'
ELSE CONVERT(VARCHAR ,VESLOC.STARTDATE ,110)
END AS STARTDATE
,CASE
WHEN VESLOC.ENDDATE IS NULL THEN 'Missing Date'
ELSE CONVERT(VARCHAR ,VESLOC.ENDDATE ,110)
END AS ENDDATE
,C44.[NAME] AS VESLOCNAME
,VES.[NAME] AS VESSELNAME
,VES.NUMORGID AS VESSELNUMORGID
,PAY.COMPANYID
,CMP.[NAME] AS COMPANYNAME
,PAY.PREPARATIONDATE
,PAY.COMPLETIONDATE
,VESPAY.PERIODE
FROM PWVESCOSTPLACE VESLOC
JOIN PW001C44 C44
ON C44.CODE = VESLOC.CODE
AND (VESLOC.STARTDATE IS NULL OR VESLOC.ENDDATE IS NULL)
JOIN PWORGVES VESPAY
ON VESPAY.NUMORGID = VESLOC.NUMORGID
LEFT JOIN PWORG VES
ON VES.NUMORGID = VESLOC.NUMORGID
JOIN PWCMPPAY PAY
ON PAY.COMPANYID = VES.NUMORGIDABOVE
AND PAY.[PERIOD] = VESPAY.PERIODE
LEFT JOIN PWORG CMP
ON CMP.NUMORGID = PAY.COMPANYID
)
SELECT (
(
SELECT STARTDATE+CHAR(9)+' '+ENDDATE+' '+CHAR(9)+VESLOCNAME+' '+CHAR(10)
FROM cteSrc z
WHERE z.VESSELNUMORGID = ves.NUMORGID
ORDER BY
STARTDATE
FOR XML PATH('')
,TYPE
).value('.' ,'varchar(max)')
) AS #DETAILS
,(
SELECT DISTINCT PERIODE
FROM cteSrc z
WHERE z.VESSELNUMORGID = ves.NUMORGID
) AS #CurrentPeriod
,ves.[NAME] AS #VesselName
,tel.teleno AS #Email
--'arvin.john.salandanan@adonishr.com' AS #Email
FROM PWORG ves
LEFT JOIN PWORGTEL TEL
ON TEL.NUMORGID = ves.NUMORGID
AND TEL.TELETYPE = '6'
WHERE ves.ORGTYPE = 3
AND ISNULL(ves.PASSIVE ,'')<>'T'
--AND ves.NUMORGID = 100000025 |