I have a SQL Server table named AgentLog in which I store for each agent his daily number of sales.
| AgentName | Date | SalesNumber |
| John | 01.01.2014 | 45 |
| Terry | 01.01.2014 | 30 |
| John | 02.01.2014 | 20 |
| Terry | 02.01.2014 | 15 |
| Terry | 03.01.2014 | 52 |
| Terry | 04.01.2014 | 24 |
| Terry | 05.01.2014 | 12 |
| Terry | 06.01.2014 | 10 |
| Terry | 07.01.2014 | 23 |
| John | 08.01.2014 | 48 |
| Terry | 08.01.2014 | 35 |
| John | 09.01.2014 | 37 |
| Terry | 10.01.2014 | 35 |
If an agent doesn't work on one particular day, there is no record of his sales on that date.
I want to generate a report(query) on a given date interval (ex: 01.01.2014 - 10.01.2014) that counts on how many days an agent wasn't present for work (ex: John - 6 days), was at work (John - 4 days) and also returns the date interval it wasn't present (ex: John 03.01.2014 - 07.01.2014, 10.01.2014) (there can be multiple intervals).
You need to create a custom table and populate it with a record for each date you want in your range (Feel free to go as far back in the past and forward into the future as you feel you may need.). You could do this in Excel very easily and import it.
Select * from Custom.DateListTable dlt left outer join agentlog ag on dlt.Date = ag.Date
I would approach this by getting the number of dates in the interval, as well as the number of dates the agent was at work, and you then have everything you need.
To get the number of days you can use
SELECT DATEDIFF(day, '2014-01-01', '2014-10-01') AS totalDays;
To get the number of days an agent worked, you can use the
COUNT(*) aggregate function:
SELECT agentName, COUNT(*) AS daysWorked FROM myTable GROUP BY agentName;
Then, you can just add to that query to get the days not worked by subtracting totalDays - daysWorked:
SELECT agentName, COUNT(*) AS daysWorked, (DATEDIFF(day, '2014-01-01', '2014-10-01') - COUNT(*)) AS daysMissed FROM myTable GROUP BY agentName;
Here is an SQL Fiddle example.
The only way I can think of to resolve this is to creating a temporary table with only one column (datetime) and save there all the dates from the selected range. You can create an stored procedure that fills that temporary table using a cursor with all the dates from the interval. Then do a LEFT join between your table and the temporary table to look for null values in your table (The days where that person didn't come to work)
SET DATEFIRST 1; --Monday DECLARE @StartDate DATETIME = '2014-01.01', @EndDate DATETIME = '2014-01.10'; WITH data as ( select 0 as i, DATEADD(DAY, 0, @StartDate) as TheDate union all select i + 1, DATEADD(DAY, i + 1, @StartDate) as TheDate from data where i < (@EndDate - @StartDate) ) SELECT a.AgentName, SUM(CASE WHEN c.Date IS NULL THEN 1 ELSE 0 END) AS Missing, SUM(CASE WHEN c.Date IS NOT NULL THEN 1 ELSE 0 END) AS Working FROM Agent a JOIN data b ON NOT EXISTS(SELECT NULL FROM SpecialDate s WHERE s.date = b.TheDate) LEFT JOIN AgentLog c ON c.AgentName = a.AgentName AND c.Date = b.TheDate WHERE DATEPART(weekday, b.TheDate) <= 5 GROUP BY a.AgentName OPTION (MAXRECURSION 10000);
It includes a check for weekends, as well as a reference to "SpecialDate" where a list of non working days can be maintained, and excluded from the check.
Reading your question again, I realise that this will only solve half your problem.
NOTE: The following answer mainly addresses the trickiest part of the question, which is how to obtain "absence from work" intervals.
Given these values as Interval Start - End dates:
DECLARE @IntervalStart DATE = '2013-12-30' DECLARE @IntervalEnd DATE = '2014-01-10'
the following query gives you the "absence from work" intervals:
SELECT AgentName, DATEADD(d, 1, t.[Date]) As OffWorkStart, DATEADD(d, -1, t.NextDate) As OffWorkEnd FROM ( SELECT AgentName, [Date], LEAD([Date]) OVER (PARTITION BY AgentName ORDER BY [Date] ASC) As NextDate, DATEDIFF(DAY, [Date], LEAD([Date]) OVER (PARTITION BY AgentName ORDER BY [Date] ASC)) As NextMinusCurrent FROM #AgentLog) t WHERE t.NextMinusCurrent > 1 -- Get marginal beginning interval (in case such an interval exists) UNION ALL SELECT AgentName, @IntervalStart AS OffWorkStart, DATEADD(DAY, -1, MIN([Date])) AS OffWorkEnd FROM #AgentLog GROUP BY AgentName HAVING MIN([Date]) > @IntervalStart -- Get marginal ending interval (in case such an interval exists) UNION ALL SELECT AgentName, DATEADD(DAY, 1, MAX([Date])) AS OffWorkStart, @IntervalEnd FROM #AgentLog GROUP BY AgentName HAVING MAX([Date]) < @IntervalEnd ORDER By AgentName, OffWorkStart
With the input data you supplied, the above query gives you the following output:
AgentName OffWorkStart OffWorkEnd --------------------------------------- John 2013-12-30 2013-12-31 John 2014-01-03 2014-01-07 John 2014-01-10 2014-01-10 Terry 2013-12-30 2013-12-31 Terry 2014-01-09 2014-01-09
The idea behind the basic part of the query is to employ the following nested query:
SELECT AgentName, [Date], LEAD([Date]) OVER (PARTITION BY AgentName ORDER BY [Date] ASC) As NextDate, DATEDIFF(DAY, [Date], LEAD([Date]) OVER (PARTITION BY AgentName ORDER BY [Date] ASC)) As NextMinusCurrent FROM #AgentLog
in order to get any existing gaps between the days a certain agent is present for work. A value of
NextMinusCurrent > 1 indicates such a gap.
Counting days is trivial once you have the above query in place. E.g. placing the above query in a CTE you can count total number of absence days with sth like:
;WITH cte ( ... query goes here ) SELECT AgentName, SUM(DATEDIFF(DAY, OffWorkStart, OffWorkEnd) + 1) AS AbsenceDays FROM cte GROUP By AgentName
P.S. The above query makes use of SQL Server LEAD function, which is available from SQL SERVER 2012 onwards.
SQL Fiddle here
CTEs together with
ROW_NUMBER() can be used to simulate
LEAD function. The first part of the query becomes:
;WITH cte1 AS ( SELECT AgentName, [Date], ROW_NUMBER() OVER (PARTITION BY AgentName ORDER BY [Date] ASC) As rn FROM #AgentLog ), cte2 AS ( SELECT cte1.AgentName, cte1.[Date], cteLead.[Date] AS NextDate, DATEDIFF(DAY, cte1.[Date], cteLead.[Date]) As NextMinusCurrent FROM cte1 LEFT OUTER JOIN cte1 AS cteLead ON (cte1.rn = cteLead.rn - 1) AND (cte1.AgentName = cteLead.AgentName) ) SELECT AgentName, DATEADD(d, 1, cte2.[Date]) As OffWorkStart, DATEADD(d, -1, cte2.NextDate) As OffWorkEnd FROM cte2 WHERE NextMinusCurrent > 1
SQL Fiddle for SQL Server 2008 here. I hope it executes in SQL Server 2005 also!