take field name in query and popular other!

Asked

Viewed 44 times

-1

I’m doing a search at the bank.

On the table, I have 6 dates: data1, data2, data3, data4, data5 e data6.

I’m sweeping these dates to find out which one matches the current month.

This is already being done:

...
WHERE 
 YEAR(data1) = YEAR(CURRENT_DATE) AND MONTH(data1) = MONTH(CURRENT_DATE) OR
 YEAR(data2) = YEAR(CURRENT_DATE) AND MONTH(data2) = MONTH(CURRENT_DATE) OR
 YEAR(data3) = YEAR(CURRENT_DATE) AND MONTH(data3) = MONTH(CURRENT_DATE) OR
 YEAR(data4) = YEAR(CURRENT_DATE) AND MONTH(data4) = MONTH(CURRENT_DATE) OR
 YEAR(data5) = YEAR(CURRENT_DATE) AND MONTH(data5) = MONTH(CURRENT_DATE) OR
 YEAR(data6) = YEAR(CURRENT_DATE) AND MONTH(data6) = MONTH(CURRENT_DATE)
ORDER BY cliente, tipo, id

Now I need to find out which date (1,2,3,4,5 or 6) that fell to catch that number search in another field called valorX, where x is the value of the date field (1,2,3,4,5 or 6) and concatenate and return how many reals are in that field.

Somebody help me with this logic?

mysql sql

  • Already evaluated the use of CASE / WHEN?

  • Not yet. I don’t really know how to do it! My difficulty here is logic. I think!

1 answer

0

I didn’t understand your construction for the value of the year and month to be searched but try to put in the list of fields of your SELECT:

SUM (CASE WHEN (year(data1) = ano AND month(data1) = dia) THEN valor1 
     CASE WHEN (year(data2) = ano AND month(data2) = dia) THEN valor2 
     CASE WHEN (year(data3) = ano AND month(data3) = dia) THEN valor3 
     CASE WHEN (year(data4) = ano AND month(data4) = dia) THEN valor4 
     CASE WHEN (year(data5) = ano AND month(data5) = dia) THEN valor5 
     CASE WHEN (year(data6) = ano AND month(data6) = dia) THEN valor6) AS valor_total

By "concatenate" I understood that you want to add the values.

  • I guess the way I’m trying to do it isn’t possible with just one SQL. But it is as follows: After sql decides on which field date (data1, dat2...data6) fell the year and the month past, then it will take that value (1,2,...6) and will concatenate with the word value getting value1, value2, etc... and then search for the content of the same return that is in the value-number field. I think I will have to change the approach because it is getting very messy! and may end up leaving the focus of the forum.

Browser other questions tagged mysql sql

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