Split the following SQL statement

Question

I really need some help with the following.

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')

Basically what I have is 2 tables (payroll and history). In the payroll table I have the following items, date pay username timeon timeoff. and in the history table the columns are the months of the year JAN FEB MAR APR etc.

When a user enters his timeon and timeoff details he also enters a value into the pay column(if there is one, 0 if none) along with the date. So a typical row of the table will be:

Date           username      timeon    timeoff    pay
10/08/2014     xx@xx.com     08:00     10:00      60
11/08/2014     xx@xx.com     09:00     19:00      70
12/08/2014     xx@xx.com     07:00     18:00      60
14/08/2014     xx@xx.com     06:00     17:00      90
etc...

When the user has submitted the details the script above reads the total pay and updates the history table with the total figure for the month. (this is done in a separate script) the information from this script is collected by an array. One of the reason why I need help

Most importantly our payroll runs from the 11th of the month to the 10th of the next month and that's what the above SQL code does. It looks for all pay values from the 11th to the 10th and sums it as a total. It also gives me the values for the previous month (11/07/2014 to 10/08/2014). The output view looks something like this:

PeriodStart    total
2014-07-11     180       (Previous month)
2014-08-11     45        (This month)

Previous month being 11/07/2014 - 10/08/2014 This month being 11/08/2014 - 10/09/2014

What I need is for the above code to be re-written to give me 4 separate variables. As an example the variable names could be - previous_month, ptotal, this_month, ttotal these 4 variables can then be used in my classic asp coding.

If it's easier splitting the above SQL Statement into 2 separate statements this will be ok.

I'm trying to get away from using an array in my Classic ASP code as this is throwing errors when I have a new user. I would also find it easier to play with variables than I would with an array.

so History table is for "previous month" ? and Payrol for "current month" ? is that correct — Paul Maxwell, Aug 16 '14 at 09:19
History table is the total pay from the period from 11th month to 10th of the month. The table coloums are JAN, FEB, MAR, APR, MAY etc... — Rick Kap, Aug 16 '14 at 09:59
Your missing a `'` here -> `email = xx@.xx.com'` before `xx@xx.com`, should be `email = 'xx@.xx.com'`. — user692942, Aug 16 '14 at 12:32

score 1 · Accepted Answer · edited May 23 '17 at 11:57

According to this comment by @Damien_The_Unbeliever:

Best way to display correct value after a specific date Classic ASP SQL? , Can't you get the 4 variables from the recordset itself ? Something like :

set rs= ....

if not rs.eof then
     previous_month = Month(rs("PeriodStart"))
     ptotal = rs("total")

     rs.moveNext

     this_month = Month(rs("PeriodStart"))
     ttotal = rs("total")

end if

Split the following SQL statement

1 Answers1