Query User Count by Quarter with Pivot

1

I have a Users table that, for simplicity sake, looks like this...

UserID
ClientID
DateAdded

I need to get the user count by quarter based on the DateAdded field and presented with the clients listed in column1 and the counts by quarter in columns 2-n (Q1 2019, Q2 2019, etc.)

Any help would be much appreciated.

2

The tricky thing is getting the custom column names. I find it's usually easier to rename temp table columns than to try to generate columns with dynamic names, because that forces the SQL itself to be dynamic which makes it much more difficult and time consuming to modify the code later.

If you'll provide some directly usable sample data, I can verify the code and adjust it if needed:

DECLARE @DateAddedEnd date
DECLARE @DateAddedStart date
SET @DateAddedStart = '20190101'
SET @DateAddedEnd = '20211231'

SELECT 
    ClientID, 
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 0 THEN UserID END) AS Q1,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 1 THEN UserID END) AS Q2,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 2 THEN UserID END) AS Q3,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 3 THEN UserID END) AS Q4,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 4 THEN UserID END) AS Q5,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 5 THEN UserID END) AS Q6,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 6 THEN UserID END) AS Q7,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 7 THEN UserID END) AS Q8,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 8 THEN UserID END) AS Q9,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) = 9 THEN UserID END) AS Q10,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =10 THEN UserID END) AS Q11,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =11 THEN UserID END) AS Q12,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =12 THEN UserID END) AS Q13,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =13 THEN UserID END) AS Q14,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =14 THEN UserID END) AS Q15,
    COUNT(DISTINCT CASE WHEN DATEDIFF(QUARTER, @DateAddedStart, DateAdded) =15 THEN UserID END) AS Q16
INTO #qtr_totals
FROM dbo.your_table_name
WHERE
    DateAdded >= @DateAddedStart AND
    DateAdded < DATEADD(DAY, 1, @DateAddedEnd)
GROUP BY 
    ClientID

DECLARE @ColumnCounter tinyint
DECLARE @ColumnCounterMax tinyint
DECLARE @ColumnNameNew varchar(50)
DECLARE @ColumnNameOld varchar(50)
SET @ColumnCounter = 1
SET @ColumnCounterMax = DATEDIFF(QUARTER, @DateAddedStart, @DateAddedEnd) + 1
WHILE @ColumnCounter <= @ColumnCounterMax
BEGIN
    SET @ColumnNameOld = 'tempdb.dbo.#qtr_totals.Q' + CAST(@ColumnCounter AS varchar(3))
    SET @ColumnNameNew = 'Q' + CAST(DATEPART(QUARTER, @DateAddedStart + @ColumnCounter - 1) AS varchar(1)) +
        ' ' + CAST(YEAR(@DateAddedStart + @ColumnCounter - 1) AS varchar(4))
    EXEC sys.sp_rename @ColumnNameOld, @ColumnNameNew, 'COLUMN'
END /*IF*/

SELECT *
FROM #qtr_totals
ORDER BY ClientID
1 Like
3

maybe another way
image

use sqlteam
go


create table dbo.boomshakala(UserID int,
ClientID int,
DateAdded date)

declare @columns varchar(max), @query varchar(max)

insert into boomshakala
select distinct object_id, column_id, dateadd(m, column_id * -1, getdate()) 
  from sys.all_columns 
  where object_id > 0

create table ClientSummation(ActitivyCount int, 
ClientID int, QuarterTag Varchar(100))

insert into ClientSummation
select Count(1) ActitivyCount,  
       ClientID, 
	   CONCAT('Q', CAST(DATEPART(QUARTER, DateAdded) as varchar(10)), ' ', CONVERT(char(4), DateAdded,121) )
from boomshakala
group by ClientID, 
CONCAT('Q', CAST(DATEPART(QUARTER, DateAdded) as varchar(10)), ' ', CONVERT(char(4), DateAdded,121) )

--verify
--1	71	Q1 2015
--2	47	Q1 2017
--select * From boomshakala where ClientID = 47

declare @columnDates varchar(max)

SELECT @columnDates =  COALESCE(@columnDates + ', ','') + QUOTENAME(QuarterTag)
FROM
(
	select distinct QuarterTag
	from ClientSummation	 
) AS B
ORDER BY B.QuarterTag

SET @query ='
select ClientID, ' + @columnDates + ' from
(
Select ClientID, QuarterTag, ActitivyCount
	 From ClientSummation
) p
PIVOT 
(
SUM(ActitivyCount)
FOR QuarterTag in (' + @columnDates + ')
) a '

--select @query
exec( @query);


drop table boomshakala
drop table ClientSummation
4

Thank you both. These are great!