SQL Where Clause in Deltek Vision
So it turns out it’s a nightmare to run a report on this to show Timesheets that haven’t been updated, and I HATE (make that #@!$* HATE, pardon the language ) creating and maintaining custom reports. So one option is to use a sql search to return the list of employees that you can then use in any employee list report. One more catch is that you need a way to calculate the Timesheet ending week. Enter the SQL Function, this allows you to do calculate the week ending on the fly. I’m not going to dig into the detail of functions, it’s a somewhat advanced topic, but they’re easy enough to create and use. So for our example I used the following.
( @VDATE DATETIME)
DECLARE @I smallint
set @VDATE =
DATEADD(dd, DATEDIFF(dd, 0, @VDATE), 0)
SET @I = 1
WHILE @I <= 7
SET @I = @I + 1
This basically loops through the next 7 days, when the day matches Friday it returns that day. You can change it to next Monday by just replacing the word ‘Friday’ and I’d suggest relabeling the function. Make sure you create this function in the Vision database as well. On a side note if you do timesheets every two weeks this will not work, off the top of my head I’m not sure how you’d do this, may be do a search for the next open timesheet period in vision?
On to the search, so to begin a SQL Where clause I usually select a field that I know is in another table from the Advanced Field search, as seen below.
Then I change the display type to SQL Where Clause, and then I can use that as my template.
To get the final result I first filter the tkmaster table (Timesheet master table) to only be the records that end in this week timesheet period using the new function: (tkMaster.EndDate = dbo.NEXT_FRIDAY(CURRENT_TIMESTAMP)
And then I used an AND to get the Tkmaster records that are not submitted and the Modification date is less than this NOW minus 1 day, so 24 hours to the moment:
(Submitted=’N’ and tkMaster.ModDate < DateAdd(d, -1, CURRENT_TIMESTAMP) ))
A different approach would be to use hours and say subtract 36 hours, a more reasonable period which would look like this: tkMaster.ModDate < DateAdd(hh, -36, CURRENT_TIMESTAMP) ))
So this is the final result, which I then save as a saved search.
Last note, I don’t really care for this model/idea of the mandatory 24hr timesheet and it wouldn’t be my recommendation to manage this, however I was asked to do it so there it is. I’d recommend spending the effort (which is signification the bother employee’s everyday) on how to make employees more efficient, create more value, etc… I guess it depends on how much revenue or value you believe you’re missing by not having a timesheet done every day. But I hope this gives you some ideas on how the SQL where clause could be used. Please comment if you have any ideas on this topic or email me at firstname.lastname@example.org