Select Between Period and Year

[violette]

I need to get data between year and period. 1 year have four periods.
i.e; year = 2010 period = 1,2,3,4
the problem is on the period part where i could not select second period smaller from the first period.
for example, i choose year=2010 to year=2011
and period=4 to period=2
the result should display year=2010 period=4,year=2011 period=1,year=2011 period 2. but when i run the code, it returns nothing. however if i choose year=2010 to year=2011 and period=1 to period=4, the code works fine.

here's the example of my code;

SELECT SUM(ma5.alert_value) 
FROM company_alert ma5 
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code) 
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='VPAEH' AND mc5.sector_code=ref.sector_code AND ma5.year_id BETWEEN '2009' AND '2010' AND ma5.period_id BETWEEN 'q3' AND 'q1'

which part should i fix to make it right? thanks all :)

[urtrivedi]

SELECT SUM(ma5.alert_value) 
FROM company_alert ma5 
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code) 
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='VPAEH' AND mc5.sector_code=ref.sector_code AND 
ma5.year_id BETWEEN '2009' AND '2010' 
AND (
ma5.period_id BETWEEN 'q3' AND 'q1' 
   or 
ma5.period_id BETWEEN 'q1' AND 'q3' 
)

I think between will work proper only for number not string like q1 q3, you must compare like 1, 3 etc

any way I have added or condition for ur period, Parenthesis are very important

[violette]

hi urtrivedi, thank you so much for your reply! now the code works fine and give what i want. thanks again :)

[violette]

hi guys, i have to re-open this thread for further discussion because i just realized there is a little problem. the code works great when i run it. however i just noticed that,if i choose quarter 3 year 2009 until quarter 1 year 2010, the result does not return quarter 4 for year 2009.

SELECT ma5.alert_value,period_id,year_id
FROM company_alert ma5
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code)
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='DSO' AND mc5.sector_code='s06' AND ma5.year_id BETWEEN '2009' AND '2010' AND (ma5.period_id BETWEEN '3' AND '1' OR ma5.period_id BETWEEN '1' AND '3')
GROUP BY year_id,period_id

result for above query;
year_id period_id
2009 3
2010 1
2010 2
2010 3

it should return quarter 4 for year 2009 but it just skip and display quarter 1 for year 2010. how can i fix this? thanks guys.

[urtrivedi]

I think this must work

SELECT ma5.alert_value,period_id,year_id
FROM company_alert ma5
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code)
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='DSO' AND mc5.sector_code='s06' 
AND (

ma5.year_id*10+ma5.period_id between 2009*10+3 and 2010*10+1 

)


GROUP BY year_id,period_id

2009*10+3 and 2010*10+1 you just change parameter of year period through ur front end

[violette]

I think this must work

SELECT ma5.alert_value,period_id,year_id
FROM company_alert ma5
LEFT JOIN qiddb.company_info mb5 ON (mb5.stock_code=ma5.stock_code)
LEFT JOIN ref_sector mc5 ON (mc5.sector_code=mb5.sector_code)
WHERE ma5.alert_id='DSO' AND mc5.sector_code='s06' 
AND (

ma5.year_id*10+ma5.period_id between 2009*10+3 and 2010*10+1 

)


GROUP BY year_id,period_id

2009*10+3 and 2010*10+1 you just change parameter of year period through ur front end

this is the best solution. thanks again :)