| Author |
Topic |
|
ricky_1605
Starting Member
30 Posts |
 Posted - 2011-04-19 : 05:29:18
|
| Hey guysPlease tell me how to use case and between in where condition. This query is to find sum of data for the financial year i.e. April to March. The query is below:WHERE vadodara.id BETWEEN (CASE WHEN CONVERT(INT, @month) IN (01,02,03) THEN (CONVERT(INT, (@year + '04')) - 100)WHEN CONVERT(INT, @month) IN (04,05,06,07,08,09,10,11,12) THEN CONVERT(INT, (@year + '04')) ELSE 0 END AND CONVERT(INT, (@year + @month)))please tell me where am i going wrong.Nipun Chawla |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-19 : 05:32:29
|
what is the data type for @month & @year ?Why @year + '04' ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-19 : 06:01:03
|
| WHERE vadodara.id BETWEEN ( CASE WHEN CONVERT(INT, @month) IN ( 01, 02, 03 ) THEN ( CONVERT(INT, ( @year + '04' )) - 100 ) WHEN CONVERT(INT, @month) IN ( 04, 05, 06, 07, 08, 09, 10, 11, 12 ) THEN CONVERT(INT, ( @year + '04' )) ELSE 0 END ) AND ( CONVERT(INT, ( @year + @month )) )--------------------------http://connectsql.blogspot.com/ |
|
|
|
ricky_1605
Starting Member
30 Posts |
Posted - 2011-04-19 : 08:13:14
|
| Datatype of @month & @year is varchar. @year is value input by user for the report. +'04' because financial year is from april(04) to march.Nipun Chawla |
|
|
|
ricky_1605
Starting Member
30 Posts |
Posted - 2011-04-19 : 08:15:12
|
quote:
Originally posted by lionofdezert
WHERE vadodara.id BETWEEN ( CASE WHEN CONVERT(INT, @month) IN ( 01, 02, 03 ) THEN ( CONVERT(INT, ( @year + '04' )) - 100 ) WHEN CONVERT(INT, @month) IN ( 04, 05, 06, 07, 08, 09, 10, 11, 12 ) THEN CONVERT(INT, ( @year + '04' )) ELSE 0 END ) AND ( CONVERT(INT, ( @year + @month )) )--------------------------http://connectsql.blogspot.com/
Not getting proper results if user inputs @month = (10,11,12), from (04,05,06,07,08,09) the result is correct.Nipun Chawla |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-19 : 09:15:59
|
what do you want really ?from the begin of financial period until the selected period (@year / @month) ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ricky_1605
Starting Member
30 Posts |
Posted - 2011-04-19 : 23:43:29
|
quote:
Originally posted by khtan
what do you want really ?from the begin of financial period until the selected period (@year / @month) ?
KH[spoiler]Time is always against us[/spoiler]
Yes, suppose if the user selects to see the report of February 2011 then it should show the data from April 2010 to January 2011.Nipun Chawla |
|
|
|
|
|
|
Case and between in where condition