DTS to Excel and recreating sheets each month

Question

Post reply

DTS to Excel and recreating sheets each month

MikeMahony

SSC Eights!

Points: 891
More actions
March 8, 2006 at 6:49 am

#67307

I have a process wherein I have to run 8 queries for one of our sales departments each month. These queries are a rolling 12 months of sales numbers. They create 12 tabs in an Excel spreadsheet. Our sales people, when they identify a prospect, tag the customer record with their tag. These queries utitlize that tag and look at the date it was set. We then run a query multiple times that returns:
Everyone tagged in March 2005, what were their sales for the next 12 months?
Everyone tagged in April 2005, what were their sales for the next 12 months?
This continues for a year.
I have put this into a DTS package, but my problem is this. If I run this query fresh up to February 2006, end up with a spreadsheet that has the following tabs:
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
Next month I want these same tabs, but I want to add March 2006 and drop February 2005. Is there a way to accomplish this without completing resetting up the DTS package?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 1

Something like :

SELECT DATENAME(month, dateadd(month,-12,getdate()))+' '+DATENAME(year, dateadd(month,-12,getdate())) AS 'First Tab'

SELECT DATENAME(month, dateadd(month,-11,getdate()))+' '+DATENAME(year, dateadd(month,-11,getdate())) AS 'SecondTab'

can help you manufacture the months

Then use that in your DTS package where you create the worksheets:

declare @TableName nvarchar(200)

set @TableName = (SELECT DATENAME(month, dateadd(month,-12,getdate()))+' '+DATENAME(year, dateadd(month,-12,getdate())) AS 'First Tab')

select @TableName

declare @SQLstring nvarchar(4000)

set @SQLstring = N'Create Table `' + @TableName + '` (`Col001` Long , `Col002` Long , `Col003` VarChar (20) , `Col004` VarChar (30) )'

select @SqlString

execute sp_executesql @SQLstring

Hope this helps!

MikeMahony SSC Eights! Points: 891 More actions · Answer 2

Thanks! I have one more question though. If the tabs already exist, how do I get rid of them so that I can recreate them? DROP TABLE doesn't seem to be allowed in DTS.