Post reply

TSQL Help

  • February 15, 2011 at 9:17 pm

    #231394

    Help. Trying to avoid a cursor to self join a table and get obtain a two year average of one

    of the elements.

    CREATE TABLE LUNCHES

    (

    StateVARCHAR(10)

    ,CountyVARCHAR(10)

    ,CityVARCHAR(10)

    ,Lunch_Year VARCHAR(10)

    ,Lunch_Month VARCHAR(10)

    ,Actual_Lunch_DATE DATE

    ,Served DECIMAL(12, 4)

    )

    INSERT INTO LUNCHES

    SELECT 'FL', 'Marion', 'Ocala', '2010', '06', '2010_06-01', 38.0000

    UNION ALL

    SELECT 'FL', 'Marion', 'Citrus', '2009', '06', '2009_06-01', 15.0000

    UNION ALL

    SELECT 'FL', 'Marion', 'Ocala', '2008', '06', '2008_06-01', 22.0000

    UNION ALL

    SELECT 'FL', 'Marion', 'Citrus', '2010', '03', '2006_03-01', 10.0000

    UNION ALL

    SELECT 'FL', 'Marion', 'Ocala', '2010', '04', '2003_04-01', 42.0000

    UNION ALL

    SELECT 'FL', 'Marion', 'Citrus', '2010', '01', '2010_01-01', 18.0000

    What I want to do is is self join a running two year average based on the Actual_lunch_date

    elment, grouped by the state, county and city

    I thought about going the CTE route, but didn't think that would work. The kicker

    is that the table has over 4 million rows

    Any suggestion's would be greatly appreciated as I don't want to do RBAR (where is Jeff when I need him)

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 16, 2011 at 1:42 am

    #1287484

    Hi,

    Hope this solves your problem.

    select sum(served)/COUNT(*),State,County,City,Lunch_Year from LUNCHES Group by State,County,City,Lunch_Year

    having COUNT(*) = 2 /* this can changed based on your requirement */

  • February 16, 2011 at 7:49 am

    #1287686

    That doesn't take into the need of it being a two year average based on the Actual_lunch_date. However it is very interesting.

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 16, 2011 at 8:02 am

    #1287695

    Marvin,

    Can you please post expected results from that sample data - to clear up a few things that are bugging me?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • February 16, 2011 at 8:24 am

    #1287716

    MM

    For line one, I'm expecting to get the return of 30.

    I get that result by adding the number of lunches served within the two year period of 2010-06-01 which results

    selecting rows that have a actual_lunch date between 2010-06-01 and 2008-06-01

    for FL, Marion, Ocala

    For line two I'm expecting 15 as it is the only line that meets the two year criteria

    Now that I've had a full night of rest after a 16 hour day, I'm thinking function.

    Thanks

    MD

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 16, 2011 at 9:21 am

    #1287754

    Would this work for you?

    select State,County,City, lunch_year, lunch_month, actual_lunch_date, served

    ,(select avg(l.served) from lunches l where l.state = lunches.state and l.county = lunches.county and l.city = lunches.city

    and l.actual_lunch_date between dateadd(yy, -2, lunches.actual_lunch_date) and lunches.actual_lunch_date group by l.state, l.county, l.city)

    as TwoYearAvg

    from lunches

    C


    Colleen M. Morrow
    Cleveland DBA

  • February 16, 2011 at 9:29 am

    #1287758

    What kind of function? Table-valued (multi-statement or inline) or Scalar?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 16, 2011 at 9:46 am

    #1287768

    I'm going scalar. In fact I've already finished it and it works great. Definately beats the cursor and RBAR. Jeff Moden would be proud of me 🙂

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 16, 2011 at 9:51 am

    #1287770

    Colleen, that would do it as well. I had something like that at first but I just realized I didn't qualify my actual lunch dates correctly.

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 16, 2011 at 9:52 am

    #1287772

    Do you mind posting your code? I am very curious to see how you solved this. I suspect that Jeff may want to have a look as well.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

    • Viewing 10 posts - 1 through 9 (of 9 total)

    You must be logged in to reply to this topic. Login to reply