Help with report-like sproc thing
-
Hi all,
I have three tables:
Table: Week
WeekID (int)
WeekNum (int)
WeekPlannedWork (decimal)
Table:Activities
ActID (int)
PercentageofWork(decimal)
Table
rogressTblProgressID (int)
WeekID (int)
ActID (int)
ProgressPerc (decimal)
What I'm trying to do is get a report-like view of weekly progress for all activities, the weeknum or weekid will be supplied by an asp page. However, the report thing should show the value of ProgressTbl.ProgressPerc*Activities.PercentageofWork/100 for the week, the current total in percentage for each activity, and the cumulative value of PercentageOfWork based on the week (and the previous weeks).
Hope I've been clear enough.
I've been at it for a few days and losing my hair. Any help is truly appreciated.
-
Can you also post a few rows of data that demonstrates what you have to work with, along with the desired report-output from that sample data?
(please post data in the form of ready-made insert lines)
/Kenneth
-
thanks, i'll try,
Activities - (Fields - ActID (pk, identity(1,1), ActTitle (varchar50), PercentageofWork(Decimal)
Preparation of Learning Units,25.0
Compilation of Resources, 25.0
Materials Write-up, 20.0
Testing and Certification,30.0
Week - (Fields - WeekID (pk, identity(1,1), WeekNum (int), WeekPlannedWork(Decimal)
1,30.0
2,10.0
3,10.0
4,10.0
5,40.0
ProgressTbl (Fields - ProgressID (pk, identity(1,1), WeekID (int), ActID(int), ProgressPerc(Decimal)
1,1,30.0
1,3,10.0
1,4,5.0
2,1,10.0
2,2,20.0
3,1,10.0
3,2,10.0
The thing for the report would look like this:
Week 1
Activity | Progress | Actual Work Weightage
Preparation of Learning Units| 30.00%| =30% of 25%
Compilation of Resources| 0| =0% of 25%
Materials Write-up| 10.00| =10% of 20%
Testing and Certification| 5.0| =5% of 30%
This Week Work: = Total for the week
Current Cumulative: = Total for the week and past weeks(if any)
Hope I'm clear enough, thanks for helping.
-
Deleted answer, thought I had it but
Far away is close at hand in the images of elsewhere.
Anon. -
SELECT a.ActTitle AS [Activity],
ISNULL(p.ProgressPerc,0) AS [Progress],
ISNULL(p.ProgressPerc,0)*a.PercentageofWork/100 AS [Actual Work Weightage],
ISNULL((SELECT SUM(ISNULL(p2.ProgressPerc,0)) FROM @ProgressTbl p2 WHERE p2.ActID = a.ActID AND p2.WeekID <= @WeekID),0) AS [Cumulative]
FROM @Activities a
LEFT OUTER JOIN @ProgressTbl p
ON p.ActID = a.ActID AND p.WeekID = @WeekID
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for your help David,
right now i'm cumulative on the percentage of work, how to get the cumulative of the actual work? is it possible?
Thanks again.
-
SELECT a.ActTitle AS [Activity],
ISNULL(p.Progress,0) AS [Progress],
ISNULL(p.Progress,0)*a.PercentageofWork/100 AS [Actual Work Weightage],
ISNULL(p.Cumulative,0) AS [Cumulative]
FROM @Activities a
LEFT OUTER JOIN (SELECT p2.ActID,
SUM(CASE WHEN p2.WeekID = @WeekID THEN p2.ProgressPerc ELSE 0 END) AS [Progress],
SUM(p2.ProgressPerc) AS [Cumulative]
FROM @ProgressTbl p2
WHERE p2.WeekID <= @WeekID
GROUP BY p2.ActID) p
ON p.ActID = a.ActID
What do you mean by 'cumulative of the actual work' ?
Far away is close at hand in the images of elsewhere.
Anon. -
sorry for not being clear, could i get the total for the [Cumulative] column for each week as well? do i need another sproc for that?
-
SELECT a.ActTitle AS [Activity],
ISNULL(p.Progress,0) AS [Progress],
ISNULL(p.Progress,0)*a.PercentageofWork/100 AS [Actual Work Weightage],
SUM(ISNULL(p.Cumulative,0)) AS [Cumulative]
FROM @Activities a
LEFT OUTER JOIN (SELECT p2.ActID,
SUM(CASE WHEN p2.WeekID = @WeekID THEN p2.ProgressPerc ELSE 0 END) AS [Progress],
SUM(p2.ProgressPerc) AS [Cumulative]
FROM @ProgressTbl p2
WHERE p2.WeekID <= @WeekID
GROUP BY p2.ActID) p
ON p.ActID = a.ActID
GROUP BY a.ActTitle, p.Progress, a.PercentageofWork WITH ROLLUP
HAVING GROUPING(a.PercentageofWork)=0 OR GROUPING(a.ActTitle)=1
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you very much for your help, David.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply