There are some things you need to know about the fundamentals of relational database design, before you go much further. Otherwise you risk creating databases that contain logical inconsistencies, or databases that unnecessarily consume additional disk space. The basic fundamentals you need to understand are the primary key, column relationships, foreign key constraints, and database normalization (to at least 3rd normal form, preferably beyond). There will be a bit of a learning curve, but it will quickly pay for itself, and make your experience with T-SQL much smoother.

As an example, you mentioned you have two tables named [payroll] and [history]. You mentioned you use a script to move the data from [payroll] to [history]. The first thing to note is that [payroll] is, itself, a 'history' of sorts. The second thing to note is that by moving records from [payroll] to [history], you are creating two locations that contain the same fundamental data. A future reader of those two tables may uncover a discrepancy, where the data within the history table could not possibly be derived from the payroll table. That future reader would be stuck with a logical inconsistency ("which is correct, [payroll] or [history]?"). While you may believe that risk is worth the convenience because you alone control access to the data, I am fairly certain most database designs outlive the designer. A third thing to note is that further designs may depend upon [history], which means that making changes to the design (or the correction of an logical inconsistency) very difficult to do, later on. This is a major reason why having a firm grasp on the fundamentals of relational database design is important, at the very outset of a project. The fourth thing to note is that moving data from [payroll] to [history] will require disk I/O, which is the most costly operation a database server must do. The fifth thing to note is that if there is a concern about performance (hence why the [history] table was created), it is important to understand that indexing is designed to decimate the I/O costs of reading. It would be prudent to grasp the fundamentals of indexing, before creating a [history] of history already within [payroll]. Perhaps an assurance of logical consistency is worth a slight performance penalty.

There are perhaps more elegant ways to write what follows. I am trying to show you a concept - I am not trying to optimize the code. Keep in mind that SQL is not a procedural language (it is not like BASIC, C, etc). SQL Server's query optimizer can be expected to choose how SQL Server should execute statements within the following batch.

CREATE PROCEDURE

GetHistory

(

@previous_month date OUTPUT

,@ptotal int OUTPUT

,@this_month date OUTPUT

,@ttotal int OUTPUT

)

AS

SELECT

@previous_month = PeriodStart

,@ptotal = Total

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyDerivedTable

WHERE

PeriodStart =

(

SELECT

MIN(PeriodStart)

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MySecondDerivedTable

)

;

SELECT

@this_month = PeriodStart

,@ttotal = Total

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyThridDerivedTable

WHERE

PeriodStart =

(

SELECT

MAX(PeriodStart)

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyFourthDerivedTable

)

GO

Notice the 4 aliases named %DerivedTable.

I cannot help you with ASP code and how it handles parameters, but submitting:

EXEC GetHistory

to SQL Server will cause SQL Server to return four parameters: @previous_month;@ptotal;@this_month;@ttotal

Notice that the above code does not need a [history] table. The code is based upon [payroll] alone. You may become concerned about performance (depending upon the number of rows within [payroll]). To address a performance problem, take the body of the sproc and add the declarations back, as so:

declare

@previous_month date

,@ptotal int

,@this_month date

,@ttotal int

;

SELECT

@previous_month = PeriodStart

,@ptotal = Total

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyDerivedTable

WHERE

PeriodStart =

(

SELECT

MIN(PeriodStart)

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MySecondDerivedTable

)

;

SELECT

@this_month = PeriodStart

,@ttotal = Total

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyThridDerivedTable

WHERE

PeriodStart =

(

SELECT

MAX(PeriodStart)

FROM

(

select

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

as PeriodStart, SUM(pay) as total

from

Payroll

where

[Date] between

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20001211') and

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10

,CURRENT_TIMESTAMP))

,'20010210') AND (email = 'xx@.xx.com')

group by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

order by

DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

) AS MyFourthDerivedTable

)

Save above code as GetHistory.sql. Open SQL Server's Database Tuning Advisor, and submit GetHistory.sql for analysis. I am fairly certain the DTA will suggest the creation of indexes upon [date] and . Create the DTA's suggested indexes and again consider performance. If there is currently a trivial number of rows within [payroll], the benefits of the indexes may not be observable. If you expect the number of rows within [payroll] to increase, keep the indexes in place (for later).