Category — Stuff

Many web sites and blogs will tell you how to find the last day, first day of a month or year

But what if you need the month end and begin dates for a specific time period?  Well, here is the code for that:

–script to get month begining & end dates for a time period
–declare all variables
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @MonthEndDate datetime
DECLARE @MonthBeginDate datetime

DECLARE @MonthlyDates Table
(    MonthID INT NOT NULL IDENTITY(1,1),
MonthBeginDate SMALLDATETIME NOT NULL,
MonthEndDate SMALLDATETIME NOT NULL
)

–set the time period here
SET @StartDate = ‘3/1/2009′
SET @EndDate = ‘5/31/2012′

–Get the first month’s begin and end dates
SELECT @MonthBeginDate = DATEADD(dd,-(DAY(@StartDate)-1),@StartDate)
SELECT @MonthEndDate = DATEADD(mm,1,@StartDate) – DAY(DATEADD(mm,1,@StartDate))

–do the first insert into the holding table
INSERT INTO @MonthlyDates
(MonthBeginDate, MonthEndDate)
VALUES
(@MonthBeginDate, @MonthEndDate)

–use while loop to move through each month
WHILE @MonthEndDate < @EndDate
BEGIN

SELECT @MonthBeginDate = DATEADD(mm, 1, @MonthBeginDate)
SELECT @MonthEndDate = DATEADD(dd, -1, DATEADD(mm, 1, @MonthBeginDate))

INSERT INTO @MonthlyDates
(MonthBeginDate, MonthEndDate)
VALUES
(@MonthBeginDate, @MonthEndDate)

END
SELECT
MonthBeginDate,
MonthEndDate
FROM @MonthlyDates

So this code will give you all the months begin & end dates, in order.  Now in this code I’m not looking at when the @StartDate or @EndDate falls in the month.  For example, if I pass into the code a start date of March 10, 2009 it will return, as the first row, the begin and end dates for the month of March.  If you wanted to only return dates for whole months that your range covers (in the above example that would be a start month of April), you would need to alter the code.

Oh, and it is good for leap years too!

This past week I needed to use the TOP keyword for some code and came across some excellent resources:

First, the quick basics of top:

Dynamically controlling the number of rows affected by a SQL Server query

Next, a good article on how to retrieve top and bottom rows together:

How to Retrieve TOP and BOTTOM Rows Together using T-SQL

And an excellent article on the TOP per group scenario:

Who’s On First? Solving the Top per Group Problem

And the book Inside Microsoft SQL Server 2008 T-SQL Querying there is a whole chapter mostly on TOP; Chapter 9 TOP and APPLY.

TOP isn’t something used everyday in SQL Server, but when you need it, you’ll be happy it’s part of TSQL.

I’ve installed both RS 2000 & RS 2005 on various desktops and laptops in the past with no problems, so I figured getting RS 2008 up and running would be no big deal.  But I did run into a little problem along the way.

After the install, I tried to navigate to the Report Manager home page and I was greeted with a login box:

Hey I’m a local admin, I don’t need to log in. What gives!  So I scratched my head for a bit and then fired up Google.  But the information I found didn’t help me much. I tried several suggestions such as changing the account that RS Service runs in.  No luck with that.  Finally  I found a some help in one forum post (located at an MSND forum) that suggested changing the intranet security settings.  I wasn’t sure if it would work because I was on a different version of Windows (XP vs.. Vista), and they described a somewhat different set of problems, but I figured it was worth a try.  After some trial and error I put my URL in the allowed sites box, and it worked!

The link above gives the step by step for solving this, but here are some pictures showing what I did to fix the problem:

First go to Tools, Internet Options, Security Tab. Click on Local Intranet.

Then click on the “Sites” button to bring this screen up.

Click on the Advanced button and enter the URL in the “Add websites to this zone” box.  Click Add.

Click Close or Ok to back out of the boxes.  Refresh your screen and you are done!  Report Manager should appear now.