Hi,

I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months (all from the most recent year/month) in following format:

                                       3 months     6 months     9 months     12  months

__________________________________________________________

number of distinct users        x                  y                  z                      w

Any ideas?

Tnx      

CREATE TABLE [A] (
    [Year] INTEGER NULL,
    [Month] INTEGER NULL,
    [User] VARCHAR(255) NULL);

GO

INSERT INTO A([Year],[Month],[User]) 
VALUES
(2014,8,'Giacomo'),
(2014,9,'Leila'),
(2014,10,'Melodie'),
(2014,11,'Cora'),
(2014,12,'Amanda'),
(2015,1,'Stone'),
(2015,2,'Kaitlin'),
(2015,3,'Gloria'),
(2015,4,'Petra'),
(2015,5,'Kirsten'),
(2015,6,'Cally'),
(2015,7,'Alexander'),
(2015,8,'Ciaran');


;WITH Cte AS
(
SELECT CAST(LTRIM(STR(Year))+'-'+LTRIM(STR(Month))+'-01' AS DATE) DT, [User] FROM A
)
,Cte2 AS
(
SELECT DT,[USER],(DENSE_RANK() OVER(ORDER BY DT)-1)/3 GrpBy FROM Cte WHERE DT>= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-12,0)
)
,Cte3 AS
(
SELECT GrpBy,COUNT([User]) UserCnt FROM CTE2 GROUP BY GrpBy
)
,Cte4 AS
(
SELECT GrpBy,UserCnt2
 FROM CTE3 c3
CROSS APPLY
(
SELECT SUM(UserCnt) UserCnt2 FROM Cte3 WHERE GrpBy<=c3.GrpBy
) c  
)
SELECT [0] AS [3 Months],[1] AS [6 Months],[2] AS [9 Months],[3] AS [12 Months] FROM CTE4
PIVOT
(
MAX(UserCnt2) FOR GrpBy IN([0],[1],[2],[3])
)p

--DROP TABLE A 

 

• Proposed as answer by Naomi NModerator 16 hours 5 minutes ago

Hi,

I am using Eric DDL with some more data (since we need to check the distinct work OK)

-- DDL+DML

CREATE TABLE [A] (
    [Year] INTEGER NULL,
    [Month] INTEGER NULL,
    [User] VARCHAR(255) NULL);

GO

truncate table A
INSERT INTO A([Year],[Month],[User]) 
VALUES
(2014,8,'Giacomo'),
(2014,9,'Leila'),
(2014,10,'Melodie'),
(2014,11,'Cora'),
(2014,12,'Amanda'),
(2015,1,'Stone'),
(2015,2,'Kaitlin'),
(2015,3,'Gloria'),
(2015,4,'Petra'),
(2015,5,'Kirsten'),
(2015,6,'Cally'),
(2015,7,'Alexander'),
(2015,8,'Ciaran'),

(2014,2,'Amanda'),
(2014,10,'Melodie'),
(2015,7,'Petra'),
(2015,7,'Ciaran');

select * from A

please try this:

declare @CurrentYear int = datepart(YEAR,GETDATE())
declare @CurrentMonth int = datepart(MONTH,GETDATE())
-- select @CurrentMonth,@CurrentYear
select 
	'number of distinct users', 
	[3 Months] = (
	select count(distinct [user])from A 
	where 
		([Year] = @CurrentYear and [Month] > @CurrentMonth-3) -- data from this year
		or 
		-- if @CurrentMonth < 3 then we need data from previous year as well:
		-- If the "@CurrentMonth > 3" then this part "12 + (@CurrentMonth - 3)" is more then 12 and will not return any row
		-- but If the "@CurrentMonth < 3" then this part "12 + (@CurrentMonth - 3)" is less then 12 and will return the missing data from previous year
		([Year] = @CurrentYear - 1 and [Month] > 12 + (@CurrentMonth - 3)) -- data from previous year
	), 
	-- same with 9 and 12
	[6 Months] = (
	select count(distinct [user]) from A 
	where 
		([Year] = @CurrentYear and [Month] > @CurrentMonth-6) -- data from this year
		or 
		([Year] = @CurrentYear - 1 and [Month] > 12 + (@CurrentMonth - 6)) -- data from previous year
	), 
	[9 Months] = (
	select count(distinct [user]) from A 
	where 
		([Year] = @CurrentYear and [Month] > @CurrentMonth-9) -- data from this year
		or 
		([Year] = @CurrentYear - 1 and [Month] > 12 + (@CurrentMonth - 9)) -- data from previous year
	), 
	[12 Months] = (
	select count(distinct [user]) from A 
	where 
		([Year] = @CurrentYear and [Month] > @CurrentMonth-12) -- data from this year
		or 
		([Year] = @CurrentYear - 1 and [Month] > 12 + (@CurrentMonth - 12)) -- data from previous year
	)

* My first solution do not using any calculation on the original columns [Year],[Month] so if you have (and you probably should have) index on these column the query will probably use the index (I am not saying it will use the index if there are)

 ** we have no information regarding your real DDL, therefore, I can not say which query will be best or if indexes are relevant in your case, or if this query will use indexes in your case :-(

Another solution can be using CONVERT, which In some cases you can just use this as well, but check which is best in your specific case!

declare @FirstDayOfTheMonth DATE = DATEADD(DAY,1-(DATEPART(DAY,GETDATE())),GETDATE())
SELECT 
	'number of distinct users',
	[3 Months] = (
		select COUNT(DISTINCT [User])
		from A
		where DATEFROMPARTS([Year],[Month],01) > DATEADD(MONTH,-3,@FirstDayOfTheMonth)
	),
	[6 Months] = (
		select COUNT(DISTINCT [User])
		from A
		where DATEFROMPARTS([Year],[Month],01) > DATEADD(MONTH,-6,@FirstDayOfTheMonth)
	),
	[9 Months] = (
		select COUNT(DISTINCT [User])
		from A
		where DATEFROMPARTS([Year],[Month],01) > DATEADD(MONTH,-9,@FirstDayOfTheMonth)
	),
	[12 Months] = (
		select COUNT(DISTINCT [User])
		from A
		where DATEFROMPARTS([Year],[Month],01) > DATEADD(MONTH,-12,@FirstDayOfTheMonth)
	)
GO

Hi RyanAB,

This is not always a good idea :-)

by converting/calculating the column before you use it for the filter, you might eliminate the indexes

if you have indexes on these columns (AND YOU SHOULD HAVE INDEXES probably),then by using filter on the calculated value, you must first calculate the value, the SQL Server will not have option to use the indexes since it must first scan all and do the calculation and only on the result of the calculation it can use the filter (it will need at least index scan instead of index seek pro

Try

declare @a table (
    [Year] INTEGER not null,
    [Month] INTEGER not NULL,
    [User] VARCHAR(255) not NULL);



INSERT INTO @a([Year],[Month],[User]) 
VALUES
(2014,8,'Giacomo'),
(2014,9,'Leila'),
(2014,9,'Jim'),
(2014,10,'Melodie'),
(2014,11,'Cora'),
(2014,12,'Amanda'),
(2014,12,'Melodie'),
(2015,1,'Stone'),
(2015,2,'Kaitlin'),
(2015,3,'Gloria'),
(2015,4,'Petra'),
(2015,5,'Kirsten'),
(2015,5,'Gloria'),
(2015,6,'Cally'),
(2015,6,'Jim'),
(2015,7,'Alexander'),
(2015,8,'Ciaran');

;with cte as (select [User] as UserId, DATEFROMPARTS([Year],[Month],1) as dt from @a)

select 
count(distinct (case when dt between dateadd(month,-3, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP then UserId end)) as last3MonthsCount,
count(distinct (case when dt between dateadd(month,-6, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP then UserId end)) as last6MonthsCount,
count(distinct (case when dt between dateadd(month,-9, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP then UserId end)) as last9MonthsCount
from cte 

Hi,

Please check the execution plan comparing

* the first query is my second solution and the second query is Naomi's.
Tested on SQL 2014

** I added to Naomi's solution the last colum to get 12 month as the OP asked

"count(distinct (case when dt between dateadd(month,-12, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP then UserId end)) as last12MonthsCount "

1. without indexes:

2. with index 

CREATE NONCLUSTERED INDEX A_User_Year_Month_Inx on A([user],[Year],[Month])

* you should check other DDL as well.

** You should check the IO as well!
Sometimes the execution plan percentage misleading.

Select distinct Sum(Case when dtMonth=0 Then  1   End) Over() [3MONTH],
Sum(Case when dtMonth IN (0,1)  Then  1   End) Over() [6MONTH],
Sum(Case when dtMonth IN (0,1,2)  Then 1   End) Over() [9MONTH], 
Sum(Case when dtMonth IN (0,1,2,3)  Then 1   End) Over() [12MONTH] 
 from (
select  distinct [User], datediff(month,datefromparts([Year],[Month],1),getdate())/3 dtMonth 
from [A]
) t
 

Hi RyanAB,

This is not always a good idea :-)

by converting/calculating the column before you use it for the filter, you might eliminate the indexes

if you have indexes on these columns (AND YOU SHOULD HAVE INDEXES probably),then by using filter on the calculated value, you must first calculate the value, the SQL Server will not have option to use the indexes since it must first scan all and do the calculation and only on the result of the calculation it can use the filter (it will need at least index scan instead of index seek pro

I agree with you that for the long term you might need to think about a better solution (maybe even using different DDL) :-)

Unfortunately we don't have the real DDL here since the OP did not post it :-(
so we are just talking theoretically, and I had to mentioned that this solution is not always a good idea :-)

* I posted two full solutions code anyway which the first one do not use this approach

** "nicer" is not an issue here i think.
     I care about performance first :-)

Anyway, WELL DONE RyanAB
It is a solution to the question (we do not have to post full code each time), and till we will get DDL we can only talk about performance in

Good day Jingyang Li

Yes, your query is faster but is not returning the same result :-)
Your solution return the same result as Eric's solution.
This is why I added several more rows from the start (to create duplicate rows).

Assuming that I understood the issue, then your code return duplicate rows.
Please compare the results in your code with the results in my and in Naomi codes.

This is what I got (and Naomi as well):

3 Months    6 Months    9 Months    12 Months
----------- ----------- ----------- -----------
4           6           9           12

This is what your code return on the same data (my DML as above):

This s all the information i used ordered by date

we supposed to get these rows for the last 6 month (8 rows return in the last 6 month):

Gloria     2015-03-01
Petra      2015-04-01
Kirsten    2015-05-01
Cally      2015-06-01
Ciaran     2015-07-01
Alexander  2015-07-01
Petra      2015-07-01
Ciaran     2015-08-01

but the user Ciaran has duplicate rows and the user Petra as well, therefore the last result should be 6 and not 7 for the last six month. If I notice correctly then your query only remove duplicate in the first 3 month therefore you cleared the duplication of the user Ciaran but stayed with 2 rows for the user Petra (therefore you got 8-1 = 7 instead 8-2 = 6)

* I hope I understood the OP question :-)
And that this is

I don't think there is a plural form of the 'code' word. I can double check, but I am pretty sure there isn't one.

Check this page (the first google result)

http://english.stackexchange.com/questions/20455/is-it-wrong-to-use-the-word-codes-in-a-programming-conte

I don't think there is a plural form of the 'code' word. I can double check, but I am pretty sure there isn't one.

Check this page (the first google result)

http://english.stackexchange.com/questions/20455/is-it-wrong-to-use-the-word-codes-in-a-programming-conte

Check the code. Don't use 'Codes' word at all, it's incorrect. I also added a link to the discussion about the usage.

I think in Hebrew there are lots of words having only plural or only singular form as well. Say, 'maim', 'shamaim' - only plural, right? I have to admit that my Hebrew is very weak, almost non-existent :(

Each language has its rules and exceptions.

LOL :-)
I need to ask my father regarding the Hebrew (he used to teach Hebrew grammar and Literature).
I am not sure regarding these Hebrew examples as well :-)

Anyway, thanks for the fix, +1
so from now on "CODE" instead of "CODES" :-)

Thanks Naomi