Help Building Aging Report

# Thread: Help Building Aging Report

1. Senior Member
Join Date
Dec 1969
Posts
146

## Help Building Aging Report

Guys, I am working on a ticket tracking system, and the user wants to generate a variety of aging reports.<BR><BR>The data is stored in the following order:<BR><BR>position, date, ticketnumber<BR>new 01/01/2003, 1234<BR>BR 01/02/2003. 1234<BR>BR 01/02/2003. 1235<BR>etc.<BR><BR>What I need to calculate is 3 things:<BR><BR>1. Report 1, the aging in the most current position for each ticket. My thinking is to select the max date from the database for each ticket, and use asp datediff. Is there an easy way or a way in general to subtract the number of weekend days from now to the day I am pulling from the database?<BR><BR>ie <BR><BR>Ticket Current Position Aging<BR>1234 BR 2<BR>1235 BR 2<BR><BR>2. Report 2 Column 1, avg number of days tickets are in their current status<BR><BR>Project Status Project Count Average Aging<BR>New 0 0<BR>BR 2 2<BR><BR><BR><BR>3. Report 3 Historical Averages, average length each ticket is in each status. Basically, taking the difference between two entries getting the datediff and averaging this time for all the tickets and each step in the flow. Any ideas on the logic behind this one?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## I thought we had covered...

...how to get the number of business days between dates?<BR><BR>You said you had a table that had each business day in the year(s) with a "B" flag for it, no?<BR><BR>So then (1) becomes:<BR><BR>SELECT tickets.ticket, tickets.ticketdate, COUNT(bizdays.theDate) AS aging<BR>FROM tickets, <BR> bizdays, <BR> (SELECT ticket, Max(ticketdate) AS mtd FROM tickets GROUP BY ticket) AS T2<BR>WHERE tickets.ticket = T2.ticket<BR> AND tickets.ticketdate = T2.mtd<BR> AND bizdays BETWEEN tickets.ticketdate AND Date()<BR>GROUP BY tickets.ticket, tickets.ticketdate<BR>ORDER BY tickets.ticket<BR><BR>Or something along those lines.<BR><BR>Do I have you mixed up with somebody else? Sorry, if so.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
146

## "It wasnt me"

or however that song went :). I tried looking back some days in the message board but didnt the post you were referring to. All I need to subtract is the number of weekends. Is there a formula for finding out the number of weeks between the 2 dates, and just subtract the number x 2? I think that would work.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## What do you do about holidays?

Going to just ignore them?<BR><BR>Anyway, it&#039;s not as simple as that.<BR><BR>You can do<BR> datediff(&#039;w&#039;, dateInDB, Date() )<BR>but taking the two dates of #6/13/2003# and #6/16/2003#, for example, you will get *ZERO* weeks! Even though you start on Friday and end on Monday. Unfortunately, weeks are considered by DateDiff to be essentially the same as "days divided by 7".<BR><BR>Let&#039;s see...<BR><BR>I *think* this gets the right answer (for Access only! Has to be adapted to work with SQL Server):<BR><BR> datediff( &#039;d&#039;, dateInDB, Date() ) <BR> - datediff( &#039;w&#039;, dateInDB, Date() ) <BR> + 2 * CLNG( Weekday(dateInDB) &#062; Weekday(Date()) )<BR><BR>I just checked: It works so long as the dateInDB is *NOT* a Sunday. (If the dateInDB is a Sunday, then it acts the same as if it were the previous Friday. This, too, I *think* can be fixed, if you care a bunch.)<BR><BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
146

## Here was my best guess

ticketAging = DateDiff("d", ticketStartDate, Date())<BR> <BR> count=0<BR> newticketaging=0<BR> for x=0 to ticketaging<BR> if weekday(dateadd("d", ticketstartdate, x))="1" or weekday(dateadd("d", ticketstartdate, x))="7" then<BR> count=count +1<BR> End if<BR> next<BR> <BR> newticketaging=TicketAging-count<BR><BR>But I will try yours right now, and see if I pull the same result. As for holidays, we are ignoring them thank goodness :)

6. Senior Member
Join Date
Dec 1969
Posts
146

## Your method != my method

ticketStartDate = CDate(strDate)<BR> ticketAging = DateDiff("d", ticketStartDate, Date())<BR> <BR> count=0<BR> newticketaging=0<BR> for x=0 to ticketaging<BR> if weekday(dateadd("d", ticketstartdate, x))="1" or weekday(dateadd("d", ticketstartdate, x))="7" then<BR> count=count +1<BR> End if<BR> next<BR> <BR> newticketaging=TicketAging-count<BR> <BR> <BR> <BR> dateInDB=ticketStartDate <BR> othermethod= ticketaging - datediff( "w", dateInDB, Date() ) + 2 * CLNG( Weekday(dateInDB) &#062; Weekday(Date()) ) <BR> <BR> <BR> <BR> <BR><BR> <BR> response.write ticketaging & " " & newticketaging & " " & count & " " & othermethod<BR><BR><BR>here is the output<BR><BR>154 110 44 132 <BR>118 84 34 100 <BR>109 77 32 92 <BR>96 68 28 81 <BR>88 62 26 74 <BR>73 51 22 61

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ummm...and how am I...

...supposed to guess what those numbers mean?? <BR><BR>Maybe you could add in a display of the ticketStartDate for each line???<BR><BR>But I agree that mine is wrong. <BR><BR>But yours is (pardon me!) nutso!<BR><BR>If you are going to run a FOR loop to get the count, why do you count the weekend days and then subtract them from the datediff????<BR><BR>Why don&#039;t you just count the *NON* weekend days and be done with it??? No need for datediff, at all.<BR><BR>&#060;%<BR>For x = ticketStartDate To Date()<BR> If weekday(x) &#060;&#062; 1 AND weekday(x) &#060;&#062; 7 Then count = count + 1<BR>Next<BR>%&#062;<BR><BR>Presto. You are done.<BR><BR>****************<BR><BR>Incidentally, I *thought* you wanted to do all this in the SQL query! I didn&#039;t realize you didn&#039;t mind doing it in VBS code.<BR><BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
146

## RE: Ummm...and how am I...

Here is the output<BR>S= Start Date<BR>C= Date Diff (Now-S)<BR>W= Weekend Day Countr<BR>GT= S-w<BR>Y= Value using your method<BR> <BR>S C W GT Y <BR>6/6/2003 10 4 6 7 <BR><BR>Reason I can not do it purely in a query is the startr value is derived and ugly in this query. As it turns out, it is a derivation of the ticket number.<BR><BR>I am nutso, if I am doing a for loop, I might as well do the one main for loop :) instead of subtracting, life would be easier that way haa haa.<BR><BR>As for my original questions 2 and 3, those are done purely in queries, any guesses? :)<BR><BR>Thanks for the help

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## Wait a sec...

Isn&#039;t (2) based on data you get from this one? That is, doesn&#039;t it rely on getting average of time ticket is in a given state? But then how can you get the average *in the database* if you don&#039;t get the actual number of biz days ALSO *in the database*????<BR><BR>(My method doesn&#039;t work because of the Weekday()&#060;Weekday() thing. I&#039;m still thinking to see if there&#039;s a way around it.)<BR><BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
146

## In a sense

The issue I guess I am having is queries 2 and 3 come from a different table than query 1. Query 1 comes from a table where the startdate is derived.<BR><BR>Query 2 and 3 come from a status table so each ticket can have multiple status. I guess I will need to calculate the real busienss days difference between now and the most recent status date for each ticket. Add those up and divide by the total number of tickets in each status.<BR><BR>Query 3 feels like a recursive but I have no idea.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•