SQL comparison with values written in different ways

Navigation
0

I am comparing two tables "A" and "B" in which I want to compare values of columns with the same meaning, but are written in different ways. The table "A" has the column "Mes_ano" and has values "jan, fev, mar, abr ..." while in table "B" has column "Months_promotion" and has random values of 01 to 12. How can I make these correlations via SQL by picking "Funcionario_mes" from table "A" when "fev" from table A corresponds to "02" from table "B"?
The result of SQL should bring only the months quoted in table "B". Could someone help me?

    
asked by anonymous 25.07.2017 / 01:40

1 answer

1

I wanted to understand why storing 'jan' in a column in the database, puts ... but, come on:

You can make a Case When directly in the query, or create a function for it, as I imagine you will have to use it again in other querys, the function is most appropriate: 

CREATE FUNCTION [dbo].[mesFromName]
(
    @mes Varchar(20)
)
RETURNS INT
AS
BEGIN
    RETURN
        CASE 
            WHEN @mes like 'jan%' THEN 1
            WHEN @mes like 'fev%' THEN 2
            WHEN @mes like 'mar%' THEN 3
            WHEN @mes like 'abr%' THEN 4
            WHEN @mes like 'mai%' THEN 5
            WHEN @mes like 'jun%' THEN 6
            WHEN @mes like 'jul%' THEN 7
            WHEN @mes like 'ago%' THEN 8
            WHEN @mes like 'set%' THEN 9
            WHEN @mes like 'out%' THEN 10
            WHEN @mes like 'nov%' THEN 11
            WHEN @mes like 'dez%' THEN 12
            ELSE 0
        END 

END

After that, just join the tables using the function: 

Select
a.mes, 
a.funcionario,
case 
    when p.mes is null then 
        'Não' 
    else 
   'Sim' 
End  as teve_promocao
from tabelaa a 
left outer join promocao p on dbo.mesFromName(a.mes) = p.mes;

I hope I have helped. And do not forget to make a response.

I put it in SQL Fiddle: link

    
25.07.2017 / 02:30
How to send data from an HTML page to MySQL? Connecting MS Access and PHP