Loop thru upto 3 years (2005,2004,2003)

[svsuthar1]

Hey Team,

I have the below code working the way I want for one Year, and I could create 2 other connection and recordset and get this code to work for the other 2 years. But I want to do this by one connection and one recodset. I want to loop through all 3 years and and display it same way for each year.

I hope this is understandable. The code below is working for a single year.



<TABLE width=80% cellSpacing=0 cellPadding=1 border=1 align=center bordercolor="#cccccc" style='border-collapse:collapse;'>
<TR>
<TD colspan=14 align=center><FONT color=navy face=verdana size=4><B>2005</B></TD></TR>
<TR>
<TD width=250><B>Error Category</B></TD>
<TD align=middle><B>Jan</B></TD>
<TD align=middle><B>Feb</B></TD>
<TD align=middle><B>Mar</B></TD>
<TD align=middle><B>Apr</B></TD>
<TD align=middle><B>May</B></TD>
<TD align=middle><B>Jun</B></TD>
<TD align=middle><B>Jul</B></TD>
<TD align=middle><B>Aug</B></TD>
<TD align=middle><B>Sep</B></TD>
<TD align=middle><B>Oct</B></TD>
<TD align=middle><B>Nov</B></TD>
<TD align=middle><B>Dec</B></TD>
<TD align=middle><B>Total</B></TD>
</TR>

<%
If Request.QueryString("fldInternal")<>"" Then
Session("fldInternal")=Request.QueryString("fldInternal")
End If

'Open up a connection our access database
'we will use a DSN-less connection

Dim objConn, path
path="C:\Inetpub\

http://wwwroot\Supply_DB_Back.mdb"

Set objconn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString= "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
"DATA SOURCE=" & path
'"DATA SOURCE=" & Server.MapPath("Supply_DB_Back.mdb")
objConn.Open
Dim strSQL, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
strSQL ="SELECT [qryAllErrors].[fldRootCauseName], fldInternal, Sum(IIf([month]=1,[Count],0)) AS Jan, Sum(IIf([month]=2,[Count],0)) AS Feb, Sum(IIf([month]=3,[Count],0)) AS Mar, Sum(IIf([month]=4,[Count],0)) AS Apr, Sum(IIf([month]=5,[Count],0)) AS May, Sum(IIf([month]=6,[Count],0)) AS Jun, Sum(IIf([month]=7,[Count],0)) AS Jul, Sum(IIf([month]=8,[Count],0)) AS Aug, Sum(IIf([month]=9,[Count],0)) AS Sep, Sum(IIf([month]=10,[Count],0)) AS Oct, Sum(IIf([month]=11,[Count],0)) AS Nov, Sum(IIf([month]=12,[Count],0)) AS [Dec], sum([Count]) AS Total " _
& "FROM qryAllErrors " _
& "WHERE fldDate Between #1/1/2005# and #12/31/2005# " _
& "GROUP BY [qryAllErrors].[fldRootCauseName], qryAllErrors.fldInternal " _
& "HAVING (((qryAllErrors.fldInternal) LIKE " & Session("fldInternal")& "));"
'& "HAVING (((qryAllErrors.fldInternal) LIKE " & Request.QueryString("fldInternal")& "));"
'& "HAVING (((qryAllErrors.fldRootCauseName) Not Like 'Internal Error'));"

Dim objRS, Row
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

'Display the contents of the Table

Do While Not objRS.EOF
Jan = Jan + objRs("Jan")
Feb = Feb + objRs("Feb")
Mar = Mar + objRs("Mar")
Apr = Apr + objRs("Apr")
May = May + objRs("May")
Jun = Jun + objRs("Jun")
Jul = Jul + objRs("Jul")
Aug = Aug + objRs("Aug")
Sep = Sep + objRs("Sep")
Oct = Oct + objRs("Oct")
Nov = Nov + objRs("Nov")
Dec = Dec + objRs("Dec")

If Row Mod 2 = 0 Then
Response.Write(" <TR bgcolor=#DDDDDD>")
Else
Response.Write(" <TR> ")
End If
Response.Write(" <TD><A href='RootCauseDetail.asp?fldRootCauseName=" & objRs.Fields("fldRootCauseName") & "'>" & objRs("fldRootCauseName")& "</A></TD>")
Response.Write(" <TD & align=center> " & objRS("Jan") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Feb") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Mar") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Apr") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("May") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Jun") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Jul") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Aug") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Sep") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Oct") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Nov") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Dec") & "</TD>")
Response.Write(" <TD & align=center> " & objRS("Total") & "</TD>")
Response.Write(" </TR>")

'Move to the next row in the table
Row = Row +1
objRS.MoveNext
Loop
Response.Write("<TR><TD><B>Total</B></TD>")
Response.Write("<TD align=center><B>" & Jan & "</B></TD>")
Response.Write("<TD align=center><B>" & Feb & "</B></TD>")
Response.Write("<TD align=center><B>" & Mar & "</B></TD>")
Response.Write("<TD align=center><B>" & Apr & "</B></TD>")
Response.Write("<TD align=center><B>" & May & "</B></TD>")
Response.Write("<TD align=center><B>" & Jun & "</B></TD>")
Response.Write("<TD align=center><B>" & Jul & "</B></TD>")
Response.Write("<TD align=center><B>" & Aug & "</B></TD>")
Response.Write("<TD align=center><B>" & Sep & "</B></TD>")
Response.Write("<TD align=center><B>" & Oct & "</B></TD>")
Response.Write("<TD align=center><B>" & Nov & "</B></TD>")
Response.Write("<TD align=center><B>" & Dec & "</B></TD></TR>")

'Clean up our ADO objects

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = nothing

 

[Lothario]

put the if condition in your SQL query...something like this...

I am sure you will be asking for the year or date variable on your form

so supposing that your form variable is mydatefield

then something like this should work

If Year(Request.form("mydatefield")='2005' Then
& "WHERE fldDate Between #1/1/2005# and #12/31/2005# " _
end if
If Year(Request.form("mydatefield")='2004' Then
& "WHERE fldDate Between #1/1/2004# and #12/31/2004# " _
end if

Similarly for the other year...

the query will be constructed based on the if condition...

-L

 

[svsuthar1]

I want to show all three Year at the same time one right after another.

My SQL could be for 3 year combined. I just don't know how to loop through to give me the same format.

Samir

 

[Lothario]

if you want to show them together...i am afraid to say that you would need 3 recordsets...of course one connection object is enough though..

-L

 

[DreXor]

well with the current data structure i would agree with loth on this, but, a minor change could make life a little easier for you :

split your date field into 3 parts, MM DD YY in the database, this would remove the hoards of if[month] etc..

you could do a group by YY and sum on the month values etc.
this way you get a concise recordset of totals for a time period based on the criteria set forth.

also with this change you could do ( depending on the data ) totals per month, with a secondary option to total by day

second option would be to put all the work into a SP, where you could use conditionals, and multiple select statements to push all 3 data sets into one returned recordset.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[DreXor]

also in my experience in the past, most of the time you have some kind of report with totals, someone inevitably
asks where the numbers come from, i like having running totals in pages, with hidden table or page elements that can be shown on a click..

example, 1 row of total, click the total or a "+" sign on the side and the table grows out to show the numbers that made that total, it's nice when you're looking at 5-10 years of data, and unsure where some of it comes from.

also as part of a show/hide function on the show part, you can xml fetch the recordset secretly from another page and populate the table without having to process it all the first go on the page.


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[Tarwn]

The only problem you might run into with Drex's method is that munchs that don't have values would not be included in the recordset, whereas with your method you will be getting field values of 0.

The smallest change you could do in order to be able to pull back multiple years would be to add a year field to your SELECT and simply GROUP BY and ORDER BY the year part. Somehting like:

strSQL ="SELECT [qryAllErrors].[fldRootCauseName], fldInternal, 
Sum(IIf([month]=1,[Count],0)) AS Jan, Sum(IIf([month]=2,[Count],0)) AS Feb, Sum(IIf([month]=3,[Count],0)) AS Mar, Sum(IIf([month]=4,[Count],0)) AS Apr, Sum(IIf([month]=5,[Count],0)) AS May, Sum(IIf([month]=6,[Count],0)) AS Jun, Sum(IIf([month]=7,[Count],0)) AS Jul, Sum(IIf([month]=8,[Count],0)) AS Aug, Sum(IIf([month]=9,[Count],0)) AS Sep, Sum(IIf([month]=10,[Count],0)) AS Oct, Sum(IIf([month]=11,[Count],0)) AS Nov, Sum(IIf([month]=12,[Count],0)) AS [Dec],
[highlight]Year(fldDate) AS Year,[/highlight]
Sum([Count]) AS Total " _
    & "FROM qryAllErrors " _
    & "WHERE [highlight]Year(fldDate) Between Year(Now()) and Year(Now())+2 [/highlight]" _
    & "GROUP BY [qryAllErrors].[fldRootCauseName], qryAllErrors.fldInternal " _
    & "HAVING (((qryAllErrors.fldInternal) LIKE " & Session("fldInternal")& ")) " _
[highlight]    & "ORDER BY Year(fldDate)"[/highlight]
    '& "HAVING (((qryAllErrors.fldInternal) LIKE " & Request.QueryString("fldInternal")& "));"       
    '& "HAVING (((qryAllErrors.fldRootCauseName) Not Like 'Internal Error'));"

Now you just loop through the returned records keeping track of the year. You will need to create your own 3-year total as the Total field that is returned will be the total for that year. So you should be able to modifiy your code above with only a few additional lines to output this data the way you want - basically a variable to keep track of the last year you processed, a comaprison between the year in the recordset and the last one processed, and code to start a new row/output year/whatever and update the last proc year variable. The SQL above was written on the fly so I may have written in a few errors accidentally

-T

 

[svsuthar1]

Wow thanks guys,

I got it to work with your ideas. I was able to extract Year out of the date field and was able to sort by year and able to use loop to give what I wanted.

Thanks guys.

DreXor, How would you do what you are talking about can you help with any examples.