ON THE FLY
-
Hi ,
i need help on that .i have a stored procedure which works fine,the only thing i need is to calculate the subtotals and Grand Total FOR ALL THE THREE YEARS for the maintenance cost on the FLY and return it to the application.
CREATE PROCEDURE FMPSGetMaintenanceCost
@LEA_Code Varchar(4)
AS
declare @cyear INT
SET @CYear = YEAR(GETDATE())
SELECT Line_Number,
Maintenance_Type_Name,
Maintenance_Type_Level,
Rollup_To_Line_Number AS Parent,
@cyear as CurrentYear,
NULL as fPreviousyear,
NULL as sPreviousyear,
F.Maintenance_Cost
FROM Ref_Maintenance_Type RM
INNER JOIN FMPS_Maintenance_Cost F
ON F.Maintenance_Type_ID = RM.Maintenance_Type_ID
WHERE @cyear = F.School_Year
Line # FY 2004 FY 2005 FY 2006 Custodial services 1561 Professional services 1562 Clerical services 1563 Other salaries 1564 Contracted services 1565 Supplies 1565 Other expenses sub-total Maintenance of Grounds 1591 Professional services 1592 Clerical services 1593 Other salaries 1594 Contracted services 1595 Supplies 1596 Other expenses sub-total Grand Total -
I have an idea, post sample data, sample results, and what YOU have done so far and what results YOU have gotten so far. Then we can help you figure out what is wrong instead of doing your work for you.
-
Read up on WITH ROLLUP in BOL....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Go for broke... read up on WITH CUBE in BOL... that's where they teach you about the GROUPING function that works both with ROLLUP and CUBE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is an extremely simple example of what you might try.
I am using a pass-thru query in Access to display the number of members updated within the past five days AND they also wanted to see the grand total, all in the same query...
CREATE PROC [dbo].[procUpdatesPending]
AS
DECLARE
@today smalldatetime
SELECT
@today = dbo.fnNoTime(GETDATE())
SELECT
dbo.fnNoTime(UpdatedDateTime) AS LastUpdated,
COUNT(*) AS Members,
'A' AS TheSort
INTO
#temp
FROM
PERSON
WHERE
dbo.fnNoTime(UpdatedDateTime) BETWEEN DATEADD(d,-5,@today) AND @today
GROUP BY
dbo.fnNoTime(UpdatedDateTime)
UNION
SELECT
NULL,
COUNT(*) AS Members,
'B' AS TheSort
FROM
PERSON
WHERE
dbo.fnNoTime(UpdatedDateTime) BETWEEN DATEADD(d,-5,@today) AND @today
SELECT
LastUpdated,
Members
FROM
#temp
ORDER BY
TheSort
DROP TABLE #temp
BTW - the dbo.fnNoTime() is a UDF that strips out the time portion.
-
You might want to post your code for the UDF dbo.fnNoTime() as I doubt Best will figure out how to write it.
-
I posted in another forum just today, take a look at
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=367982
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply