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