Convert Access function to SQL subquery

  • 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

  • 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

  • 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

  • Are you running this for a single projects or all your projects at the same time?

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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