November 18, 2011 at 3:04 pm
I need to calculate a column in a view. It's done in Access by using a function "fnChallengeBudget" that takes "ProjectID" and returns a dollar amount:
Dim ChallengeDate As Date
ChallengeDate = Nz(DMax("cptransdate", _
"tblcptransaction", _
"CPProjectID=" & ProjectID & " AND CPTransType=2 AND INSTR(nz(CPTransComment,""""),'30% Challenge') > 0"), Date + 700)
fnChallengeBudget = Nz(DSum("cptransamount", _
"tblcptransaction", _
"CPProjectID=" & ProjectID & " AND CPTransType=2 AND CPTransdate <= #" & ChallengeDate & "#"), 0)
I'd like to calculate it inline in the view rather than using a user-defined function (since I think it can be done, and it's only used in one place....)
Anybody wanna take a go at it?
Jim
November 21, 2011 at 6:42 am
You can use that into a outer apply (derived table) to get those values for multiple projects in the same query.
SELECT
DATEADD(D , 700 ,
MAX(CASE WHEN CPTransComment LIKE '%30[%] Challenge%'
THEN cptransdate
ELSE NULL
END)) AS Max_cptransdate
, SUM(CASE WHEN CPTransdate < @ChallengeDate THEN cptransamount
ELSE NULL
END) AS Sum_cptransamount
FROM
dbo.tblcptransaction
WHERE
CPProjectID = @ProjectId
AND CPTransType = 2
November 21, 2011 at 8:18 am
Ninja,
Thank you so much for your help. I really appreciate it.
Your code seems to miss the interaction between the first "DMAX" and the second. Note that the result of the first DMAXis used as the limit on the DXUM. It's a two-part inter-related set. That was the hard part for me. So, where you've used "@ChallengeDate", it needs to use the max_challengedate from the outer set.
For each @Project, the admin posts an entry called "30% Challenge", which makes the project manager responsible for bringing the project in 30% under the sum of budgets posted up to that date. Thus, the first domain aggregate function finds the date the "30% Challenge" was posted, and the second domain aggregate adds up the budgets up to that date. I don't know how to nest those.
Sorry I wasn't clear earlier.
Jim
November 21, 2011 at 8:35 am
Are you running this for a single projects or all your projects at the same time?
November 22, 2011 at 3:26 am
Hello,
yes, it can be done in a SQL statement, the problem is to obtain the date first and then sum.
First of all you can do it using a function like that,
CREATE FUNCTION fnChallengeBudget(@CPProjectID AS INTEGER) RETURNS DECIMAL(18,6) AS
BEGIN
DECLARE @maxDate AS DATETIME
DECLARE @sumAmount AS DECIMAL(18,6)
SELECT @maxDate = MAX(cptransdate)
FROM tblcptransaction
WHERE CPProjectID = @CPProjectID AND CPTransType = 2
AND CPTransComment LIKE '%30[%] Challenge%'
SET @maxDate = ISNULL(@maxDate, getdate() + 700)
SELECT @sumAmount = SUM(cptransamount)
FROM tblcptransaction
WHERE CPProjectID = @CPProjectID AND CPTransType = 2 AND CPTransdate <= @maxDate
RETURN @sumAmount
END
it is direct translation from your VB code.
If you want to do it without using a function yo can code something like that
WITH X AS (
SELECT CPProjectID, MAX(cptransdate) AS maxDate
FROM tblcptransaction
WHERE CPTransType = 2 AND CPTransComment LIKE '%30[%] Challenge%'
GROUP BY CPProjectID
)
SELECT CPProjectID, SUM(cptransamount)
FROM tblcptransaction
LEFT JOIN X ON X.CPProjectID = tblcptransaction.CPProjectID
WHERE CPTransType = 2 AND CPTransdate <= ISNULL(maxDate, GETDATE() + 700)
Now you can write two solutions to your problem, code and test both to choose the best.
Regards,
Francesc
November 22, 2011 at 6:26 am
Oops,
I forgot two things:
- the last query needs a GROUP BY clause.
- GETDATE() is not equivalent to DATE() but to NOW(), you should take care of that. Surely it is not important, supposing that DATE() + 700 plays the role of infinite.
Francesc
November 22, 2011 at 6:47 am
Since I'm used to Access' limitations on aggregates as subqueries, I'm used to writing these as separate queries and referencing them as views. So that's what I did. Ifeel like SS will optimize out the stupidity of it all.
I created view 'qryBAChallengeDatebyProjectID' that returns a ProjectID and a Challenge date for every project that has one (or two years out into the future for those that do not...)
I then created a view called 'qryBACPChallengeBudget' that joined tblCPTrans to the view 'qryBAChallengeDatebyProjectID', Summing all budget entries prior to the challenge date.
I use that view in higher-level queries that need the challenge budget.
It's neat, clean, and verbose, but it works.
I'm sure I could have nested the SQL rather than creating the views, and I will come back and try it, but I got it going yesterday, and that allowed me to move on.
Does anyone think using a scalar stored procedure is more efficient? Doesn't seem like it to me (which is why I didn't go that route in the first place...) But, I have been wrong before (just today, my wife says....) and about SQL Server, I've been wrong frequently.
I've included the views here. Sorry I can't seem to get the tools I use to do any decent formatting.
Thanks for your help guys!
USE [CPAS]
GO
/****** Object: View [dbo].[b]qryBAChallengeDatebyProjectiID[/b] Script Date: 11/22/2011 08:41:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[qryBAChallengeDatebyProjectiID]
AS
SELECT CPProjectID, MAX(CPTransDate) AS CPChallengeDate
FROM dbo.tblCPTransaction
WHERE (CPTransComment LIKE '*30[%] Challenge*')
GROUP BY CPProjectID
USE [CPAS]
GO
/****** Object: View [dbo].[b]qryBACPChallengeBudget[/b] Script Date: 11/22/2011 08:43:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[qryBACPChallengeBudget]
AS
SELECT dbo.tblCPTransaction.CPProjectID, CAST(SUM(dbo.tblCPTransaction.CPTransAmount) AS money) AS ChallengeBudget
FROM dbo.tblCPTransaction LEFT OUTER JOIN
dbo.qryBAChallengeDatebyProjectiID ON dbo.tblCPTransaction.CPProjectID = dbo.qryBAChallengeDatebyProjectiID.CPProjectID
WHERE (dbo.tblCPTransaction.CPTransDate < ISNULL(dbo.qryBAChallengeDatebyProjectiID.CPChallengeDate, DATEADD(d, 700, GETDATE())))
GROUP BY dbo.tblCPTransaction.CPProjectID
HAVING (dbo.tblCPTransaction.CPProjectID IS NOT NULL)
Jim
November 22, 2011 at 6:48 am
Ninja's_RGR'us (11/21/2011)
Are you running this for a single projects or all your projects at the same time?
Part of a larger query that reports on all projects at once.
Jim
November 22, 2011 at 6:59 am
JimS-Indy (11/22/2011)
Ninja's_RGR'us (11/21/2011)
Are you running this for a single projects or all your projects at the same time?Part of a larger query that reports on all projects at once.
Use the With version up there and it should work just fine for what you need.
November 22, 2011 at 9:05 am
Hello,
It's neat, clean, and verbose, but it works.
ha ha, sure that neat, clean and verbose are big defects, but despite of that it still works 😀
Does anyone think using a scalar stored procedure is more efficient? Doesn't seem like it to me
I think the same, but anyway testing it is cheap.
I will join the Ninja's suggestion, you can merge all this code in only one view and test what is best. Or even you can create these two views inside Access, but surely you will gain nothing doing it.
And seeing your code I perceive that the condition "AND CPTransType = 2" is lost, hope it will be unimportant.
Regards,
Francesc
November 22, 2011 at 9:28 am
Thanks for that catch. I'll fix it.
I'm currently stuck on a union query that's lockin up for some reason.
I am enjoying the learning, though.
Again, thanks for the catch!
Jim
November 22, 2011 at 9:36 am
JimS-Indy (11/22/2011)
Thanks for that catch. I'll fix it.I'm currently stuck on a union query that's lockin up for some reason.
I am enjoying the learning, though.
Again, thanks for the catch!
Union or union all?
Union all = Qry1 + Qry2
UNON = SELECT DISTINCT FROM (Qry1 + Qry2).
Massvie difference in the results and the workload.
November 22, 2011 at 10:02 am
Used the WITH version. Worked fine. Both my ugly version and the 'WITH' version produce 2633 rows in less than a second.
Thanks again.
Jim
November 22, 2011 at 10:11 am
Ninja's_RGR'us (11/22/2011)
JimS-Indy (11/22/2011)
Thanks for that catch. I'll fix it.I'm currently stuck on a union query that's lockin up for some reason.
I am enjoying the learning, though.
Again, thanks for the catch!
Union or union all?
Union all = Qry1 + Qry2
UNON = SELECT DISTINCT FROM (Qry1 + Qry2).
Massvie difference in the results and the workload.
Using UNION.
Project expenses come from 7 different sources, budgets from two. My massive query assigns each transaction a "week" number, then sums by Project, Discipline, WeekNbr. Sadly, there may be budgets without expenses in a given Project, Discipline, and/or Week. Similarly, there may be expenses without budgets. To be sure I get them all, I perform a union query on all the 'sum' queries to obtain a set that contains every possible Project/Disciplline/WeekNbr combination so I don't miss any of them when I flatten the whole thing for reporting (Pivot Table/Chart) applications. The final query has probably 5-deep 8-wide subqueries. And they have to be evaluated twice.
Using Access, the query takes 2 hours to run. I'm trying to convert it to SS and run it on the server (I only run it daily, at night, creating a 'shadow' table with the result.)
Jim
November 22, 2011 at 10:27 am
Sounds like my standard reports here :-D.
I still don't see how union is usefull here. You need to add data here, not de-deplicate it.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply