Calculate Lastmonth in an SP

Hi - from within an SP (which is run automatically), how would I
determine range for 'last month' - eg. this is April, so I would like my
SP to be able to calculate 1 March 2005 to 31 March 2005?
My problem is for this: I have a .net web application which keeps track
of time for consultants. At the moment, on the first of each month, I
go to a web page, enter the start date and end date of the previous
month, and hit a button - this calculates, depending on the time the
consultant has spent with each client, appends the client ID, the agent
ID, and the commission to a 'billing' table.
The consultants then look each month, and click a button to send an
email to each of the clients (if they agree the charges).
What I would like to do, is have an SP, run from a job on the first of
each month, so that I don't have to manually run this process.
So, in the SP I would have something like:
Insert into charges (clientid, agentid, commission) values (@.cliendid,
@.agentid, @.commission) WHERE dateCharged = @.lastmonth
But how do I set @.lastmonth from within the SP - without me having to
become involved?
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
select dateadd(m,datediff(m,0,getdate())-1,0)as FirstDay,
dateadd(m,datediff(m,0,getdate()),0)-1 as LastDay
"Mark" <anonymous@.devdex.com> wrote in message
news:ezIhI4yPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> Hi - from within an SP (which is run automatically), how would I
> determine range for 'last month' - eg. this is April, so I would like my
> SP to be able to calculate 1 March 2005 to 31 March 2005?
> My problem is for this: I have a .net web application which keeps track
> of time for consultants. At the moment, on the first of each month, I
> go to a web page, enter the start date and end date of the previous
> month, and hit a button - this calculates, depending on the time the
> consultant has spent with each client, appends the client ID, the agent
> ID, and the commission to a 'billing' table.
> The consultants then look each month, and click a button to send an
> email to each of the clients (if they agree the charges).
> What I would like to do, is have an SP, run from a job on the first of
> each month, so that I don't have to manually run this process.
> So, in the SP I would have something like:
> Insert into charges (clientid, agentid, commission) values (@.cliendid,
> @.agentid, @.commission) WHERE dateCharged = @.lastmonth
> But how do I set @.lastmonth from within the SP - without me having to
> become involved?
> Thanks for any help,
> Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi Mark
You may want to use a calendar table to do this see
http://www.aspfaq.com/show.asp_?id=2519 or you can use the dateadd function
directly.
John
"Mark" wrote:

> Hi - from within an SP (which is run automatically), how would I
> determine range for 'last month' - eg. this is April, so I would like my
> SP to be able to calculate 1 March 2005 to 31 March 2005?
> My problem is for this: I have a .net web application which keeps track
> of time for consultants. At the moment, on the first of each month, I
> go to a web page, enter the start date and end date of the previous
> month, and hit a button - this calculates, depending on the time the
> consultant has spent with each client, appends the client ID, the agent
> ID, and the commission to a 'billing' table.
> The consultants then look each month, and click a button to send an
> email to each of the clients (if they agree the charges).
> What I would like to do, is have an SP, run from a job on the first of
> each month, so that I don't have to manually run this process.
> So, in the SP I would have something like:
> Insert into charges (clientid, agentid, commission) values (@.cliendid,
> @.agentid, @.commission) WHERE dateCharged = @.lastmonth
> But how do I set @.lastmonth from within the SP - without me having to
> become involved?
> Thanks for any help,
> Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>