Início / dba / Perguntas / 278830
Accepted
TheDemonLord
Asked: 2020-10-29 13:31:27 +0800 CST

Contando registros agrupados, usando data de início e data de término com NULLs

  • 772

Aqui está o meu problema, Source DB é Azure MSSQL - eu tenho uma tabela que lista todas as assinaturas vendidas. As colunas relevantes são:

Número da conta, grupo de assinatura, data de compra, data de cancelamento

Data de cancelamento contém valores NULL (para assinaturas que ainda não foram canceladas). Uma conta pode ter várias assinaturas, em vários grupos de assinaturas.

Os dados de exemplo são:

Número da conta | Grupo de assinatura | Data de compra | Data de cancelamento |

1 | A | 04/02/2013 | 04/02/2014 |
1 | A | 04/02/2013 | NULO |
1 | B | 04/02/2013 | NULO |
2 | C | 07/05/2018 | 08/05/2020 |
3 | C | 20/3/2020 | NULO |
3 | C | 08/04/2020 | NULO |

etc.

Estou procurando contar o número de grupos de assinatura para cada cliente e para cada mês - para que a saída seja algo assim:

Cliente | Contagem de A | Contagem de B | Contagem de C | Mês/Ano |
1 | 1 | 1 | 0 | 10/2020 |
3 | 0 | 0 | 2 | 10/2020 |
1 | 1 | 1 | 0 | 09/2020 |
3 | 0 | 0 | 2 | 09/2020 |
1 | 1 | 1 | 0 | 08/2020 |
2 | 0 | 0 | 1 | 08/2020 |
3 | 0 | 0 | 2 | 08/2020 |

Eu posso escrever a consulta para um determinado mês:

selecione t.[número da conta],
COUNT(CASE WHEN t.[Grupo de assinatura] = A então 1 ELSE NULL END) como "Contagem de A",
COUNT(CASE WHEN t.[Grupo de assinatura] = B então 1 ELSE NULL END) ) como "Contagem de B",
COUNT(CASE WHEN t.[Grupo de assinatura] = C então 1 ELSE NULL END) como "Contagem de C",
'09/2020' como 'Mês/Ano'
da tabela t
onde (t .[Data da compra] <= '30/09/2020' e (t.[Data de cancelamento] é nula ou t.[Data de cancelamento] > '10/01/2020'))
Agrupar por t.[Número da conta], [Mês ano]

No entanto, espero que alguém possa me ajudar a modificar o acima para que ele seja executado por todos os meses (com base no MIN da data de compra e no MAX na data do cancelamento)

sql-server azure-sql-database

1 respostas

  1. Best Answer

    Acabei fazendo isso usando um Stored Procedure, criando uma Temp Table:

    DECLARE @StartDate  DATE,
        @EndDate    DATE;

SELECT   @StartDate = (select min([purchase date]) from Table where [Purchase Date] != '1970-01-01')      
         ,@EndDate  = (select max([Cancellation date]) from Table);


;with months (date)
AS
(
    SELECT @startdate
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @enddate
)
select     IDENTITY(int, 1, 1)  as ID,
           [LastDayOfMonth]  = EOMONTH(date),
           [FirstDayOfNextMonth] = DATEADD(DAY, 1, EOMONTH(date)),
           [date] = date
into #tempdatetable
from months
option (maxrecursion 0)

    Em seguida, fazendo um Loop através dessa Tabela e preenchendo uma tabela de saída:

    Declare @Id int
Declare @ldom date
declare @fdonm date
declare @my date

While (Select Count(*) From #tempdatetable) > 0
Begin

    Select Top 1 @Id = Id,
    @ldom = LastDayOfMonth,
    @fdonm = FirstDayOfNextMonth,
    @my = [date]
    From #tempdatetable

insert into MyOutputTable (Customer,
[Count of A],
[Count of B],
[Count of C],
[Month/Year])
select T.[Customer],
COUNT(CASE WHEN T.[Subscription group] = 1 then 1 ELSE NULL END) as "Count of A",
COUNT(CASE WHEN T.[Subscription group] = 2 then 1 ELSE NULL END) as "Count of B",
COUNT(CASE WHEN T.[Subscription group] = 3 then 1 ELSE NULL END) as "Count of C",
@my as 'Month/Year'
from Table T
where ((T.[Purchase Date] <= @ldom or T.[Purchase Date] is null) and (T.[Cancellation date] is null or T.[Cancellation date] > @fdonm) )
Group by T.[Customer]

Delete #tempdatetable Where Id = @Id

End

drop table #tempdatetable
    • 0

relate perguntas