Dashboard - 'First of the Week' Relative Date Searches

Thomas_Hathaway

The dashboards have been a great addition to the Cireson tool set. One issue that I keep running into is building a weekly report query without it being a continuous count. As it stands now there are three operators that allow first of the month,quarter & year but week is missing. Change to a "days ago" operator and now the results turn into a running number and really isn't very helpful to supervisors and managers who want to know running stats like created vs closed for the week in the Dashboard. Changing over to a sql query widget and functions like count don't work, unless I'm missing something.

What I think would be helpful is to expand those operators to include "First Day of the Week". I've built such a query already but really really hate the finagling of the query itself to enable grouping or not able to use the count function in a plain jane ServiceManagement Query via the widget. I just want a number.  


* that pictures much larger than I thought it was going to be. 

Example Query:

select count(WorkItemId) as 'ClosedCount'  

from WorkItem

where 

(TierId = '6CAB9C40-D82F-952D-1F90-5742ECBFE69A' --CTS IR's

or 

TierId = '1B55C7DA-2BCA-78B8-72BE-6741F9CDA875' --CTS SR's

or 

TierId = 'C956674B-B49A-B145-A7E3-783E41471E98') --CTS Ma's

and

(StatusId = '2B8830B6-59F0-F574-9C2A-F4B4682F1681' --IR Resolved

or

StatusId = 'B026FDFD-89BD-490B-E1FD-A599C78D440F') -- SR completed

and

Created >= DATEADD(ww, DATEDIFF(ww,-1,GETDATE()), -1) -- how I get the start of the week

Gerhard_Goossens

I did something like this...

(Today)

AND DATEDIFF(dd, ResolvedDate, GETDATE()) = 0

(Yesterday)

AND DATEDIFF(dd, ResolvedDate, GETDATE()) = 1

(This Week)

AND ResolvedDate >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

(Last Week)

AND (ResolvedDate >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0) --first day of last week

AND ResolvedDate < DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)) --first day of this week

(This Month)

AND DATEPART(yyyy, ResolvedDate) = @Year

AND DATEPART(mm, ResolvedDate) = @Month

AND DATEPART(dd, ResolvedDate) BETWEEN 1 AND @Day

(Last Month)

AND DATEDIFF(dd, ResolvedDate, GETDATE()) <= 30

(This Year)

AND DATEDIFF(yyyy, ResolvedDate, GETDATE()) = 0

(Last Year)

AND DATEDIFF(yyyy, ResolvedDate, GETDATE()) = 1