October 24, 2011 at 12:46 pm
Hey guys,
I have a query that is for a report...The way I have it written works but its hardcoded...What I mean by that is I have @StartDate and @EndDate...
I have @StartDate = GETDATE - 30 and I have @EndDate = GETDATE...
This report will be run at the end of the month...So @EndDate is ok...But @StartDate needs to be changed b/c there is not 30 days in every month...So how would I create the @StartDate where it grabs the 1st of the month everytime the query is run?
Thanks
October 24, 2011 at 12:50 pm
Take a look at this. http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/3/25/some-common-date-routines.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2011 at 3:46 pm
Here is how I would do it:
If run on the last day of the month for the current month:
set @startdate = dateadd(mm, datediff(mm, 0, getdate()), 0);
set @enddate = dateadd(mm, datediff(mm, 0, @startdate) + 1, 0);
if run the following month for the previous month:
set @startdate = dateadd(mm, datediff(mm, 0, getdate()) - 1, 0);
set @enddate = dateadd(mm, datediff(mm, 0, @startdate) + 1, 0);
WHERE clause would do the following for either:
some_date_column >= @startdate and some_date_column <= @enddate
And Sean, thanks for the plug!
October 24, 2011 at 3:54 pm
And Sean, thanks for the plug!
No problem. It is a great shortcut to figuring all that stuff out. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2011 at 11:23 pm
CELKO (10/24/2011)
The next trick is a report period calendar. It gives a name to a range of dats.CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.
I like the MySQL convention of using double zeroes for months and years, That is "yyyy-mm-00' for a month within a year and "yyyy-00-00" for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
I see several issues with the table you suggest, One, it would require being updated each reporting period for each report. Two, there is nothing to ensure that the start and end dates cover the approriate time frame. If you are going to build a ReportPeriods table (I strongly dislike underscores in column names, personal preference) it should be built such that reporting periods are clearly established over a period of time such that it isn't necessary to update it monthly and that older reports can be recreated without modification as well.
Just saying. It is also nice that you like MySQL but this is MS SQL Server and we need to work within its capabilities.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply