Return records according to percentage in sql server

Asked

Viewed 125 times

-1

Good night, I need to make a select that returns only the records that were paid more than 60% of the installments.

I created a table with the following structure:

CREATE TABLE PARCELAS(
IDPARCELAS INT IDENTITY(1,1) PRIMARY KEY,
IDFINANCIAMENTO INT FOREIGN KEY REFERENCES FINANCIAMENTO(IDFINANCIAMENTO),
NUMERO INT NOT NULL,
VALOR_PARCELA DECIMAL(18,2) NOT NULL,
DATA_VENCIMENTO DATE NOT NULL,
DATA_PAGAMENTO DATE 
);

Select result: 

1 213 2 1 2333.33 2019-07-25 2019-07-25
2 214 2 2 2333.33 2019-08-25 2019-08-25
3 215 2 3 2333.33 2019-09-25 2019-09-25
4 216 2 4 2333.33 2019-10-25 2019-10-25
5 217 2 5 2333.33 2019-11-25 2019-11-25
6 218 2 6 2333.33 2019-12-25 2019-12-25
7 219 2 7 2333.33 2020-01-25 2020-01-25
8 220 2 8 2333.33 2020-02-25 2020-02-25
9 221 2 9 2333.33 2020-03-25 2020-03-25
10 222 2 10 2333.33 2020-04-25 2020-04-25
11 223 2 11 2333.33 2020-05-25 2020-05-25
12 224 2 12 2333.33 2020-06-25 2020-06-25
13 225 2 13 2333.33 2020-07-25 2020-07-25
14 226 2 14 2333.33 2020-08-25 2020-08-25
15 227 2 15 2333.33 2020-09-25 2020-09-25
16 228 3 1 3000.00 2019-07-25 NULL
17 229 3 2 3000.00 2019-08-25 NULL
18 230 3 3 3000.00 2019-09-25 NULL
19 231 3 4 3000.00 2019-10-25 NULL
20 232 3 5 3000.00 2019-11-25 NULL
21 233 3 6 3000.00 2019-12-25 NULL
22 234 3 7 3000.00 2020-01-25 NULL
23 235 3 8 3000.00 2020-02-25 NULL
24 236 3 9 3000.00 2020-03-25 NULL
25 237 3 10 3000.00 2020-04-25 NULL
26 238 3 11 3000.00 2020-05-25 NULL
27 239 3 12 3000.00 2020-06-25 NULL
28 240 3 13 3000.00 2020-07-25 NULL
29 241 3 14 3000.00 2020-08-25 NULL
30 242 3 15 3000.00 2020-09-25 NULL
31 243 4 1 2200.00 2020-04-25 1900-01-01
32 244 4 2 2200.00 2020-05-25 1900-01-01
33 245 4 3 2200.00 2020-06-25 1900-01-01
34 246 4 4 2200.00 2020-07-25 1900-01-01
35 247 4 5 2200.00 2020-08-25 1900-01-01
36 248 4 6 2200.00 2020-09-25 1900-01-01
37 249 4 7 2200.00 2020-10-25 1900-01-01
38 250 4 8 2200.00 2020-11-25 1900-01-01
39 251 4 9 2200.00 2020-12-25 1900-01-01
40 252 4 10 2200.00 2021-01-25 1900-01-01
41 253 4 11 2200.00 2021-02-25 1900-01-01
42 254 4 12 2200.00 2021-03-25 1900-01-01
43 255 4 13 2200.00 2021-04-25 1900-01-01
44 256 4 14 2200.00 2021-05-25 1900-01-01
45 257 4 15 2200.00 2021-06-25 1900-01-01
46 258 5 1 1266.66 2020-01-25 2020-01-25
47 259 5 2 1266.66 2020-02-25 2020-02-25
48 260 5 3 1266.66 2020-03-25 2020-03-25
49 261 5 4 1266.66 2020-04-25 NULL
50 262 5 5 1266.66 2020-05-25 NULL
51 263 5 6 1266.66 2020-06-25 NULL
52 264 5 7 1266.66 2020-07-25 NULL
53 265 5 8 1266.66 2020-08-25 NULL
54 266 5 9 1266.66 2020-09-25 NULL
55 267 5 10 1266.66 2019-07-25 NULL
56 268 5 11 1266.66 2019-08-25 NULL
57 269 5 12 1266.66 2019-09-25 NULL
58 270 5 13 1266.66 2019-10-25 NULL
59 271 5 14 1266.66 2019-11-25 NULL
60 272 5 15 1266.66 2019-12-25 NULL

Where this table has 60 records. I need to bring only the records where the 3rd column is 2 and 4, because in them more than 60% of the installments were paid. An unpaid installment is with the payment date as NULL, as I can do with select without using procedures.

Thanks in advance.

sql sql-server

  • What do you save in this NUMBER field? It gets the value 2 4?

  • Old man, put the select you did too. A doubt, you make a insert of all plots and then you make a update in the registration of the installment paid?

  • @Danielmendes esse 2 e 4 é uma FK de outra tabela, chamada financiamento, onde o financiamento 2 tem esses parcelas e o 4 tb

  • @Tmilitino but my doubt is this, I do not know how I could make the select, did the Insert in hand, direct added already with the null fields

  • 1

    What I don’t understand is that you display 7 columns as a result, but your table only has 6 fields. It all seems that your query is very simple to create, but it is necessary to understand this data.

  • I just want the select that you used to bring this result

  • @Danielmendes the first column is only the number of records, from the second onwards that are the fields of my table

  • @Tmilitino select * from parcels

  • Ah, ok. A would be the fourth column in case then it should have the value 2 and 4? Would it be 2 and 4 even? Or would it be 2 OR (OR) 4?

  • @Danielmendes I gave an example, I need to return only the financing (column 2) that has 60% of the installments paid, that is, if each of them has 15 installments, I have to bring everyone who has paid at least 9 installments, that would be 2 and 4, because 2 and 4 have the 15 installments paid, 3 has none and 5 has 3, understood?

  • A basic question, which we can not deduce from what you wrote: Which field gives information on the amount of installments actually paid? Or how can I determine such a quantity from the data in your table (along with the example data you posted)? Assuming the second field of your select is IDFINANCIAMENTO it does not repeat itself, at least in your example. There is also what has already been asked: your table has 6 fields but your example of select has 7 fields.

  • Let’s go, again, the first field is only the number of records of the table, is the number of rows, the column idfinanfiamento in this case would be 3 column. To find out if she ta pays the payment date field can not be as null

  • face understood now, from a reformulated question that got very confused

Show 8 more comments

1 answer

0

Follows solution:

select idfinanciamento
,count(IDPARCELAS)
,count(data_pagamento)
,count(data_pagamento)*100/count(IDPARCELAS) as percentual_pago
from parcelas
where idfinanciamento in
      (select distinct idfinanciamento from parcelas where data_pagamento is not null)
group by idfinanciamento
having  count(data_pagamento)*100/count(IDPARCELAS) >= 60

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.