Date Range assistance - SQL Server Forums

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources