Junction of SQL SERVER Selects

Question

Junction of SQL SERVER Selects

Navigation

#1 by (2 votes)

3

I have the following selects, but I need to be returned that data together, joining the selects does anyone suggest something?

SELECT 
COUNT(numero_parcela) as 'total_parcelas',
SUM(valor_original) as 'valor_original', 
SUM(valor_pago) as 'valor_pago' 
FROM financeiro.contas_receber 
WHERE id_cliente = 247

SELECT 
COUNT (numero_parcela) as 'parcelas_atraso', 
SUM(valor_original) as 'valor_atraso'
FROM financeiro.contas_receber
WHERE id_cliente = 247 and data_vencimento < GETDATE()

Thank you very much

sql-server

asked by anonymous 28.01.2016 / 00:03

1 answer

Value attribute of options in select are not set correctly Java String byte array with negative numbers

score 2 · Answer 1

You can use the two options below, I would use the first option as it is the same table, thus avoiding unnecessary processing:

CASE WHEN:

SELECT 
    COUNT(numero_parcela) as 'total_parcelas',
    SUM(valor_original) as 'valor_original', 
    SUM(valor_pago) as 'valor_pago',
    SUM(CASE WHEN data_vencimento < GETDATE() THEN valor_origianl ELSE 0 END) as 'valor_atraso'
FROM financeiro.contas_receber 
WHERE id_cliente = 247

CROSS JOIN:

SELECT 
    COUNT(a.numero_parcela) as 'total_parcelas',
    SUM(a.valor_original) as 'valor_original', 
    SUM(a.valor_pago) as 'valor_pago',
    b.parcelas_atraso,
    b.valor_atraso
FROM financeiro.contas_receber as a
CROSS JOIN (
    SELECT 
        COUNT (numero_parcela) as 'parcelas_atraso', 
        SUM(valor_original) as 'valor_atraso'
    FROM financeiro.contas_receber
    WHERE id_cliente = 247 and data_vencimento < GETDATE()
) as b
WHERE a.id_cliente = 247