July 28, 2010 at 6:15 am
I am using SQL Server 2008. I have to calculate the running total in different columns. I have created the following matrix
TaskSub TasksDescriptionWork DateTargated Actual
HRMaterialPhysical WorkCostHRMaterialPhysical WorkCost
ARKG-01-BCut of WallCement13-Jul-10 60 24000 60 24000
14-Jul-10 30 12000 30 12000
15-Jul-10 50 20000 40 16000
Excavation12-Jul-10 2500 2000
14-Jul-10 2500 2500
Intake ChamberSkilled Labor12-Jul-105 25005 2500
13-Jul-105 25007 3500
14-Jul-105 25005 2500
16-Jul-103 15003 1500
Unskilled Labor12-Jul-106 18006 1800
13-Jul-107 21007 2100
14-Jul-106 18006 1800
16-Jul-107 21007 2100
Iron Bar12-Jul-10101000101000
13-Jul-1055005500
14-Jul-1033004400
16-Jul-1033003300
ARKG-01-CRRMUnskilled Labor17-Jul-107 21007 2100
18-Jul-105 15005 1500
Skilled Labor12-Jul-106 30005 2500
13-Jul-105 25004 2000
14-Jul-105 25005 2500
16-Jul-103 15003 1500
Using the following Query I want the commulate sum for each Sub_Task
SELECT a.W_Date, a.Description, a.Status, SUM(b.HR) AS HR, SUM(b.Material) AS Material, SUM(b.Physical_Work) AS Physical_Work, SUM(b.Cost) AS Cost,
tblSub_Task.Sub_Tasks, tblTasks.Project_Task
FROM tblProg AS a INNER JOIN
tblProg AS b ON b.W_Date <= a.W_Date AND a.Description = b.Description AND a.Status = b.Status INNER JOIN
tblSub_Task ON a.Sub_Task_ID = tblSub_Task.Sub_Task_ID AND b.Sub_Task_ID = tblSub_Task.Sub_Task_ID INNER JOIN
tblTasks ON tblSub_Task.Task_ID = tblTasks.Task_ID
GROUP BY a.Description, a.Status, a.W_Date, tblSub_Task.Sub_Tasks, tblTasks.Project_Task
ORDER BY a.Description, a.Status DESC, a.W_Date
But it does not calculate running total for each Sub_Task. I need the matrix (Output) like
TaskSub TasksDescriptionWork DateTargated Actual
HRMaterialPhysical WorkCostHRMaterialPhysical WorkCost
ARKG-01-BCut of WallCement13-Jul-10 60 24000 60 24000
14-Jul-10 90 36000 90 36000
15-Jul-10 140 56000 130 52000
Excavation12-Jul-10 2500 2000
14-Jul-10 5000 4500
Intake ChamberSkilled Labor12-Jul-105 25005 2500
13-Jul-1010 500012 6000
14-Jul-1015 750017 8500
16-Jul-1018 900020 10000
Unskilled Labor12-Jul-106 18006 1800
13-Jul-1013 390013 3900
14-Jul-1019 570019 5700
16-Jul-1026 780026 7800
Iron Bar12-Jul-10 101000101000
13-Jul-10151500151500
14-Jul-10181800191900
16-Jul-10212100222200
ARKG-01-CRRMUnskilled Labor17-Jul-107 21007 2100
18-Jul-1012 360012 3600
Skilled Labor12-Jul-106 30005 2500
13-Jul-1011 55009 4500
14-Jul-1016 800014 7000
16-Jul-1019 950017 8500
Apart from this how can I highlight un matched values under Targated & Actual for different columns.
Please help
July 28, 2010 at 6:55 am
You might have an easier time doing your running values in Reporting Services
=RunningValue(Fieldname.value, Sum(or other aggregation), Scope (Generally your group name or data set name))
July 28, 2010 at 7:03 am
Instead of calculating a running total in T-SQL, you can use the RunningValue function in your report to calculate the running totals.
For highlighting certain values, just add an expression to a property of choice of the textbox. For instance, if you want to show Actual to be listed in bold if it doesn't match Targeted, add an expression
=IIf(Fields!Targeted.Value <> Fields!Actual, "Bold", "Normal")
to the FontWeight property of the textbox.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply