Null values in table that share a same data in another column-CodePudding

I have the following table:

Since I have a second and third purchase columns, I need the table to have the same number of rows and since the month is shared between values in the second and third. I need to get rid of the nulls.

As the table month values are shared in second and third. And the year stays the same throughout. Values should appear in a single row as they share the other columns data such as month and year

SELECT 100 * t.num_clients / ((SELECT COUNT(*) FROM gen.tbCliente) (0.0)) AS third_purchase_rate, t.month, t.year
FROM (
  SELECT COUNT(*) AS num_clients, DATEPART(month, s.Fecha) AS month, DATEPART(year, s.Fecha) AS year
  FROM (
    SELECT c.IdCliente, COUNT(s.IdClienteInt) AS num_purchases
    FROM gen.tbCliente c
    INNER JOIN facVentas s ON cast(c.IdCliente as varchar) = cast(s.IdClienteInt as varchar)
    GROUP BY c.IdCliente
    HAVING COUNT(s.IdClienteInt) >= 3
  ) t
  INNER JOIN facVentas s ON t.IdCliente = s.IdClienteInt
  WHERE s.Fecha >= DATEADD(month, -6, GETDATE()) AND t.num_purchases >= 3 AND EXISTS (
    SELECT 1
    FROM facVentas s2
    WHERE s2.IdClienteInt = s.IdClienteInt AND s2.Fecha < s.Fecha
    GROUP BY s2.IdClienteInt
    HAVING COUNT(s2.IdClienteInt) = 2
  )
  GROUP BY DATEPART(month, s.Fecha), DATEPART(year, s.Fecha)
) t

SELECT 100 * t.num_clients / ((SELECT COUNT(*) FROM gen.tbCliente)   (0.0)) AS second_purchase_rate, t.month, t.year
FROM (
  SELECT COUNT(*) AS num_clients, DATEPART(month, s.Fecha) AS month, DATEPART(year, s.Fecha) AS year
  FROM (
    SELECT c.IdCliente, COUNT(s.IdClienteInt) AS num_purchases
    FROM gen.tbCliente c
    INNER JOIN facVentas s ON cast(c.IdCliente as varchar) = cast(s.IdClienteInt as varchar)
    GROUP BY c.IdCliente
    HAVING COUNT(s.IdClienteInt) >= 2
  ) t
  INNER JOIN facVentas s ON t.IdCliente = s.IdClienteInt
  WHERE s.Fecha >= DATEADD(month, -6, GETDATE()) AND t.num_purchases >= 2 AND EXISTS (
    SELECT 1
    FROM facVentas s2
    WHERE s2.IdClienteInt = s.IdClienteInt AND s2.Fecha > DATEADD(day, -30, s.Fecha)
    GROUP BY s2.IdClienteInt
    HAVING COUNT(s2.idClienteInt) = 1
  )
  GROUP BY DATEPART(month, s.Fecha), DATEPART(year, s.Fecha)
) t

Both of those need to be inserted to the table without creating the extra rows

The table should this way:

SecondPurchase, ThirdPurchase, Month,Year
 *number*     ,  *number*     , 7   , 2022
 *Number*     ,  *number*     , 8   , 2022
 *Number*     ,  *Number*     , 9   , 2022

etc..

Without this null values that I made an error when inserting

CodePudding user response：

Seems like a simple aggregation would do the trick

Select second_purchase_rate = max(second_purchase_rate)
      ,third_purchase_rate  = max(third_purchase_rate)
      ,[month]
      ,[year]
 Into  SomeNewTable  -- Optional
 From  YourTable
 Group By [year],[month]
 Order By [year],[month]

CodePudding user response：

Assume the table name is purchase_detail:

With cte as ( Select first_purchase, month, year from  purshase_detail where first_purchase is not null), cte2 as ( Select second_purchase, month, year from  purshase_detail where first_purchase is not null) Select c.first_purchase,c2.second_purchase,c.month.c.year from cte c inner join cte2 c2 on c.month=c2.month;