SQL Server - Monthly period comparison

Question

Hi, guys. I have a big question.

I have two banks (Mysql) where I accumulate sales of several stores and use a third bank where I do an ETL using the data union.

In my third database (SQL Server) I need to create a query that does a monthly period comparison and indicate if a client is INACTIVE or ACTIVE.

Examples

Condition 1: If a particular customer makes purchases in a particular store that accumulated the value is >= R$230,00 in the month of January 2018, it receives the status of "ACTIVE" and "DIRECT SALE" at that time.

Condition 2: If the same customer of condition 1 makes purchases in the same store in March 2018 and the accumulated value is < R$230,00, it receives the status of "INACTIVE" and does not receive the status of "DIRECT SALE" at that time.

Table structure for example

In this example Maria Aparecida gained the status of ACTIVE in the month of January and INACTIVE in the month of March for not having reached the minimum amount of R$230,00 monthly.

Answer 1

SELECT q.*, CASE WHEN /*q.mes = 1 AND*/ q.valor >= 230.0                  THEN 'ATIVO'
                 WHEN /*q.mes = 3 AND*/ q.valor < 230.0 AND q.n_lojas = 1 THEN 'INATIVO'
                 ELSE '-' END as status
FROM (
    SELECT nome, MONTH(dt_compra) as mes, YEAR(dt_compra) as ano, 
           SUM(valor) as valor, COUNT(DISTINCT loja_cnpj)) as n_lojas
    FROM tabela
    GROUP BY nome, MONTH(dt_compra), YEAR(dt_compra)) as q
ORDER BY q.nome, q.ano, q.mes