SQLTeam.com | Weblogs | Forums

Finding out how Many Consecutive Months a person usage has gone over based on 6 month period


#1

I have a report which generates the client trends for the past 6 month. I have been able to get the report to display the correct information except for one column which is number of consecutive months exceed.

here is a sample of what the report should look like based on the report being generated using today's date and with a threshold of 20 which the client enters in before generating, which in this case it would highlight all the user that have gone over 20 minutes for the past 6 months.

REPORT

Name   Department   Number   June    May  April  March February January Exceed
John    CA          2122321   25     35     45     19     23      35     2
Sue     DA          2315445   25     15     10     24     26      30     0
Max     QA          2125236   40     11     10     15     13      14     0      
Pat     DA          2125896   25     31     33     19     25      16     3

Report is based on the past 6 months once again starting from the month that it is generated so the above sample could have been generated anytime in June, also the client enters in there own threshold
As I mentioned I already have the report working using a SQL Store Proc, but just need a hand with getting the consecutive month exceed column. Any Help would be appreciated thanks.

The database table called Invoice has the has the following information.

Database Table

Name    Department Number  Period_Month  Period_Year  Inv_date   Minutes
John        CA     2122321       06          2015     2015/01/01    25
Sue         DA     2315445       06          2015     2015/01/01    25
Max         QA     2315445       06          2015     2015/01/01    40
Pat         DA     2315445       06          2015     2015/01/01    25
John        CA     2122321       05          2015     2015/02/01    35
Sue         DA     2315445       05          2015     2015/02/01    15
Max         QA     2315445       05          2015     2015/02/01    11
Pat         DA     2315445       05          2015     2015/02/01    31
John        CA     2122321       04          2015     2015/02/01    19
Sue         DA     2315445       04          2015     2015/02/01    10
Max         QA     2315445       04          2015     2015/02/01    10
Pat         DA     2315445       04          2015     2015/02/01    33

I guess all I'm asking is based on the the database structure is for a count of how many times a person minutes has gone over 20 minutes in the past 6 month consecutively (in a row). Based on the on the example table John would have a count of 2 and for Sue it would be 0 since she has never gone over consecutively.

Name     Count
John      2
Sue       0

Below I have added the Store Proc that I am using for now in the column Months Exceeded is only showing the value 1 if the user has gone over the threshold value which is not what I need but was added to have something there. Also if there is any Advice on anything I should change or Update please fell free to mention it, I would appreciate the advice. Thanks

    @ID_Category            int, 
    @ID_Service_Type        varchar(255), 
    @ID_Provider            varchar(255), 
    @ID_Service_Contract    varchar(255),
    @Account_No                varchar(255),
    @FromDate                varchar(10),
    @SearchBy                varchar(255),
    @SearchValue            varchar(255),
    @JobLevel varchar(10),
    @BussinessGroup VARCHAR(10),
    @Division VARCHAR(10),
    @Supervisory VARCHAR(10),
    @loaction VARCHAR(10),
    @UserNo VARCHAR(10),
    @Manager VARCHAR(10),
    @ManagerTitle VARCHAR(10),
    @ManagerManager VARCHAR(10),
    @MonthsExceed VARCHAR(10)
    
    
AS

 BEGIN



    set nocount on
        begin
            declare @sql as varchar(max)   
            declare @OrderBy as varchar(max)  
            declare @GroupBy as varchar(max)  
            DECLARE @Column AS VARCHAR(255)
            DECLARE @DateColumn AS VARCHAR(255)
            DECLARE @count AS INT
            DECLARE @tblColumn VARCHAR(255)
    --        select  @OrderBy = ' Order by tbl_iso_summary.[Total Chg]  DESC  '
            --select  @OrderBy = ''
            Select  @GroupBy=''
    

SET @Column = (CASE WHEN @SearchBy =1 THEN ',TIS.[Add Min Chg] as [Amount]'
                    WHEN @SearchBy =2  THEN ',TIS.[Minutes Total] as [Amount]'
                    WHEN @SearchBy =3  THEN ',TIS.[LD Chg] as [Amount]' 
                    WHEN @SearchBy =4  THEN ',TIS.[LD Min] as [Amount]'
                    WHEN @SearchBy =5  THEN ',TIS.[Roaming Chg] as [Amount]'
                    WHEN @SearchBy =6  THEN ',TIS.[Roaming Min] as [Amount]'
                    WHEN @SearchBy =7  THEN ',TIS.[Vol TP] as [Amount]'
                    WHEN @SearchBy =8  THEN ',TIS.[Util TP] as [Amount]'
                    WHEN @SearchBy =9  THEN ',TIS.[Browser Chg] as [Amount]'
                    WHEN @SearchBy =10  THEN ',TIS.[Vol Txt Msg] as [Amount]'
                    WHEN @SearchBy =11  THEN ',TIS.[Other Chg] as [Amount]'
                    WHEN @SearchBy =12  THEN ',TIS.[Monthly Plan Chg]  as [Amount]'
                    WHEN @SearchBy =13  THEN ',TIS.[Peak Min]  as [Amount]'
                    WHEN @SearchBy =14  THEN ',TIS.[Weekend Min] as [Amount]'
                    WHEN @SearchBy =15  THEN ',TIS.[Evening Min] as [Amount]'
                    WHEN @SearchBy =16  THEN ',TIS.[411 Calls Chg] as [Amount]'
                    WHEN @SearchBy =17  THEN ',TIS.[Data Roam Vol] as [Amount]'
                    WHEN @SearchBy =18  THEN ',TIS.[Data Roam Cost] as [Amount]'
                    END )
SET @DateColumn = STUFF((SELECT  ',' + (DATENAME(MONTH,[Invc Date]))   FROM dbo.tbl_iso_summary    WHERE  [Invc Date] > DATEADD(MONTH, -6, GETDATE()) GROUP BY [Invc Date] ORDER BY MAX([Invc Date]) DESC FOR XML PATH(''))  ,1,1,'')

SET @tblColumn = (SELECT REPLACE(@Column, 'as [Amount]',''))

SET @tblColumn = (SELECT REPLACE(@tblColumn,',',''))


Select @sql = 'SELECT * FROM  (
  SELECT DISTINCT
    E.First_Name + '' '' + Last_Name AS [Employee Name]
    ,'''' AS [Cost Centre]
    ,P.Provider_Name AS [Mobility Vendor]
    ,EAI.field_7 AS [Language]
    ,ST.Service_Description AS[Device Type]
    ,TIS.[Mobile Nbr] AS [Device Number]
'


IF @JobLevel > 0
    SELECT @sql = @sql + ',EAI.field_8 AS [Job level] '    
IF @BussinessGroup > 0
    SELECT @sql = @sql + ',SS.Subsidiary_Name AS [Business Group] '        
IF @Division  > 0
    SELECT @sql = @sql + ',DI.Division_Name AS [Division] '        

IF @Supervisory >0
    SELECT @sql =@sql + ',D.Department_Name AS [Supervisory Org] '

IF @loaction >0
    SELECT @sql =@sql + ', EAI.field_5 AS [Location] '

IF @UserNo > 0
 SELECT @sql = @sql + ', E.Employee_No as [User No]'

 IF @Manager > 0
 SELECT @sql = @sql + ', ( SELECT E2.First_Name + '' '' + E2.Last_Name
                            FROM dbo.Employee E2
                            WHERE E2.ID_Employee IN (
                                    SELECT REM.ID_Manager
                                    FROM dbo.Rel_Employee__Manager REM
                                    WHERE REM.ID_Employee = E.ID_Employee
                                    )
                        ) AS [Manager] '
IF @ManagerTitle > 0
SELECT @sql = @sql + ', (SELECT E5.Title
                            FROM dbo.Employee E5
                            WHERE E5.ID_Employee IN (
                                    SELECT REM4.ID_Manager
                                    FROM dbo.Rel_Employee__Manager REM4
                                    WHERE REM4.ID_Employee = E.ID_Employee
                                    )
                            ) AS [Manager Title] '
IF @ManagerManager > 0
SELECT @sql = @sql + ',(
                            SELECT E4.First_Name + '' '' + E4.Last_Name
                            FROM dbo.Employee E4
                            WHERE E4.ID_Employee IN (
                                    SELECT REM3.ID_Manager
                                    FROM dbo.Rel_Employee__Manager REM3
                                    WHERE REM3.ID_Employee IN (
                                            (
                                                SELECT E3.ID_Employee
                                                FROM dbo.Employee E3
                                                WHERE E3.ID_Employee IN (
                                                        SELECT REM2.ID_Manager
                                                        FROM dbo.Rel_Employee__Manager REM2
                                                        WHERE REM2.ID_Employee = E.ID_Employee
                                                        )
                                                )
                                            )
                                    )
                        ) AS [Manager Manager]'

IF @MonthsExceed > 0
SELECT @sql = @sql + ',( case when ' + @tblColumn + ' > ' + @SearchValue + ' Then 1  end ) AS [Months Exceeded] '


SELECT @sql = @sql + ', DATENAME(MONTH,[Invc Date]) AS Month_hidden '





SELECT @sql = @sql + @Column

Select @sql = @sql +'
FROM dbo.tbl_iso_summary  TIS
LEFT JOIN dbo.Service_Inventory SI
ON TIS.[Mobile Nbr] = SI.Primary_Number
and TIS.ID_Provider_Primary = SI.ID_Provider_Primary
LEFT JOIN dbo.Rel_Service_Inventory__Employee RSIE
ON SI.ID_Service_Inventory = RSIE.ID_Service_Inventory
AND RSIE.Primary_Indicator =1
LEFT JOIN dbo.Employee E
ON E.ID_Employee = RSIE.ID_Employee
LEFT JOIN dbo.Provider P
ON SI.ID_Provider_Primary = P.ID_Provider
LEFT JOIN dbo.Department D
ON D.ID_Department = SI.ID_Department
LEFT JOIN dbo.Employee_Additional_Info EAI
ON EAI.ID_Employee = E.ID_Employee
LEFT JOIN dbo.Service_Type ST
ON ST.ID_Service_Type = SI.ID_Service_Type
LEFT JOIN dbo.Subsidiary SS
ON SS.ID_Subsidiary = D.ID_Subsidiary
LEFT JOIN dbo.Division DI
ON DI.ID_Division = D.ID_Division
'
select @sql = @sql +  ' Where 1=1  AND TIS.[Invc Date] > DATEADD(MONTH, -6, GETDATE())'

if @ID_Category > 0 
    select @sql = @sql + '  and ST.ID_Category in (' + convert(varchar,@ID_Category) + ' )'
                                    
if left(@ID_Service_Type, 1) != '0'
    select @sql = @sql + '  and SI.ID_Service_type in (' + @ID_Service_Type + ')'
                                
if left(@ID_Provider, 1) <> '0'
    select @sql = @sql + '  and TIS.id_provider_primary in (' + @ID_Provider + ')'


 
    
                
Select @sql = @sql + ' )DATA '


SELECT @sql = @sql + 'Pivot(
                            max(amount)
                            for Month_hidden in ( ' 
        
SELECT @sql = @sql +  @DateColumn 

SELECT @sql = @sql + ' ) ) Months '

                    exec(@sql)
                --    print @sql
            end
    set nocount off
END

#2

--Edit, added some additional test data and realised solution wasn't working


#3

Think this could be refined a little, but I think it does the job. Uses ROW_NUMBER to help find consecutive months where the threshold has been exceeded

Added some additional test data to represent a gap in consecutive months over the threshold

WITH Chuff(Name,Department,Number,Period_Month,Period_Year,Minutes,RN,ThresholdGrouping,OverThresholdCount)
AS
(
SELECT	A.Name,
		A.Department,
		A.Number,
		A.Period_Month,
		A.Period_Year,
		A.Minutes,
		ROW_NUMBER() over (PARTITION BY A.Name,A.Department,A.Number ORDER BY A.Period_Month),
		ROW_NUMBER() over (PARTITION BY A.Name,A.Department,A.Number ORDER BY A.Period_Month) - CASE WHEN A.Minutes > 20 THEN ROW_NUMBER() OVER (PARTITION BY A.Name,A.Department,A.Number,CASE WHEN A.Minutes > 20 THEN 1 ELSE NULL END ORDER BY A.Period_Month ASC) ELSE NULL END,
		COUNT(CASE WHEN A.Minutes > 20 THEN 1 ELSE NULL END) OVER (PARTITION BY A.Name,A.Department,A.Number)
FROM
	(
		VALUES	('John','CA',2122321,06,2015,CAST('2015/01/01' AS DATETIME),25),
				('Sue','DA',2315445,06,2015,'2015/01/01',25),
				('Max','QA',2315445,06,2015,'2015/01/01',40),
				('Pat','DA',2315445,06,2015,'2015/01/01',25),
				('John','CA',2122321,05,2015,'2015/02/01',35),
				('Sue','DA',2315445,05,2015,'2015/02/01',15),
				('Max','QA',2315445,05,2015,'2015/02/01',11),
				('Pat','DA',2315445,05,2015,'2015/02/01',31),
				('John','CA',2122321,04,2015,'2015/02/01',19),
				('Sue','DA',2315445,04,2015,'2015/02/01',10),
				('Max','QA',2315445,04,2015,'2015/02/01',10),
				('Pat','DA',2315445,04,2015,'2015/02/01',33),
				('John','CA',2122321,07,2015,'2015/02/01',19),
				('John','CA',2122321,08,2015,'2015/02/01',21)
	) AS A(Name,Department,Number,Period_Month,Period_Year,Inv_date,Minutes)
)
,Chuff2(Name,Department,Number,Period_Month,Minutes,OverThresholdCount,ConThreshold)
AS
(

SELECT	C.Name,
		C.Department,
		C.Number,
		C.Period_Month,
		C.Minutes,
		C.OverThresholdCount,
		CASE WHEN C.ThresholdGrouping IS NULL THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY C.Name,C.Department,C.Number,C.ThresholdGrouping ORDER BY C.Period_Month) END
FROM	Chuff AS C
)
SELECT	C.Name,
		C.Department,
		C.Number,
		April = MAX(CASE WHEN C.Period_Month = 4 THEN C.Minutes END),
		May = MAX(CASE WHEN C.Period_Month = 5 THEN C.Minutes END),
		June = MAX(CASE WHEN C.Period_Month = 6 THEN C.Minutes END),
		ExceedCount = MAX(C.OverThresholdCount),
		ConseqCount = MAX(ConThreshold)
FROM	Chuff2 AS C
GROUP	BY C.Name,
		C.Department,
		C.Number;

#4

Hey Dohsan thanks for the reply, I took a look at your post and it's what I'm looking for. My only issue is based on the result from your post Max and Sue should have 0 for the ConseqCount since they both did not go over in consecutive month but just want over the current month.

Result

 Name   Department    Number    April    May    June    ExceedCount    ConseqCount
 John      CA         2122321    19      35      25         3               2
 Max       QA         2315445    10      11      40         1               1
 Pat       DA         2315445    33      31      25         3               3
 Sue       DA         2315445    10      15      25         1               1

I'm going to try this in my store proc while tweaking were needing to get the result needed. Once again thanks a lot. I'll let u know how it goes.


#5

Essentially its just putting row numbers against groups/islands of the data that have exceeded the threshold. You could always just change the value from 1 to 0, something like:

ConseqCount = CASE WHEN MAX(ConThreshold) = 1 THEN 0 ELSE MAX(ConThreshold) END

Glad it has got you in the right direction, am sure it can be refined a fair bit to make it bit more efficient.