pulling current date queue

Question

I have a view that lists employee (EmpID), request number (ReqNo), date request was opened (OpenDate) and the date it was moved to the next step in the process (AssignDate). What I am trying to do is get an average of the daily queue size. If EmpID 001 has 20 requests on 1/1/13, then has 24 on 1/2/13, 21 on 1/3/13 the average over 3 days should be 21.66, rounded up to 22. I have the following view:

CREATE VIEW EmpReqs
AS
SELECT [EmpID], [OpenDate], [AssignDate], [ReqID] 
FROM [Metrics].[dbo].[Assignments]
WHERE OpenDate BETWEEN '01/01/2013' AND '12/31/2013' AND
[EmpID] IS NOT NULL AND
[ReqNo] NOT LIKE 'M%'

I then wrote a query to pull individual employee's queues per day:

/* First attempt to generate daily queue #s */
SELECT * FROM BLReqs
WHERE [BusLiaison] LIKE 'PN' AND
[OpenDate] <= '11/15/2013' AND 
[AssignDate] > '11/15/2013'

Because no one has attempted to pull this information before, I have no way of verifying how accurate the above is. I tried using current dates, since I can see those in our database to compare but the code doesn't work, nothing is returned when I change the dates to 2014 and run my query.

What is the easiest way to verify that my code is correct, short of manually counting a day's queue?

Can anyone see any issues with the above scripts?

Is there a way to get the above code to work with current dates?

score 0 · Accepted Answer · answered Jun 21 '14 at 18:45

This question is really hard to answer because it is kind of broad and has little information at the same time. I'll try anyway:

Because no one has attempted to pull this information before, I have no way of verifying how accurate the above is.

Try checking the result of this query for a few sampled dates.

I tried using current dates, since I can see those in our database to compare but the code doesn't work, nothing is returned when I change the dates to 2014 and run my query.

So clearly, the query is not working. You should probably find out why. Run the query for a date of which you know that it should return results but doesn't. Remove conditions one by one to see which one is incorrectly removing all rows. This should be enough to identify the bug.

Can anyone see any issues with the above scripts?

No, looks fine. A very simple query. That's why I said that we have too little information. There is some key piece of information missing that allows us to find the bug.

Is there a way to get the above code to work with current dates?

Stop staring at the code and hoping for a revelation. Debug it. Experiment.

I guess the problem is that I was staring at the code too long and became very frustrated. I took a break, and I'm going to hit it again today. — MKowalski, Jun 22 '14 at 15:07

pulling current date queue

1 Answers1