Linha 103: |
Linha 103: |
| SELECT '13894' ID | | SELECT '13894' ID |
| ) X | | ) X |
| + | ), |
| + | </syntaxhighlight> |
| + | |
| + | === Assinantes === |
| + | * E |
| + | <syntaxhighlight lang="sql"> |
| + | ASSINANTES (ID, EMAIL) AS ( |
| + | /* COORDENADOR */ |
| + | SELECT 1 ID, PC.EMAIL |
| + | FROM STCC T |
| + | INNER JOIN STCCMATALUNO MA ON (MA.CODCOLIGADA = T.CODCOLIGADA |
| + | AND MA.IDTCC = T.IDTCC) |
| + | INNER JOIN STURMADISC TD ON (MA.CODCOLIGADA = TD.CODCOLIGADA |
| + | AND MA.IDTURMADISC = TD.IDTURMADISC) |
| + | INNER JOIN SHABILITACAOFILIAL HF ON (HF.CODCOLIGADA = TD.CODCOLIGADA |
| + | AND HF.IDHABILITACAOFILIAL = TD.IDHABILITACAOFILIAL) |
| + | INNER JOIN (SELECT C.CODCOLIGADA, C.IDHABILITACAOFILIAL, P.EMAIL, |
| + | ROW_NUMBER() OVER(PARTITION BY C.CODCOLIGADA, C.IDHABILITACAOFILIAL |
| + | ORDER BY C.CODCOLIGADA, C.IDHABILITACAOFILIAL, |
| + | CASE C.FUNCAO WHEN 'V' THEN 0 ELSE 1 END) ID |
| + | FROM SCOORDENADOR C |
| + | INNER JOIN PPESSOA P ON (C.CODPESSOA = P.CODIGO) |
| + | WHERE C.FUNCAO IN ('C', 'V') |
| + | AND CAST(GETDATE() AS DATE) BETWEEN C.DTINICIO AND ISNULL(C.DTFIM, GETDATE())) PC ON (HF.CODCOLIGADA = PC.CODCOLIGADA |
| + | AND HF.IDHABILITACAOFILIAL = PC.IDHABILITACAOFILIAL |
| + | AND PC.ID = 1) |
| + | WHERE T.CODCOLIGADA = @_CODCOLIGADA |
| + | AND T.IDTCC = @_IDTCC |
| ) | | ) |
| </syntaxhighlight> | | </syntaxhighlight> |