April 15, 2004 at 9:03 am
I am writing a report and the users and they would like to select the grouping of the report.
All the grouping will be on a single datetime field (oddly enough named datetime).
They would like to be able to choose if the report is grouped by half hour, day, week, or month.
I can accomplish day by using:
GROUP BY CONVERT(VARCHAR(10),DateTime, 110)
But how can I accomplish half hour, week, or month?
Many thanks for the help.
April 15, 2004 at 12:21 pm
Refer to the DATEPART functions in your Books Online. There are params for returning the month or the week.
For the half hour - assuming you're using SQL 2000 - I would create a User function that uses a combination of the DATEPART hour & minute params to return a "half-hour id"... say 1 - 48 for a day. Then you could use this as a grouping field.
April 15, 2004 at 12:50 pm
Here is a "HalfHour" function I came up with:
/*
Returns the half hour segment of a date
numbered 1 - 48
*/
CREATE FUNCTION [dbo].[HalfHour] (@TimeIn DateTime)
RETURNS int
AS
BEGIN
DECLARE
@Hour int,
@Mins int,
@HourPart int
SELECT @Hour = DATEPART(hh,@TimeIn)
SELECT @Mins = DATEPART(mi,@TimeIn)
SELECT @HourPart =
CASE
WHEN @Mins < 30 THEN 1
ELSE 2
END
RETURN( (@Hour*2) + @HourPart)
END
April 16, 2004 at 6:59 am
Hello Dana.
Unfortunately at times naming conventions are out of our hands.
In this case this database is for the Cisco VOIP phone system and we cannot change any field names.
But to answer your question it does pass the syntax check.
For anyone else having the same problem this is what I used for the grouping.
Group By DatePart(Month, [Datetime])
Group by Year([DateTime]), Month([DateTime]), Day([DateTime]), DatePart(Minute, [DateTime])/30
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply