multa nos dias úteis

SQL Server

18/08/2020

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
Mauro Augusto

Mauro Augusto

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

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


GOSTEI 1
POSTAR