CASE STATEMENT to LIMIT TIME - 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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE STATEMENT to LIMIT TIME

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-17 : 02:05:07

I have the following stored procedure in SQL 2005 and it works fine if the SourceStartTime and SourceEndTime are within the user defined range of @SourceStartTime and @SourceEndTime to calculate total duration in seconds and then sum to separate fields as days, hours, minutes and seconds. So my idea was to use a case statement to limit the time to @SourceStartTime and @SourceEndTime in cases where the SourceStartTime is before @SourceStartTime and SourceEndTime is after @SourceEndTime.

This is what I started with which works:

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL,
@SiteName nvarchar(50) = 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
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime)
AS TotalSeconds FROM TimeDetails) AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE (TimeDetails_1.SourceStartTime >= @SourceStartTime) AND (SiteDetails.SiteName IN (@SiteName)) AND
(TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))
END

This is how I tried to add the case statement, which does not work. I am a beginner to SQL so please be gentle.

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL,
@SiteName nvarchar(50) = 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
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime),
CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime = TimeDetails_1.SourceStartTime AND TimeDetails_1.SourceEndTime = TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime = @SourceStartTime AND TimeDetails_1.SourceEndTime = TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN TimeDetails_1.SourceStartTime = TimeDetails_1.SourceStartTime AND TimeDetails_1.SourceEndTime = @SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN TimeDetails_1.SourceStartTime = @SourceStartTime AND TimeDetails_1.SourceEndTime = @SourceEndTime
END AS TotalSeconds FROM TimeDetails) AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE (TimeDetails_1.SourceStartTime >= @SourceStartTime) AND (SiteDetails.SiteName IN (@SiteName)) AND
(TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))
END

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 02:20:51

you cant have CASE to return resultsets like this

CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime = TimeDetails_1.SourceStartTime AND TimeDetails_1.SourceEndTime = TimeDetails_1.SourceEndTime ...

it should be


SourceStartTime=CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime 
....

END,

SourceEndTime=CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime 
....

i.e you need to use seperate CASE for each field values.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-17 : 02:36:50

Ok, thanks for your reply. So I now have the following which is not working. Incorrect syntax near the keyword AS. Do have all the commas in the correct spot?

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL,
@SiteName nvarchar(50) = 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
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime),
SourceStartTime = CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN @SourceStartTime
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN TimeDetails_1.SourceStartTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceStartTime
END,
SourceEndTime = CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime
END AS TotalSeconds FROM TimeDetails) AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE (TimeDetails_1.SourceStartTime >= @SourceStartTime) AND (SiteDetails.SiteName IN (@SiteName)) AND
(TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 02:46:39

quote:
Originally posted by harlingtonthewizard

Ok, thanks for your reply. So I now have the following which is not working. Incorrect syntax near the keyword AS. Do have all the commas in the correct spot?

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL,
@SiteName nvarchar(50) = 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
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime),
SourceStartTime = CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN @SourceStartTime
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN TimeDetails_1.SourceStartTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceStartTime
END,
SourceEndTime = CASE
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime
WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime
WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime
END AS TotalSeconds FROM TimeDetails) AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE (TimeDetails_1.SourceStartTime >= @SourceStartTime) AND (SiteDetails.SiteName IN (@SiteName)) AND
(TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))
END

remove code in red. you've already given an alias so no need of giving another one using AS

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-17 : 03:03:09

If I remove the code in RED I get a big long list of errors. Indicating things like; The multipart identifier "Timedetails_1.SourceStartTime" could not be bound, no column specified for duration and invalid colunm name totalseconds.

In the original code I had without the CASE statements when I look at the tables in Static SQL I have a table called Duration with two fields; TimeID and TotalSeconds. That table is linked to the next table called TimeDetails_1 that contains TimeID, SourceStartTime, SourceEndtime and a few other fields that are not in use. That table then links through a few other tables to get finally to the SiteDetails table where the SiteName field is of interest as used in the filter.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 03:32:26

right, i've formated it a bit clearer so you can see it easier...


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),
			SourceStartTime = CASE
								WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceStartTime 
								WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN @SourceStartTime 
								WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN TimeDetails_1.SourceStartTime
								WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceStartTime
								END,
			SourceEndTime = CASE
								WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime 
								WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime <= @SourceEndTime THEN TimeDetails_1.SourceEndTime 
								WHEN TimeDetails_1.SourceStartTime >= @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime 
								WHEN TimeDetails_1.SourceStartTime < @SourceStartTime AND TimeDetails_1.SourceEndTime > @SourceEndTime THEN @SourceEndTime
								END 
			AS TotalSeconds 
	FROM TimeDetails
	) AS Duration 
INNER JOIN TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID 
INNER JOIN EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID 
INNER JOIN SystemState ON EventDetails.EventID = SystemState.EventID 
INNER JOIN SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE	(TimeDetails_1.SourceStartTime >= @SourceStartTime) 
		AND (SiteDetails.SiteName IN (@SiteName)) 
		AND (TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))

you've already assigned a column name for the 2nd case by saying sourceendtime = so the AS totalseconds becomes and error, but in the outer query you're referencing totalseconds still ???

and look at your derived table. your prefixing all your columns with timedetails_1 which is an alias from the outer query and not what you're actually selecting from at all.

confused..???

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 03:53:13

ah... should the AS TotalSeconds actually be aliassing your datediff function? and do those Case expressions belong in the outer query perchance?

Em

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-17 : 20:19:19

If you look at my first post you can see the first set of code without any case statements. This code works however is limited as described. Please excuse me as I am learning and often do not know what I am talking about so here goes:) I am using the datediff to calculate the difference between sourcestarttime and sourceendtime in seconds which are fields in the database (also have TimeID field with auto generated key) and storing as a calculated field called TotalSeconds in a table called duration (this is at least how the tables appear if done in the wizard outside a stored procedure). Duration is certainly not a table in my database originally. TotalSeconds is then used in the first select statement to calculate separate fields as Days, Hours, Minutes, and Seconds. Doing a basic sum of another table in my layout without this given an example of 60 seconds for one row and 2 seconds for another provides 62 seconds as a result rather than 1 hour and 2 seconds.
The problem I am having is that the duration of all events is only correct if sourcestarttime and sourceendtime is between the user-entered range of @sourcestarttime and @sourceendtime. Of course as shown by my case logic sometimes the sourcestarttime maybe before @sourcestarttime or sourceendtime maybe after @sourceendtime. In these cases I need to include the duartion of these events however the duration of each event needs to be limited to @sourcestarttime or @sourceendtime and not the real time in the database. So it seemed to me case statements could help here however I do not know where they should go? I thought they should go here because I need to change the calculation of Totalseconds so the result in this subquery can be used in the next first select statement.
I hope this makes sense.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-17 : 21:25:47

I could ask the question another way. How do I add a case statement to limit as described above? I have made the code a little smaller for this exercise.

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
FROM (SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime)
AS TotalSeconds FROM TimeDetails) AS Duration INNER JOIN
TimeDetails ON Duration.TimeID = TimeDetails.TimeID
WHERE (TimeDetails.SourceStartTime >= @SourceStartTime) AND
(TimeDetails.SourceStartTime <= @SourceEndTime + DAY(0))
END

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-18 : 02:10:19

Ok so now I have the following which at least runs and provides the correct results for 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:

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

elancaster
A very urgent SQL Yakette

1208 Posts

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

continued here apparently....
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106863

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-18 : 04:46:30