| Author |
 Topic  |
|
|
snaayk
Senior Member
USA
1061 Posts |
 Posted - 26 March 2002 : 16:25:11
|
I currently have a table with a lot of info. I want to just take one column and sum it up. I then want to group it by date and by crew of people. Now the info I get is correct. I get a total OT for the day. Now, the problem is displaying it in ASP. You see, I want to display it like this:
Name of group | Date1 | Date2 | Date3 | Date4
joe | 12.4 | 1.25 | 2.75 | 4.75
jane | 2.5 | [NO DATA] | 5.75 | [NO DATA]
Where DateX is the dates pulled from the db and the values come from the db as well.
My current code will give 6 records, 4 records for joe and 2 for jane, in order of date. But this is no good for displaying it on a page. any ideas how this would be done???
current code:
[code]SELECT crew.f_name, crew.mstr_rc, daily_activity.date, Sum(daily_activity.tot_ot) AS SumOftot_ot
FROM crew INNER JOIN (employee INNER JOIN daily_activity ON employee.tech_no = daily_activity.tech_no) ON crew.rc = employee.rc
GROUP BY crew.f_name, crew.mstr_rc, daily_activity.date
HAVING (((crew.mstr_rc)=[master_rc])
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 March 2002 : 18:14:43
|
What is the problem? I don't seem to see any problem with this. What you want to do is doable...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
crash
Advanced Member
Netherlands
2064 Posts |
Posted - 26 March 2002 : 18:22:29
|
ruirib's right; doable
Crash's Site | Crash is from 
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 March 2002 : 18:23:07
|
Well, now I think I see it. You want to group the data right?
Well it looks like this needs a bit of processing. I'll take a look and see if I can come up with a suggestion...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
crash
Advanced Member
Netherlands
2064 Posts |
Posted - 26 March 2002 : 18:26:38
|
still it's doable. you want ot do it, ruirib?
Crash's Site | Crash is from
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 March 2002 : 18:29:41
|
quote:
still it's doable. you want ot do it, ruirib?
Crash's Site | Crash is from
Why, you want to? You can take it if you like it. But is not as simple as it looks. The dates are your problem here, you can have as many dates as records. It is doable, but can give you some work...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 26 March 2002 18:35:35 |
|
|
|
crash
Advanced Member
Netherlands
2064 Posts |
Posted - 26 March 2002 : 18:36:21
|
i know that, that's why i asked if you wanted to do it  j/k
unfortunately, i don't have the time to do it... (no, that is not an excuse, i know. it's the truth!)
Crash's Site | Crash is from
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 March 2002 : 18:55:27
|
Ok, here is an approach to do it. Snaayk you need to create a crosstab query. It's the simplest and quickest way to do it. With such a crosstab query you get a line per record, just the way you want it.
The details can get a bit complicated. Email me and I'll help ya do it.
<edit>There is one thing that I don't understand in your example, however. How come you have three fields in the SELECT part and in the GROUP BY clause and the crew.mstr_rc column is never shown? </edit>
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 26 March 2002 19:52:55 |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 26 March 2002 : 22:25:41
|
Thats just the SQl statement I was playing with. It comes up with the correct data but not necesarily the best or most efficient way.
For a better explanation:
I want to pull hours for a given time period for a given amount of groups. Lets say 4 days and 3 groups. I can do this. So that I can display like I did above. The days in columns and the groups in rows.
I will email you ruirib, thanks for the help....again 
|
|
|
| |
Topic |
|
Topic Locked
