Last active
June 14, 2016 21:19
-
-
Save marttosc/d4f337c96af4cefdfbf165b0d1e4b38e to your computer and use it in GitHub Desktop.
MySQL DML - Locação
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Generated from SQLines | |
SELECT CLI.NOME, | |
( | |
CASE WHEN TPC.CODIGO = 1 THEN | |
SUM(VEI.VALORKM * (DEV.KMATUAL - VEI.KM) + (SELECT SUM(D.VALOR) FROM devolucao D WHERE D.LOCACAO = LOC.CODIGO)) ELSE | |
SUM(VEI.VALORDIARIA * DEV.DATAHORA- LOC.DATAHORA + (SELECT SUM(D.VALOR) FROM devolucao D WHERE D.LOCACAO = LOC.CODIGO)) | |
END | |
) TOTAL | |
FROM cliente CLI, locacao LOC, tipocobranca TPC, veiculo VEI, devolucao DEV | |
WHERE | |
CLI.CODIGO = 1 AND | |
LOC.CLIENTE = CLI.CODIGO AND | |
TPC.CODIGO = LOC.TIPOCOBRANCA AND | |
VEI.CODIGO = LOC.VEICULO AND | |
DEV.LOCACAO = LOC.CODIGO; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT CLI.NOME, | |
( | |
IF ( | |
TPC.CODIGO = 1, | |
SUM(VEI.VALORKM * (DEV.KMATUAL - VEI.KM) + (SELECT SUM(D.VALOR) FROM devolucao D WHERE D.LOCACAO = LOC.CODIGO)), | |
SUM(VEI.VALORDIARIA * DATEDIFF(DEV.DATAHORA, LOC.DATAHORA) + (SELECT SUM(D.VALOR) FROM devolucao D WHERE D.LOCACAO = LOC.CODIGO)) | |
) | |
) TOTAL | |
FROM cliente CLI, locacao LOC, tipocobranca TPC, veiculo VEI, devolucao DEV | |
WHERE | |
CLI.CODIGO = 1 AND | |
LOC.CLIENTE = CLI.CODIGO AND | |
TPC.CODIGO = LOC.TIPOCOBRANCA AND | |
VEI.CODIGO = LOC.VEICULO AND | |
DEV.LOCACAO = LOC.CODIGO; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment