Date Format - 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

Analysis Server and Reporting Services (2005)

Date Format

Author

Topic

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 11:52:42

I have graph that will show the number events that happened in the past 3 years by Day, week, month, quarter, or year. I have a min Date field and max data field. The min date is populated from a database value. I would like the Max Field to be populated based upon the choice of how they want the date to be (Day, week, month, etc...). I want that date to be reverted to the previous week start because right now, since the month or week or whatever isn't done, I get this huge drop. I was wondering if there was any way to make is that if the user selects "Quarter", that the maxDate Field would revert the start of this quarter automatically, so the drop is eleminated. Is there a way to do this in reporting services?
Thanks in advance,

/jcarver

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:56:41

You can use a Calendar function like F_TABLE_DATE and do a LEFT JOIN against the Events table.

Peter Larsson
Helsingborg, Sweden

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 12:27:22

I'm not sure exactly what you mean. Maybe it would help to explain what I am doing a bit better. I have a million plus dates that are all formated in the SQL dateTime Formated as mm/dd/yyyy hh:mm:ss I have used some of the DateDiff and dateadd Functions to return how many projects were done in a day, week, month, or year, etc...however, I now want to be able to say that I just want to return the beggining day of the week if "Week" is chosen from the drop down list. Maybe Calender functions still work, but I wasn't able to find it. Thanks,

/jcarver

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-16 : 14:08:43

What is your definition of the "beginning of week"?

CODO ERGO SUM

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 14:49:37

Sunday is the first day of the week according to my database. So that would be what I would want to use as my first day of the week.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-16 : 15:07:11

Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 15:41:27

That works perfect, Thank you so much for that. I only have one question that remains, in my Query I have a bunch of IF statements that if the users input value is "Week" the statement will run the Week Statment. I can get the value in a query, but if I try to run the report, I can't because "StartofWeek" depends on a variable. How would you suggest getting around this? Thanks a bunch for the solution on the StartOf!!

/jared

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-16 : 15:51:31

If you want help with code, you will need to post it.

CODO ERGO SUM

jcarver
Starting Member

18 Posts

Posted - 2007-07-16 : 16:20:09

Ok, Here is the Code I have for a page then

IF(@Type= 'Day')
BEGIN
SELECT dbo.F_START_OF_DAY(getdate()) AS StartOfDay
END
IF(@Type='Year')
BEGIN
SELECT dbo.F_START_OF_YEAR(getdate()) AS StartOfYear
END
IF(@Type='Month')
BEGIN
SELECT dbo.F_START_OF_MONTH(getdate()) AS StartOfMonth
END
IF(@Type='Quarter')
BEGIN
SELECT dbo.F_START_OF_QUARTER(getdate()) AS StartOfQuarter
END
IF(@Type='Week')
BEGIN
SELECT dbo.F_START_OF_WEEK(getdate(), DEFAULT) AS StartOfWeek
END

The Type Field is a non-queried field that has a drop down containing each of those values. When I select the YEAR value from the Drop Down list, I want the Parameter @MinDate to be filed with whatever Value corresponds with Type.

EXAMPLE
I select Month from the drop down list.
I want the min Date field to become populated with the value passed to it in the F_START_OF_MONTH
END

What happens Now is that in order to get the value that I want in the min date box, I now can't change the date If I wanted to. I want to that to change whenever the drop down changes, but I also want to be able to manually change it. I can get the user to be change it, but then if I select it again, the value in the textbox won't change. If I selected "WeeK" from the dropdown list, textbox would fill in the value 07/15/2007, if I then changed my mind and selected "Month" the value would stay the same instead of changing to 07/01/2007. I can get each part of what I need, but never together. Thanks again,

/jcarver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources