Re: UDF and SQL2000 - Why doesn't this work?

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/13/04 

Date: Sat, 14 Aug 2004 01:02:15 +0200

On Fri, 13 Aug 2004 15:38:34 -0400, "Calvin X" <spam
freerobotno_spammingdrone@rotsnail.com> wrote:

>Hi Everyone,
>I am having some problems getting this user defined function to return data
>that I need. Basically This is what I want to do - I have created a query
>that breaks down values for a period start and end into a daily value and I
>want to loop through these values and sum the total value based on the
>period that is passed to the function. The period that is passed is most
>ofter quite different than the period that is defined for the query values
>that are being broken into daily values.
>
>The loop only seems to run through once and then it exits returning either
>NULL or the correct value for one day. When it returns null I run the same
>query against the data source and it gives me the value I need.
>
>Ay help would be great thanks,
>
>Calvin X
(snip)

Hi Calvin,

I'm trying to understand what you're trying to accomplish, but without
knowing the data returned in qryUtVariablesDailyVal and the output you'd
like to get, it gets hard.

If I don't misunderstand your requirements, you can accomplish this lots
easier (and quicker) without looping:

SELECT SUM(DailyVal)
FROM qryUtVariablesDailyVal
WHERE VariableCode = @Var
AND StartDate < @PeriodEnd
AND EndDate >= @PeriodStart
(untested)

Note the reversal of start and end dates - this will include all rows from
the table with the ugly name unless they end before or start after the
specified period.

If this doesn't return the data you need, please post the following:

1. Structure of your table, as DDL (i.e. CREATE TABLE statements; make
sure that all constraints are included, but omit columns that are
irrelevant to the problem);
2. Sample data that illustrates the problem, posted as INSERT statements
(so that I can cut and paste both the DDL and the INSERT statements to
recreate the sample data in my test DB);
3. Expected output from the sample data supplied;
4. A description of the business problem you;re trying to solve.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Extremely complicated problem :(
    ... >their associated charges, payments and adjustments + the payer name ... If you do want help writing the query, ... Sample data that is illustrative of your problem. ... Expected output, based on the sample data given; ...
    (microsoft.public.sqlserver.programming)
  • RE: SELECT stmt question
    ... Can you post some DDL, sample data and expected result? ... "joe" wrote: ... > query. ...
    (microsoft.public.sqlserver.programming)
  • Re: New to SQL and trying multiple table joins
    ... > Can't be certain of how to write this query for you without DDL (CREATE ... Sample data is no issue, but I was worried about the DDL. ... in the CurrentJob the ODBC manager always asks me to select keys.. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedres
    ... If you provide DDL+ sample data + expected output so it will be help us to ... Is there any config that may have been changed? ...
    (microsoft.public.sqlserver.server)
  • Re: Why doesnt this work?
    ... says that no shop_person from this subquery may be equal - of course, ... Begin by posting your DDL ... Without DDL and sample data, with only a query and a "it does not work!", ...
    (microsoft.public.sqlserver.programming)