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
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?
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
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.
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.
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.
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.
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 />
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
Joe is right, omitting the row makes more sense. If it is what you wanted then just use inner instead of left join.
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.
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
Thank you very much marovic. Your suggestion has solved my problem for now with a little modification.
