Best way to display correct value after a specific date Classic ASP SQL?

Question

I've been working on a website which i'm having problems with and need some help please!!

We have a database with date values and cash amounts ie

22/06/2014 15.00
23/06/2014 15.00
28/06/2014 15.00

etc...

In my html/asp code I have a table which shows the total amount made today, this month and last month, the problem is our month runs from the 11th of the month to the 10th of the next month.

Output

 Today    this Month   Last Month 
 £15.00    £135.00     £150.00

So the table is showing the correct amount in the correct columns but when the we get to the 11th of the month the value from 'this month' should move into 'last month' column as this is a new month for us.

I have tried everything to make this work, but have failed... hope someone can help me.

Here is my code

<%@ Language=VBScript %>
<html>
<head>
<% 
strDate =Date()

thisDay = day(strDate) 
refd = strDate+ 1 - thisDay 
fdlm = dateadd("m", -1, refd) 
fdtm = dateadd("m", 0, refd)
fdpm = dateadd("m", -2, refd)
fdtm = refd 

firstday_thismonth = fdlm + 10      '11th of this month
lastday_thismonth = fdtm + 9        '10th of this month
firstday_lastmonth = fdpm + 10      '11th of last month
lastday_lastmonth = fdlm + 9        '10th of last month 

Set conn = Server.CreateObject("ADODB.Connection")
conn.open connStr

strSQL = "SELECT cashpay FROM payroll WHERE email = '" & email & "' ORDER BY TempID DESC" 
Set rs = conn.Execute(strSQL)   

today = rs("cashpay")   ' TODAY

strSQL = "SELECT SUM(cashpay) AS thismonth FROM payroll WHERE (date BETWEEN '" & firstday_thismonth & "' AND '" &  lastday_thismonth & "')" 
Set rs = conn.Execute(strSQL)   ' THIS MONTH
thismonth = rs("thismonth")


strSQL = "SELECT SUM(cashpay) AS lastmonth FROM payroll WHERE (date BETWEEN '" & firstday_lastmonth & "' AND '" &  lastday_lastmonth & "')" 
    Set rs = conn.Execute(strSQL)                               
    lastmonth = rs("lastmonth") ' LAST MONTH
%>
</head>
<body>
<table width="450">
<thead>
<tr>
<th></th>
    <th align="left">Today</th>
    <th align="left">This Month</th>
    <th align="left">Last Month</th>
</tr>
</thead>
<tbody>
    <tr>
    <td>Payment</a></td>
    <td align="left">&pound;<%=today%>.00</td>
    <td align="left">&pound;<%=thismonth%>.00</td>
        <td align="left">&pound;<%=lastmonth%>.00</td>
    </tr>
</tbody>
</table>
   </body>
</html>

@ALL sorry I might have not made it clear what the problem is.

When I display the table it shows the values in the correct columns for the whole month (1st to the end of the month) and when it's the 1st of a new month then the columns shift to the right.

What I need is when we get to the 11th of the month is for the values to shift to the right. so in the example above if today was the 11th Aug and I displayed the table then 'this month' value of £135.00 would be displayed in 'last month' column and 'this month' value would display all the values from 11th Aug to 10th Sept.

I ran your code (except the SQL) and it appeared to produce the right dates. Have you Response.Write the SQL to see if the dates look right to you? What is the actual problem you are having? Could it be that your database server is expecting the dates in a different format (try "yyyy-mm-dd")? — johna, Aug 05 '14 at 22:29
Hi John. Yes the code works fine the problem I have is that I need some code to shift the columns over once we start a new month which for us is on the 11th of the month — Rick Kap, Aug 05 '14 at 22:36
John stole my comment! :-) I'm not sure I follow your response -- if you're getting the dates right upfront, the rest should fall out. Separately but related, the SQL you provide is going to give you different results than what your table looks to be set up to handle. The first query is going to give you as many rows as meet the conditions, whereas the second two (lastmonth and thismonth) are going to give you the single value you seem to expect. — Bret, Aug 05 '14 at 22:40
@Rick Kap, I don't follow your response either. Your queries are based on today's date so should be fine for any month you run this??? — johna, Aug 06 '14 at 02:57
Does it work the way you want if you increase the to-date by 1 ? check this : http://stackoverflow.com/questions/13099364/sql-server-fetching-records-between-two-dates — Flakes, Aug 06 '14 at 05:14
It's not really a display issue at all, is it - it's that you need to find the right logic to work out the start and end of the *current* period and the *previous* period, where those are based around the 11th of each month. — Damien_The_Unbeliever, Aug 06 '14 at 07:26

score 1 · Accepted Answer · answered Aug 06 '14 at 07:22

I've produced some SQL that should help, I think should solve the problem, if you can accept just running one query and having to process two rows:

select
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
        as PeriodStart,
    SUM(cashpay) 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')
group by
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')
order by
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,[Date])),'20010111')

The first row should be the value for the previous period, the second row will be the value for the current period

The expressions may look a little complex and I first wrote this query, to make sure I understood what was happening:

select
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,CURRENT_TIMESTAMP))
                                ,'20010111') as CurrentPeriodStart,
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,CURRENT_TIMESTAMP))
                                ,'20010210') as CurrentPeriodEnd,
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,CURRENT_TIMESTAMP))
                                ,'20001211') as PreviousPeriodStart,
    DATEADD(month,DATEDIFF(month,'20010101',DATEADD(day,-10,CURRENT_TIMESTAMP))
                                ,'20010110') as PreviousPeriodEnd

Now, we'll start in the middle, with the expression DATEADD(day,-10,CURRENT_TIMESTAMP). That just subtracts 10 days from the current date. So it always gives us a date in the right month for the current period - between the 1st and 10th of the month, it gives us a value in last month, but from the 11th onwards, it gives us a value in the current month.

Then, we use the DATEADD,DATEDIFF trick to effectively ignore the day part of that computed value and instead replace it with another value. In the first example (CurrentPeriodStart) we replace the day part with the 11th of the month. In the second example (CurrentPeriodEnd) we replace it with the 10th of the following month. The actual dates I'm using (20010101, 20010111 and 20010210) don't really matter much, except that the relationship between them is the one I'm trying to create with our dates - that 20010111 is the 11th day of the same month as 20010101 and 20010210 is the 10th day of the month that follows 20010101.

We then do the same trick again to find the dates for the preceding period.

That, finally, allows us to use these values in the first query in my answer - first, lets look at the WHERE clause:

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

That's just using the PreviousPeriodStart and CurrentPeriodEnd dates we worked out in the second query, so we're querying for all values from both periods in a single query.

Now, consider the expression in the SELECT and GROUP BY clauses:

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

where, instead of working with the current date, we're instead operating on the Date column - but we're using exactly the same trick we used to find the start of the current period. So this, instead, is computing the start of the period in which Date falls - whether that be the current period or the previous period. So that's why we're going to get two results - everything in the current period will compute one value, and everything in the previous period will compute a different value.

Finally, we use the same expression in the ORDER BY clause just so that we know what order the results are going to be returned in.

I know that this might look quite complicated, but hopefully if you study it for a while you'll be able to see the logic to how it works.

Thank you so much for the explanation. Your coding works. Would it be possible to do the same but have 4 separate variables with the answers from the query instead of running one query and having to process two rows? like CurrentPeriodStart, CurrentPeriodEnd, PreviousPeriodStart, PreviousPeriodEnd. — Rick Kap, Aug 06 '14 at 13:15
@RickKap - you could (you could base it off of my second query) but really, calling `MoveNext` shouldn't be that much work and you should generally, when writing SQL, try to ask as "large" a question as possible in a single query - let the database do the heavy lifting and work out how best to work out the answers. — Damien_The_Unbeliever, Aug 06 '14 at 13:17
Thank you Damien, it works perfectly. I've used an array to gather the information. — Rick Kap, Aug 06 '14 at 22:34

Best way to display correct value after a specific date Classic ASP SQL?

1 Answers1

Linked