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 my MSSQL Database. In the payroll table I have the following columns:- Date, username, timeon, timeoff and pay 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 query 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.
Thanks in advance