convert date range to month value

I am trying to generate reports from a MS SQL 2000 database. The data is entered according to date. The date format is char and formatted like: mm/dd/yy

I need to generate monthly reports based on billing cycles that run from the 25th to the 24th of the next month. I want to create a stored procedure that runs on the 25th of each month to automatically sum the daily values into a monthly total, but I don't know how to convert the date range to a value like "September". For example:

08/25/03 - 09/24/03 = September 2003

Is there a way to do this or a better design? Any suggestions?

Eventually there will be a front end web app to this that users can go to to find outh their own totals.

Thanks.

I think this will do it:

SELECT DATENAME(month, '09/24/03 ') AS 'Month Name'

SELECT DATENAME(year, '09/24/03 ') AS 'Year Name'

Vbscript also has functions that will strip out the date, like: Month(), Day(), Year().

I am not sure if I am answering your question...

Thanks dcarva!

But how can I mold that into a stored procedure (or vbscript) that I can schedule to run monthly and have it automatically update for the previous month?

In other words, how do I total the values for a given month each month?

I don't know if I am being clear. Sorry.

You can schedule a stored procedure. Create your stored procedure. (say it's called storedProcX) Open SQL Server. Go to Management->SQL Server Agent->Jobs. Create a new job. Go to Steps tab. Click on New. Select the database you are running against. In the 'command' textbox, enter the stored proc
you want to execute...

EXEC storedProcX

Then go to the schedules tab and add a schedule.

Thanks again!

I think I am pretty clear on the scheduling, however, I need some help in writing the proc, or scrapping that and writing the vbscript. I have a table with the following columns:
Date(char)
Cust(char)
Size(numeric)

This table is updated on a daily basis with the day's "size" total. What I want to do is run a proc each month that would total the daily "size" into a monthly total(sum) for each Customer. The only caveat is that a "monthly" total means from the 25th of the previous month to the 24th of the current month. For example, January 2004 = '12/25/03' - 01/24/04.

If possible, Iwould like to take the totals each month and write them to a different table.

Thanks again for your help!

Here is something I threw together. I would run this query in SQL analyzer to test it. Here is the code I think might work for you. This would have been much easier to write if you had stored your date fields as datetime. After you test it, throw it in a stored proc.

DECLARE @CurDate datetime
DECLARE @StartMonth int
DECLARE @StartYear int
DECLARE @StartDate varchar(10)
DECLARE @EndDate varchar(10)
SET @CurDate = GetDate()

-- Get last month and the year that last month was in
SET @StartMonth = DATEPART( month, GetDate() )
SET @StartYear = DATEPART( year, GetDate() )

-- If we are currently in January, then last month was in last year
IF ( @StartMonth = 1 )
BEGIN
SET @StartMonth = 12
SET @StartYear = ( @StartYear - 1 )
END
ELSE
BEGIN
SET @StartMonth = @StartMonth - 1
END

-- Set start and end dates
SET @StartDate = CONVERT( varchar(2), @StartMonth ) + '/25/' + CONVERT( varchar(4), @StartYear )
Print @StartDate
SET @EndDate = CONVERT( varchar(2), DATEPART( month, @CurDate ) ) + '/24/' + CONVERT( varchar(4), DATEPART( year, @CurDate ) )
Print @EndDate

-- Display customer and
SELECT Cust, SUM([size])
FROM Table1
WHERE CONVERT( DateTime, [Date]) >= CONVERT( DateTime, @StartDate )
AND CONVERT( DateTime, [Date]) <= CONVERT( DateTime, @EndDate )
GROUP BY Cust

Thanks for the quick (and working) response. I hope you are not getting sick of me yet, but when I ran the script it gave me 2 columns, client and size. Now, when I run it today, 1/13/2004, what dates is the size value good for? Is it last month, December '03? Just curious.

Also, can I just add another few lines to update another table with this output? Update existing table with new values.

Thanks, again!

This should display the date for you:

-- Display customer and
SELECT Cust, [Date], SUM([size])
FROM Table1
WHERE CONVERT( DateTime, [Date]) >= CONVERT( DateTime, @StartDate )
AND CONVERT( DateTime, [Date]) <= CONVERT( DateTime, @EndDate )
GROUP BY Cust, [Date]

As far as updating the other table, you can probably do an INSERT INTO...

INSERT INTO MyTable (Cust, Size)
SELECT FROM ....

(look in SQL Books ONLINE help that comes with SQL Server)

Thanks for all your help!

No problem. Did it work for you or are you stuck?

Oh it is working for me - I just need to work on the insert and then the interface.

One quick question on the revised version you posted. When I run that one it now gives me a date column. That is good but all the dates are different. I ran the script on the 13th, and it outputs many different dates. Is it just puuling dates from my table's date column, or maybe the last date anything was updated? It was just kind of confusing. For example:

blah 12/27/03 15236
blah 12/30/03 166
blah 12/25/03 1515
blah 01/11/04 325
blah 01/01/04 6971392
blah 01/10/04 3635201
blah 01/11/04 3627402
blah 01/09/04 625663
blah 01/09/04 660205
blah 12/25/03 78
blah 12/31/03 629801
blah 12/28/03 56379
blah 12/27/03 2052310

Any thoughts on why the different dates?

Actually, adding the date messes up the query. Let me look at it further. Try adding this to your select statement:

SELECT Cust, SUM([size]), MIN([Date]) StartDate, MAX([Date]) EndDate

I added what you posted to the select statement and here is the output...

blah 46218 12/27/03 12/27/03
blah 356353 01/07/04 01/07/04
blah 286225 01/11/04 01/11/04
blah 326764 01/05/04 01/05/04
blah 213198 01/06/04 01/06/04
blah 231402 12/31/03 12/31/03
blah 696 01/08/04 01/08/04
blah 2374 01/04/04 01/04/04
blah 55297 01/08/04 01/08/04
blah 595 12/28/03 12/28/03
blah 220064 01/12/04 01/12/04
blah 32646 01/05/04 01/05/04

The start date and end dates seem to match. Is that what you intended?

Yes