Need Help to create a Custom Report - Time worked last 7 days but only counting a Custom Field

[Patrick_Allers]

Hi there,

I need help in regards of creating a new custom report.
I already have a Report which shows how much time has been "worked" put in on Tickets with custom Fields. In my case we have a Custom Field "Category" which we categorize the Tickets in, for example: VPN, Office365, User, Hardware etc.

But this report only shows the total Time worked which has been put in to these categories but I want a second one to only consider the last 7 days and if you're really good another one for "this month".

I can post my current Report in here maybe it's easy for someone to just add a couple things and it's done, I've tried it but it always gave me Error's in the Report.

_______________________________________________________________________________________
My Report:

Select Distinct Top 1000000 htblticketcustomfield.data As Category,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))) + ' minutes' As TimeWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblticketcustomfield.fieldid =
htblcustomfields.fieldid
Inner Join (Select Top 1000000 htblticketcustomfield.data,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Inner Join htblnotes On
htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid =
htblhistory.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblhistory.ticketstateid
Inner Join htblticket On
htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid =
htblhistory.typeid
Where htblticketstates.statename = 'Closed' And htblticketstates.statename =
'Closed' And htblticket.spam <> 'True' And htblhistorytypes.name
In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And
htblcustomfields.name Like '%Category%'
Group By htblticketcustomfield.data
Having Max(htblhistory.date) > GetDate() - 120) As Custom1 On
Custom1.data = htblticketcustomfield.data