Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- SUM(CASE WHEN a.Type = 1
- THEN CAST(DATEDIFF(s,
- ca.StartTime,
- ca.EndTime) AS FLOAT)
- / 60.0
- ELSE NULL
- END) AS WorkingTime ,
- AnnotherResult ...
- Select ... , something/WorkingTime, something*WorkingTime, ...
- From
- (
- Select *, CASE WHEN a.Type = 1
- THEN CAST(DATEDIFF(s,
- ca.StartTime,
- ca.EndTime) AS FLOAT)
- / 60.0
- ELSE NULL
- END) AS WorkingTime
- from myTable
- ) t
- DECLARE @CA TABLE
- (
- StartTime datetime,
- EndTime datetime,
- ActivityType int,
- Employee varchar(50)
- );
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-01 1:00 PM', '2014-04-01 2:00 PM', 1, 'Jim');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-03 1:00 PM', '2014-04-03 3:00 PM', 1, 'Jim');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-04 9:00 AM', '2014-04-04 11:00 AM', 1, 'Jim');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-05 10:00 AM', '2014-04-04 11:13 AM', 0, 'Jim');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-02 10:00 AM', '2014-04-02 3:00 PM', 1, 'Sally');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-04 1:55 PM', '2014-04-04 3:11 PM', 1, 'Sally');
- INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-03-30 1:45 PM', '2014-04-01 1:00 AM', 0, 'Sally');
- -- Method #1: CTE
- WITH BaseData AS
- (
- SELECT
- Employee,
- SUM (
- CASE
- WHEN ca.ActivityType = 1 THEN
- 1
- ELSE
- 0
- END
- ) AS NumType1Entries,
- SUM (
- CASE
- WHEN ca.ActivityType = 1 THEN
- CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
- ELSE
- NULL
- END
- ) AS TotalMinutesType1
- FROM @CA ca
- GROUP BY Employee
- )
- SELECT
- BaseData.Employee,
- BaseData.NumType1Entries,
- BaseData.TotalMinutesType1,
- CASE
- WHEN BaseData.NumType1Entries > 0 THEN
- BaseData.TotalMinutesType1 / BaseData.NumType1Entries
- ELSE
- NULL
- END AS MinutesPerType1Entry
- FROM BaseData;
- -- Method #2: Derived Table
- SELECT
- BaseData.Employee,
- BaseData.NumType1Entries,
- BaseData.TotalMinutesType1,
- CASE
- WHEN BaseData.NumType1Entries > 0 THEN
- BaseData.TotalMinutesType1 / BaseData.NumType1Entries
- ELSE
- NULL
- END AS MinutesPerType1Entry
- FROM (
- SELECT
- Employee,
- SUM (
- CASE
- WHEN ca.ActivityType = 1 THEN
- 1
- ELSE
- 0
- END
- ) AS NumType1Entries,
- SUM (
- CASE
- WHEN ca.ActivityType = 1 THEN
- CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
- ELSE
- NULL
- END
- ) AS TotalMinutesType1
- FROM @CA ca
- GROUP BY Employee
- ) AS BaseData;
- -- Method #3 - Cross Apply
- SELECT
- Employee,
- SUM (Calcs.Type1EntryCounter) AS NumType1Entries,
- SUM (Calcs.Type1EntryTotalMinutes) AS TotalMinutesType1,
- CASE
- WHEN SUM (Type1EntryCounter) > 0 THEN
- SUM (Type1EntryTotalMinutes) / SUM (Type1EntryCounter)
- ELSE
- NULL
- END AS MinutesPerType1Entry
- FROM @CA ca
- CROSS APPLY (
- SELECT
- CASE
- WHEN ca.ActivityType = 1 THEN
- 1
- ELSE
- 0
- END AS Type1EntryCounter,
- CASE
- WHEN ca.ActivityType = 1 THEN
- CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
- ELSE
- NULL
- END AS Type1EntryTotalMinutes
- ) AS Calcs
- GROUP BY Employee;
- -- Method #4 - Inline Table-Valued User-Defined Function, Deterministic
- -- Commented-out because it can't be run without creating the function first.
- /*
- SELECT
- Employee,
- SUM (Calcs.Type1EntryCounter) AS NumType1Entries,
- SUM (Calcs.Type1EntryTotalMinutes) AS TotalMinutesType1,
- CASE
- WHEN SUM (Type1EntryCounter) > 0 THEN
- SUM (Type1EntryTotalMinutes) / SUM (Type1EntryCounter)
- ELSE
- NULL
- END AS MinutesPerType1Entry
- FROM @CA ca
- CROSS APPLY dbo.CalcType1Statistics
- (
- ca.ActivityType,
- ca.StartTime,
- ca.EndTime
- ) AS Calcs
- GROUP BY Employee;
- */
- -- Method #5 - Inline Table-Valued User-Defined Function, Non-Deterministic Aggregation
- -- Commented-out because it can't be run without creating the function first.
- /*
- SELECT
- EmpSummary.Employee,
- EmpSummary.NumType1Entries,
- EmpSummary.TotalMinutesType1,
- CASE
- WHEN EmpSummary.NumType1Entries THEN
- EmpSummary.TotalMinutesType1 / EmpSummary.NumType1Entries
- ELSE
- NULL
- END AS MinutesPerType1Entry
- FROM dbo.GetEmployeeSummary () AS EmpSummary
- */
- SELECT WorkingTime
- FROM (
- SELECT SUM(CASE WHEN a.Type = 1 THEN CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
- ELSE NULL
- END) AS WorkingTime .....)
- ;WITH WT AS (SELECT SUM(CASE WHEN a.Type = 1 THEN CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
- ELSE NULL
- END) AS WorkingTime .....)
- SELECT WorkingTime
- FROM WT
- JOIN .....
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement