How do I use Alias in DateDiff

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

SQL Server 2005 Forums

Transact-SQL (2005)

How do I use Alias in DateDiff

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-18 : 02:43:04

Ok so now I have the following which at least runs and provides the correct results from the case statement:

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SUM(Duration.TotalSeconds) / 86400 AS Days, SUM(Duration.TotalSeconds) % 86400 / 3600 AS Hours, SUM(Duration.TotalSeconds) % 3600 / 60 AS Minutes, SUM(Duration.TotalSeconds) % 60 AS Seconds, TimeDetails.SourceStartTime, TimeDetails.SourceEndTime, StartTime, EndTime
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime) AS TotalSeconds,
CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
GROUP BY TimeDetails.SourceStartTime, TimeDetails.SourceEndTime, StartTime, EndTime
END

Which is returning the following correctly however the problem I have now is that the DATEDIFF is not using StartTime or EndTime rather SourceStartTime, SourceEndTime fields directly from the DB so the total hours is 5 hours for the 3 events returned rather than the real answer I need which would be 3 hours:

Running [dbo].[Total_Duration_Stored] ( @SourceStartTime = 7/1/2006, @SourceEndTime = 7/31/2006 ).

Days Hours Minutes Seconds SourceStartTime SourceEndTime StartTime EndTime
----------- ----------- ----------- ----------- ----------------------- ----------------------- ----------------------- -----------------------
0 2 0 0 30/06/2006 11:00:00 PM 1/07/2006 1:00:00 AM 1/07/2006 1/07/2006 1:00:00 AM
0 1 0 0 15/07/2006 10:00:00 AM 15/07/2006 11:00:00 AM 15/07/2006 10:00:00 AM 15/07/2006 11:00:00 AM
0 2 0 0 31/07/2006 11:00:00 PM 1/08/2006 1:00:00 AM 31/07/2006 11:00:00 PM 31/07/2006
No rows affected.
(3 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Total_Duration_Stored].

If I replace SourceStartTime, SourceEndTime in DATEDIFF with StartTime, EndTime then I get the following error? As you can see from the result the StartTime and Endtime now seem to have the correct data for a DATEDIFF.

Invalid column name 'StartTime'
Invalid column name 'EndTime'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 02:55:57

continued from here....
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106766

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 04:45:10

you cant directly uses aliases inside datediff. either repeat the case inside datediff or change like this

SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime) AS TotalSeconds,StartTime,EndTime
FROM
(SELECT TimeID, CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
)t
GROUP BY TotalSeconds, StartTime, EndTime

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-21 : 00:14:11

If I do this then I get errors:

Ambiguous column name TimeID.
The Multipart identifier "Duration.TotalSeconds" Could not be bound error repeated four times.

SELECT SUM(Duration.TotalSeconds) / 86400 AS Days, SUM(Duration.TotalSeconds) % 86400 / 3600 AS Hours, SUM(Duration.TotalSeconds) % 3600 / 60 AS Minutes, SUM(Duration.TotalSeconds) % 60 AS Seconds
FROM
(SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime) AS TotalSeconds,StartTime,EndTime
FROM
(SELECT TimeID,
CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
)t
GROUP BY TotalSeconds, StartTime, EndTime

If I change 'SELECT TimeID, DATEDIFF' to 'SELECT Duration.TimeID, DATEDIFF' then that gets rid of the first error but the other four remain.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-21 : 00:44:38

Thanks for your help. Here is the final answer that seems to work so far:

Final Answer with specific rows returned for each time event.

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
FROM
(SELECT TimeDetails.TimeID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
FROM
(SELECT TimeID,
CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime + DAY(0)
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
) AS T
GROUP BY TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
END

Change to the following to return only total time.

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds
FROM
(SELECT TimeDetails.TimeID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds
FROM
(SELECT TimeID,
CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime + DAY(0)
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
) AS T
END

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-21 : 02:34:35

Fixed a bug if query with start and end date is the same day.

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
FROM
(SELECT TimeDetails.TimeID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
FROM
(SELECT TimeID,
CASE
WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime
ELSE TimeDetails.SourceStartTime
END AS StartTime,
CASE
WHEN TimeDetails.SourceEndTime > @SourceEndTime + DAY(0)THEN @SourceEndTime + DAY(0)
ELSE TimeDetails.SourceEndTime
END AS EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE NOT ((TimeDetails.SourceEndTime < @SourceStartTime) OR (TimeDetails.SourceStartTime > @SourceEndTime + DAY(0)))
) AS T
GROUP BY TotalSeconds, StartTime, EndTime, SourceStartTime, SourceEndTime
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources