The number of days in a year with a record

Question

I have a SQL Server table called AgentLog in which I store its daily sales for each agent.

+-----------+------------+-------------+
| 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 the agent does not work on one particular day, there are no records of his sales on that day.

I want to generate a report (request) for a given date interval (for example: 01/01/2014 - 01/10/2014), which calculates how many days the agent was not present at work (for example: John - 6 days), was at work (John - 4 days ), and also returns an interval of time that he was not present (for example: John 01/03/2014 - 01/07/2014, 01/10/2014) (there may be several intervals).

Answer 1

, ( , , .). Excel .

Select *
from Custom.DateListTable dlt
left outer join agentlog ag
on dlt.Date = ag.Date

Answer 2

, , , , , .

, DATEDIFF:

SELECT DATEDIFF(day, '2014-01-01', '2014-10-01') AS totalDays;

, COUNT(*):

SELECT agentName, COUNT(*) AS daysWorked
FROM myTable
GROUP BY agentName;

, , totalDays - daysWorked:

SELECT agentName, COUNT(*) AS daysWorked, (DATEDIFF(day, '2014-01-01', '2014-10-01') - COUNT(*)) AS daysMissed
FROM myTable
GROUP BY agentName;

SQL Fiddle.

Answer 3

- (datetime) . , , . , (, )

Answer 4

. , , " " .

- :

DECLARE @IntervalStart DATE = '2013-12-30'
DECLARE @IntervalEnd DATE = '2014-01-10'

" " :

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

:

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

:

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

, - . NextMinusCurrent > 1 .

, . . CTE, sth, :

;WITH cte (

... query goes here

)
SELECT AgentName, SUM(DATEDIFF(DAY, OffWorkStart, OffWorkEnd) + 1) AS AbsenceDays
FROM cte
GROUP By AgentName

P.S. SQL Server LEAD, SQL SERVER 2012 .

SQL Fiddle

EDIT:

CTEstogether with ROW_NUMBER()can be used to simulate functions LEAD. The first part of the request will look like this:

;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 will also be running in SQL Server 2005!

Answer 5

Try it...

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 weekend check, as well as a link to “SpecialDate,” where a list of non-working days can be saved and excluded from the scan.

Repeating your question, I understand that this will only solve half your problem.