ASP - splitting records in array based on date

Asked By erik boehringer on 05-Apr-01 12:09 PM
first i will tell you what i am doing as of right now. then what i need to be doing. 
i am selecting all hours based upon which project is choosen. i am grouping the profileemails together and summing their hours to get a total for each distinct profileemail. right now this happens for all records. this the query that does this is: 

select a.profileemail, c.profile_team, sum(convert(decimal(10,4), a.hours)) as hourSum 
from projecttime as a 
inner join profile as b on (b.profileemail = a.profileemail) 
inner join profileteam as c on (b.profile_team = c.profile_team_id) 
where ... 
group by a.profileemail, c.profile_team 
order by a.profileemail
uArray = objConn.Execute(sSql).getrows 

this works great. here is where it gets tricky. 
they want to be able to search upon a date range. ie: 2/1/01 - 2/10/01 and retrieve all records within this range. no problem there..i can already do that. here is the problem. each day has to be separated and have the total for THAT day for each distinct profileemail. then go to the next day. and so on. at the same time, i have to take the totals of each day for each profileemail email and have the total. here is an example of how it would look 
(- for spacers) 
profileemail---team--day1--day2--day3--totals 
erik@bla.com---team--1.5---3-----4-----8.5 
erik2@bla.com--team--3-----4-----5-----12 
erik3@bla.com--team--1-----2-----2.5---5.5 
--------------totals-5.5---9-----11.5--52 

do you see how i am adding each row and then each colum? right now i can add the colum just fine when NO date range is given. i have NO clue how to split into different days and then total up each ROW and then total up each colum...and then add those TWO together. please help!

Familar with multi-dimensional arrays? - Asked By Robbe Morris on 05-Apr-01 12:19 PM

How familiar?  What you describe is why there are various reporting tools out there for this.  I take it your report works fine when you are displaying daily totals right?

I did something like this just recently.  Let me have a few minutes to look over my code and see how easy it would be to implement this.

actually no...i'm not...thats my main problem - Asked By erik boehringer on 05-Apr-01 12:50 PM

your right, it works just fine when finding just one day...or all the days. because i only have one column/row to add... but not with a range.

I'm working on a query right now... - Asked By Robbe Morris on 05-Apr-01 12:51 PM

...I'm trying to put together a small sample query as we speak.
sweet...will wait in the wings. np - Asked By erik boehringer on 05-Apr-01 12:53 PM
no problem
Let's give this a go... - Asked By Robbe Morris on 18-Jul-14 12:02 PM
This is a query from one of my tables that is
designed to do something similar to your own.
Adjust the columns and relationships accordingly.
select UserID,sum(hrs) as HRS,hrsdate
from TaskHrsWorked
group by UserID,hrsdate
order by UserID,hrsdate
Results look like this.  The 9th and 16th actually
have multiple entries summed up within the same day.
1	5.2500	2001-02-08 00:00:00.000
1	7.7500	2001-02-09 00:00:00.000
1	1.5000	2001-02-11 00:00:00.000
1	.2500	2001-02-15 00:00:00.000
1	1.2500	2001-02-16 00:00:00.000
1	.2500	2001-02-19 00:00:00.000
1	9.5000	2001-03-03 00:00:00.000
1	5.5000	2001-03-04 00:00:00.000
1	2.0000	2001-03-08 00:00:00.000
2	.7500	2001-02-19 00:00:00.000
2	1.2500	2001-02-22 00:00:00.000
2	.2500	2001-02-26 00:00:00.000
I've got to jump to a meeting for a while but
here's the jist of it.  You'll need to iterate through
the record set keeping track of whether the current userid
is the same one as the last record.  If it's different, write a "</tr><tr>" to signify the start of a new row for the new user.  Also reset the current userid to the new userid about to be processed.  All the while keeping hrs totals in little counter variables for each employee as well as the total for the group.  This requires no arrays at all.  Just keep adding to the HTML string for later output after all of the records have been processed.
This should give you a real good start.  If you have any other questions, I'll try to get back to them later today.
actually i can do that right now just fine - Asked By erik boehringer on 18-Jul-14 12:03 PM
its kinda like this. i could kinda do like this...but its not working for me. if you could help me with the loop through the array..that would be great.
select profileemail, workdate, profile_team, sum(conver(decimal(10,4), hours)) as hourSum from projecttime
where...
group by profileemail, workdate, profile_team
order by profileemail
uArray = objConn.Execute(sSql).getrows
now the fun part.
for u = to ubound(uArray,2)
<td>the date</td> (this works fine
next
here is the problem i am having
for u = 0 to ubound(uArray,2)
if strPrior <> uArray(1,u) then diff day?
if strPrior <> "" then
<tr>
<td>the user row for that day</td>
</tr>
strHourSum = strHourSum + CDbl(uArray(3,u)) <-- should add ALL hours for ALL rows for ALL users on that day
end if
end if
strPrior = uArray(1,u) 'next day
next
my problem is that it only lists the last entry...and does not add the days together ACROSS the columns to get the total for a user for all days. make sense?
this is one of the more wierd things i have had to figure out.
Not sure you can do it this way... - Asked By Robbe Morris on 05-Apr-01 02:44 PM
The first thing you have to do is get the maximum number of days for your HTML table columns by subtracting the end date from the start date.  If no start date/end date is provided, you may want to run a quick query before the report to determine the number of HTML columns you need.

You should be getting a unique row per employee for each specific date.  So, I think you'll need to compare the current employee and current date in order to make your proper calculations.  A change in current employee requires writing the last hours total and </tr> and then brings a change in row. A change in current date brings a new HTML column <td></td>.  

I'm pretty sure you'll have run this comparison horizontally one employee at a time then drop down to the next employee.
ok..any examples - Asked By erik boehringer on 05-Apr-01 02:47 PM
that makes sense, but i have no clue how to even begin to do that. i am sure once i have some examples of this i could go with it...but i am at a loss
Working up a quick sample now - Asked By Robbe Morris on 05-Apr-01 02:54 PM
...
Hmmm... - Asked By Robbe Morris on 05-Apr-01 03:54 PM
After looking at your request a little deeper, this
  is going to take considerable work (as you already know).
  You are looking to format something similar to this:

  UserName    Date 1  Date 2  Date 3   Date 4  Total
   
    Bob        5.25    4.25    0.00     5.00    14.50
    Sue        4.00    0.00    3.75     6.00    13.75
    Joe        3.00    4.25    6.00     0.00    13.25

  The problem with both of our solutions is that doesn't
  accomodate for dates worked by other employees but
  there is no record for this employee for that
  date.  We are not dynamically creating empty table 
  cells for records that do not exists.

  You may have to create an array of all possible dates
  <b>first</b> and then iterate through the date array for 
  each employee in the ADO recordset and either writing
  a table cell with the hours worked or writing an 
  empty cell based on whether you find a matching 
  date or not.  As we've said before, you can then
  clear out your temporary hours counters with the change
  of employee as you iterate through the recordset.

  I wish I could've been more help with this...
yea, its alittle strange - Asked By erik boehringer on 18-Jul-14 12:03 PM
might have to do it a different way, i will mess around with it. if you might happen to come acrossed a way of doing it, please let me know.