问题描述:

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 DATEDIFF:

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)

网友答案:

Try this...

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

EDIT:

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!

相关阅读:
Top