SQL Server Performance

SQL Query

Discussion in 'General Developer Questions' started by sillylady, May 21, 2006.

  1. sillylady New Member

    Hi All,


    I have a table which has 4 fields (Code, Year, Amt1, Amt2).

    Code Year Amt1 Amt2
    C1 2000 1111 1111
    C1 2001 2222 2222
    C1 2002 3333 3333
    C1 2003 4444 4444

    A user will enter Code and a range of years like 2000-2003. If a user enters year between 2000 n 2003. The 1st average will be of Year 1999 and 2000 then 2000 n 2001, 2001 n 2002 and 2002 and 2003. If the data for the Year 1999 doesn't exist, it will not calculate the average of 1999-2000.


    My questions are 1st how to create such a query and what if the data for the year 1999 doesn't exist.

    Thanks
  2. mmarovic Active Member

    I am not quite sure I understand your requirements. How is average calculated based on years and two amounts. Is it average of two amounts like (amt1+amt2)/2 or it you want two averages like: (prevousYear.amt1+currentYear.amt1)/2, (previousYear.amt2+currentYear.amt2)/2 (in case there are rows for both years) ... and so on.
    Can you please clarify your requirements?
  3. sillylady New Member

    quote:Originally posted by mmarovic

    I am not quite sure I understand your requirements. How is average calculated based on years and two amounts. Is it average of two amounts like (amt1+amt2)/2 or it you want two averages like: (prevousYear.amt1+currentYear.amt1)/2, (previousYear.amt2+currentYear.amt2)/2 (in case there are rows for both years) ... and so on.
    Can you please clarify your requirements?

    There will be no rows for a single year i.e Year 2000 will have only one record. My requirement is (prevousYear.amt1+currentYear.amt1)/2.


    Thanks
  4. mmarovic Active Member

    ok, then:


    select current.year,
    case
    when previous.amt1 is null then current.amt1
    else (current.amt1 + previous.amt1)/2
    end
    from table current
    left join table previous on previous.year = current.year + 1 and previous.code = current.code
    where
    current.code = @code and
    current.year between @yearFrom and @yearTo


    [edited]

    More straight-forward code used.
  5. sillylady New Member

    quote:Originally posted by mmarovic

    ok, then:


    select current.year,
    case
    when previous.amt1 is null then current.amt1
    else (current.amt1 + previous.amt1)/2
    end
    from table current
    left join table previous on previous.year = current.year + 1 and previous.code = current.code
    where
    current.code = @code and
    current.year between @yearFrom and @yearTo


    [edited]

    More straight-forward code used.

    Thanks for your quick reply. I think I could not make you understand my question. I am repeating it again. A user will input Code and range of Years (2000 to 2002). Amt1 average calculation will start for the year 1999-2000 ((Amt1 of 1999 + Amt2 of 2000)/2), 2000-2001 and 2001-2002. If the data for the year 1999 does not exist, it will skip and calculate average for the year 2000-2001 and 2001-2002.
  6. mmarovic Active Member

    That's even simpler:


    select current.year,
    (current.amt1 + previous.amt1)/2
    from table current
    left join table previous on previous.year = current.year + 1 and previous.code = current.code
    where
    current.code = @code and
    current.year between @yearFrom and @yearTo

    Amount calculated will be null in case there is now row for year 1999.
    If it is not what you expected, please post sample resultset.
  7. sillylady New Member

    quote:Originally posted by mmarovic

    That's even simpler:


    select current.year,
    (current.amt1 + previous.amt1)/2
    from table current
    left join table previous on previous.year = current.year + 1 and previous.code = current.code
    where
    current.code = @code and
    current.year between @yearFrom and @yearTo

    Amount calculated will be null in case there is now row for year 1999.
    If it is not what you expected, please post sample resultset.

    I think your query is giving some results. But I need to test more. Hope you will help me if your help is needed.
  8. mmarovic Active Member

    I made an mistake, instead of current.amt1 it should be current.amt2.<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I think your query is giving some results. But I need to test more. Hope you will help me if your help is needed.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Of course sillylady. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />
  9. joechang New Member

    if results do not exist, do you want a row: 1999 null
    or do you want rows starting from 2000?

    if the second, then use INNER JOIN in place of LEFT JOIN
  10. mmarovic Active Member

    Joe is right, omitting the row makes more sense. If it is what you wanted then just use inner instead of left join.
  11. sillylady New Member

    Thanks for your reply. I have tested and your query giving a close result to my desired result. I am giving you a desired result example.

    Code----Year----Amt1----Amt2
    C1 ----2000----1111----1111
    C1 ----2001----2222----2222
    C1 ----2002----3333----3333
    C2 ----2003----4444----4444

    Lets say table 'Test' has the above data. A user inputs Code='C1' and Year between 2000 n 2002. Result will be something like this.

    2001----1666.5 ((Year2000.Amt1+Year2001.Amt1)/2)
    2002----2777.5 ((Year2001.Amt1+Year2002.Amt1)/2)

    There is no result for Year2000 because Year1999 does not have data. It would be good if it shows null value for the Year2000 in result.


  12. mmarovic Active Member

    quote:Originally posted by sillylady

    Thanks for your reply. I have tested and your query giving a close result to my desired result. I am giving you a desired result example.

    Code----Year----Amt1----Amt2
    C1 ----2000----1111----1111
    C1 ----2001----2222----2222
    C1 ----2002----3333----3333
    C2 ----2003----4444----4444

    Lets say table 'Test' has the above data. A user inputs Code='C1' and Year between 2000 n 2002. Result will be something like this.

    2001----1666.5 ((Year2000.Amt1+Year2001.Amt1)/2)
    2002----2777.5 ((Year2001.Amt1+Year2002.Amt1)/2)

    There is no result for Year2000 because Year1999 does not have data. It would be good if it shows null value for the Year2000 in result.
    I don't see any role for amt2 again. Based on your last specification the query is:


    select current.year,
    (current.amt1 + previous.amt1)/2
    from table current
    --If you decide to have a null row for year 2000, uncomment "left" keyword bellow
    /*left*/ join table previous on previous.year = current.year + 1 and previous.code = current.code

    where
    current.code = @code and
    current.year between @yearFrom and @yearTo

  13. sillylady New Member

    Thank you very much marovic. Your suggestion has solved my problem for now with a little modification.



  14. mmarovic Active Member

    You are wellcome.
  15. RGKN New Member

    Shouldn't it be

    previous.year = current.year - 1

    rather than plus 1?



  16. mmarovic Active Member

    Yes, that was the mistake sillylady fixed I guess.

Share This Page