Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Range assistance

Author  Topic 

kgomez000
Starting Member

1 Post

Posted - 2015-02-26 : 13:29:53
We have this following query that brings up days worked from current Quarter to Date.

I need assistance modifying it to bring up days worked from Last Quarter.

(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 14:42:50
Do you mean previous quarter?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 14:53:48
[code]-- This quarter
DECLARE @FromDateIncluded DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101 08:00:00'),
@ToDateNotIncluded DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000401 08:00:00');

SELECT ...
(
SELECT COUNT(DISTINCT CONVERT(CHAR(10), DATEADD(HOUR, -8, p.ActualEnd), 101))
FROM dbo.PhoneCall AS p
WHERE p.OwnerID = x.SystemUserID
AND p.ActualEnd >= @FromDateIncluded
AND p.ActualEnd < @ToDateNotIncluded
) AS [Days Worked],
...
FROM ...;

-- Previous quarter
DECLARE @FromDateIncluded DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000401', GETDATE()), '19000101 08:00:00'),
@ToDateNotIncluded DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101 08:00:00');

SELECT ...
(
SELECT COUNT(DISTINCT CONVERT(CHAR(10), DATEADD(HOUR, -8, p.ActualEnd), 101))
FROM dbo.PhoneCall AS p
WHERE p.OwnerID = x.SystemUserID
AND p.ActualEnd >= @FromDateIncluded
AND p.ActualEnd < @ToDateNotIncluded
) AS [Days Worked],
...
FROM ...;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -