multa nos dias úteis
18/08/2020
0
Boa tarde Amigos.
Estou com um problema, quero calcular a percentagem de multa a ser cobrada, mas devo ter em conta o mês corrente se a data em que calha a multa não é final de semana; feriado.
Select CC.IdAluno as aluid,CC.ANO as Ano,CC.IdMovimento as MovId, FM.IdFamMov as FMId,FM.Nome as FMNome,CC.IdMES as Mes,CC.NMOV as NMOV,CC.NDOC as NDOC,CC.VALORIGA as VALORIGA,CC.TOTDBMB as TOTDBMB, CC.TOTCRMB as TOTCRMB,
CC.TOTDBMS as TOTDBMS,CC.TOTCRMS as TOTCRMS,PD.IdPERIODO ,CONVERT(VARCHAR, CC.DATAMOV, 103) as DataMov,mov.Nome as Nome,Mov.TMov,mov.VerMovAnt,mov.VeriDivi,mov.Passa,
ISNUll((CASE WHEN DATENAME(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)) IN (N''''''''Saturday'''''''', N''''''''Sunday'''''''') THEN 0.00 else
(CASE WHEN CC.IdMovimento = EM.IdMovimento then 0.00
WHEN DATAMOV = ''''''''2018-2-12'''''''' then 0.00 else
m.Percer * ((VALORIGA - cc.TOTCRMB)- CC.Desconto)/100 end)
END),0.00) AS ''''''''Valor Multa'''''''',
CC.Desconto as Desconto, DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') as NrDias, DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1 as teste,DATEPART(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-17'''''''') +0), CC.DATAMOV)) AS SEMANA,
DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)
FROM tblRegC_CDOC CC
LEFT JOIN tblRegMovimento Mov on CC.IdMovimento = mov.IdMovimento
LEFT JOIN tblExcepMulta EM on CC.IdMovimento = EM.IdMovimento
left JOIN tblFamMov FM on mov.IdFamMov = FM.IdFamMov
left JOIN tblPERIODO PD on MOV.IdPERIODO = PD.IdPERIODO
LEFT JOIN tblMulta M on (mov.IdMovimento = M.IdMovimento AND DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''')>=M.LimInf AND DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''')<=M.LimSup)
LEFT JOIN tblFeriados F on month(DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)) = (MONTH(f.MesF))
WHERE CC.IdAluno = 14216 AND TOTDBMB <> TOTCRMB AND CC.IdUdadeOrg =1
order by FM.IdFamMov,CC.IdAluno,CC.IdMovimento,CC.ANO,CC.IdMES,DataMov
Estou com um problema, quero calcular a percentagem de multa a ser cobrada, mas devo ter em conta o mês corrente se a data em que calha a multa não é final de semana; feriado.
Select CC.IdAluno as aluid,CC.ANO as Ano,CC.IdMovimento as MovId, FM.IdFamMov as FMId,FM.Nome as FMNome,CC.IdMES as Mes,CC.NMOV as NMOV,CC.NDOC as NDOC,CC.VALORIGA as VALORIGA,CC.TOTDBMB as TOTDBMB, CC.TOTCRMB as TOTCRMB,
CC.TOTDBMS as TOTDBMS,CC.TOTCRMS as TOTCRMS,PD.IdPERIODO ,CONVERT(VARCHAR, CC.DATAMOV, 103) as DataMov,mov.Nome as Nome,Mov.TMov,mov.VerMovAnt,mov.VeriDivi,mov.Passa,
ISNUll((CASE WHEN DATENAME(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)) IN (N''''''''Saturday'''''''', N''''''''Sunday'''''''') THEN 0.00 else
(CASE WHEN CC.IdMovimento = EM.IdMovimento then 0.00
WHEN DATAMOV = ''''''''2018-2-12'''''''' then 0.00 else
m.Percer * ((VALORIGA - cc.TOTCRMB)- CC.Desconto)/100 end)
END),0.00) AS ''''''''Valor Multa'''''''',
CC.Desconto as Desconto, DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') as NrDias, DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1 as teste,DATEPART(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-17'''''''') +0), CC.DATAMOV)) AS SEMANA,
DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)
FROM tblRegC_CDOC CC
LEFT JOIN tblRegMovimento Mov on CC.IdMovimento = mov.IdMovimento
LEFT JOIN tblExcepMulta EM on CC.IdMovimento = EM.IdMovimento
left JOIN tblFamMov FM on mov.IdFamMov = FM.IdFamMov
left JOIN tblPERIODO PD on MOV.IdPERIODO = PD.IdPERIODO
LEFT JOIN tblMulta M on (mov.IdMovimento = M.IdMovimento AND DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''')>=M.LimInf AND DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''')<=M.LimSup)
LEFT JOIN tblFeriados F on month(DATEADD(day, (DATEDIFF(day,CC.DATAMOV,''''''''2018-2-12'''''''') -1), CC.DATAMOV)) = (MONTH(f.MesF))
WHERE CC.IdAluno = 14216 AND TOTDBMB <> TOTCRMB AND CC.IdUdadeOrg =1
order by FM.IdFamMov,CC.IdAluno,CC.IdMovimento,CC.ANO,CC.IdMES,DataMov
Mauro Augusto
Curtir tópico
+ 0
Responder
Post mais votado
19/08/2020
De que data estamos falando? No exemplo, seria '2018-2-12' ?
SELECT CC.IdAluno as aluid, CC.ANO, CC.IdMovimento as MovId, FM.IdFamMov as FMId, FM.Nome as FMNome, CC.IdMES as Mes, CC.NMOV, CC.NDOC, CC.VALORIGA, CC.TOTDBMB, CC.TOTCRMB, CC.TOTDBMS, CC.TOTCRMS, PD.IdPERIODO, CONVERT(VARCHAR, CC.DATAMOV, 103) as DataMov, Mov.Nome, Mov.TMov, Mov.VerMovAnt, Mov.VeriDivi, Mov.Passa, ISNUll( (CASE WHEN DATENAME(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,'2018-2-12') -1), CC.DATAMOV)) IN (N'Saturday', N'Sunday') THEN 0.00 ELSE (CASE WHEN CC.IdMovimento = EM.IdMovimento then 0.00 WHEN DATAMOV = '2018-2-12' then 0.00 ELSE M.Percer * ((VALORIGA - CC.TOTCRMB)- CC.Desconto)/100 END) END), 0.00 ) AS 'Valor Multa', CC.Desconto, DATEDIFF(day,CC.DATAMOV,'2018-2-12') as NrDias, DATEDIFF(day,CC.DATAMOV,'2018-2-12')-1 as Teste, DATEPART(weekday, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,'2018-2-17')+0), CC.DATAMOV)) AS SEMANA, DATEADD(day, (DATEDIFF(day,CC.DATAMOV,'2018-2-12') -1), CC.DATAMOV) SemNome FROM tblRegC_CDOC CC LEFT JOIN tblRegMovimento Mov on Mov.IdMovimento = CC.IdMovimento LEFT JOIN tblExcepMulta EM on EM.IdMovimento = CC.IdMovimento LEFT JOIN tblFamMov FM on FM.IdFamMov = Mov.IdFamMov LEFT JOIN tblPERIODO PD on PD.IdPERIODO = Mov.IdPERIODO LEFT JOIN tblMulta M on M.IdMovimento = Mov.IdMovimento AND DATEDIFF(day,CC.DATAMOV,'2018-2-12') BETWEEN M.LimInf AND M.LimSup LEFT JOIN tblFeriados F on MONTH(f.MesF) = MONTH(DATEADD(day, (DATEDIFF(day,CC.DATAMOV,'2018-2-12')-1), CC.DATAMOV)) WHERE CC.IdAluno = 14216 AND TOTDBMB <> TOTCRMB AND CC.IdUdadeOrg =1 ORDER BY FM.IdFamMov, CC.IdAluno, CC.IdMovimento, CC.ANO, CC.IdMES, DataMov
Emerson Nascimento
Responder
Clique aqui para fazer login e interagir na Comunidade :)
