Determine the first occurrence of a particular customer visiting the store in a particular month

91 views Asked by At

I need to determine the counts breakdown to per month (and year) of customers [alias'ed as Patient_ID] which made their first visit to a store. The date times of store visits are stored in the [MDT Review Date] column of the table.

Customers can come to the store multiple times throughout the year and increase the total count-> but what I require is ONLY the first time a customer visited.

E.g. Tom Bombadil visited the store once in January 2019, so count increased to 1, then again 4 times in March, so count should be 1 for the month of March and 0 for febraury and 1 for January, then again 4 times in October, then again 2 times in December.
I require that Tom Bombadil should be counted one and only once for a particular month, his first occurrence which was per month The output should be like :

rn1 YEAR    Month_Number    Month       Total_Count
1   2010    6               June        2
1   2010    7               July        1
1   2010    8               August      5
1   2010    10              October     5
1   2010    11              November    3
1   2011    1               January     4
1   2011    2               February    6
1   2011    4               April       7
1   2011    5               May         4
1   2011    6               June        10
1   2011    7               July        10
1   2011    8               August      14
1   2011    9               September   4
1   2011    10              October     8
1   2011    11              November    11
1   2011    12              December    11
1   2012    1               January     8
1   2012    2               February    21​

Please refer to my query. What I have attempts to use the windowing function COUNT to count the store visits per month. Then the ROW_NUMBER function attempts to assign a unique number to each visit. What am I doing wrong?

select 
        *
    from
        (select distinct 
             row_number() over (partition by p.Patient_ID, p.PAT_Forename1, p.PAT_Surname
                                order by PAT_Forename1, p.Patient_ID, PAT_Surname) AS rn1,
             datepart(year, [DATE_COLUMN]) as YEAR,
             datepart(month, [DATE_COLUMN]) as Month_Number,
             datename(month,[DATE_COLUMN]) as Month,
             count(p.Patient_ID) over (partition by datepart(year,[DATE_COLUMN]),
             datename(month, [DATE_COLUMN])) as Total_Count
         from 
             Tablename m
         inner join 
            TableName p on m.PK_ID = p.PK_ID
        
         ) as temp
    where 
        rn1 = 1​
0

There are 0 answers