CAP

[brunosiena]

select 'CAP01' + right('00000000000000'+cpf,14)

• right('000000000' + cast(NR_MATRIC_COOP as varchar),09)
• right('0000' + cast(CD_POSTO as varchar),04)
• right('00000000000000' + cast(CPF as varchar),14)
• '000000000'
• '000000000'
• '0000'
• right('00000000000000' + cast(CPF as varchar),14)
• '000000000'
• '000000000'
• RIGHT('00000000000000' + REPLACE(CAST(SUM(REPLACE(VL_LANCAMENTO, '.', '')) AS VARCHAR), '.', ''), 14)
• '00000000000000' -- Valor a integralizar, confirmar com cooperativa
• '' --Situação confirmar com cooperativa e fazer tratamento
• dateformat(isnull(DT_ADMISSAO_COOP,'1900-01-01'),'YYYYMMDD')
• dateformat(isnull(a.DT_DEMISSAO,'1900-01-01'),'YYYYMMDD')
• '00000000' -- data óbito
• right('000000000' + cast(a.NR_CONTA_CORRENTE as varchar),09) as out_res from ASSOCIADO a inner join capital c on c.cd_associado = a.cd_associado where fl_pessoa = 'F' group by cpf, CD_POSTO, NR_MATRIC_COOP, DT_ADMISSAO_COOP, a.NR_CONTA_CORRENTE, a.DT_DEMISSAO;

[OhBoyItsWorking]

/ OBS Corrigi uns campos que estavam fora do ordem, por culpa minha mesmo(Vitor)/

select 'CAP01' 
+ right('000000000' + cast(NR_MATRIC_COOP as varchar),09)
+ right('0000' + cast(CD_POSTO as varchar),04)
+ right('00000000000000' + cast(CPF as varchar),14)
+ '000000000'
+ '000000000'
+ '0000'
+ RIGHT('00000000000000' + REPLACE(CAST(SUM(REPLACE(VL_LANCAMENTO, '.', '')) AS VARCHAR), '.', ''), 14)
+ '00000000000000' -- Valor a integralizar, confirmar com cooperativa
+ 'A' --Situação confirmar com cooperativa e fazer tratamento
+ dateformat(isnull(DT_ADMISSAO_COOP,'1900-01-01'),'YYYYMMDD')
+ dateformat(isnull(a.DT_DEMISSAO,'1900-01-01'),'YYYYMMDD')
+ '00000000' -- data óbito
+ right('000000000' + cast(a.NR_CONTA_CORRENTE as varchar),09)
as out_res
from ASSOCIADO a
inner join capital c on c.cd_associado = a.cd_associado
where fl_pessoa = 'F'
group by cpf, CD_POSTO, NR_MATRIC_COOP, DT_ADMISSAO_COOP, a.NR_CONTA_CORRENTE, a.DT_DEMISSAO;
output to 'C:\Users\Home\Documents\unload\cap01.dat' format text