On 2012-04-20 10:11, Nols Smit wrote:

> I have a case where I extract data with the following SQL:
>
> Select Gender_Name, Race_Name, Fop_Name, count(ID) as Number_2008_2009 from V_Register_Fop
> where RegStatus_ID = 1 and Register_RegDate>= '01.04.2008' and Register_RegDate<= '01.04.2009'
> group by Gender_Name, Race_Name, Fop_Name
>
> But then I have to execute the SQL again for the next bookyear up to the current one.
>
>
> Is it posible to create one SQL select statement where I can have the result as:
>
> GENDER_NAME, RACE_NAME, FOP_NAME, NUMBER_2008_2009, NUMBER_2009_2010, NUMBER_2010_2011, NUMBER_2011_2012

Hi Nols,

One solution is to use variant sums:

Select Gender_Name, Race_Name, Fop_Name,
sum(
iif(Register_RegDate >= '01.04.2008' and Register_RegDate <=
'01.04.2009', 1, 0)
) as Number_2008_2009,
sum(
iif(Register_RegDate >= '01.04.2009' and Register_RegDate <=
'01.04.2010', 1, 0)
) as Number_2009_2010,
sum(
iif(Register_RegDate >= '01.04.2010' and Register_RegDate <=
'01.04.2011', 1, 0)
) as Number_2010_2011,
sum(
iif(Register_RegDate >= '01.04.2011' and Register_RegDate <=
'01.04.2012', 1, 0)
) as Number_2011_2012

from V_Register_Fop
where RegStatus_ID = 1 and Register_RegDate >= '01.04.2008' and
Register_RegDate <= '01.04.2009'
group by Gender_Name, Race_Name, Fop_Name

In other words, you count a record as '1' to the sum if the date falls
within a certain period, and as '0' otherwise.
Don't take it personally, but the idea of your query is rather
short-sighted, 'cause it requires changing the query every year (a new
column will be necessary). I'd suggest adding the year to the group by
clause. If you don't have a separate year column, you can always extract
the year from the date in a subselect statement and group by that extra
column in the outer query. Something like this:

select Gender_Name, Race_Name, Fop_Name, TheYear, count(*) from (
Select Gender_Name, Race_Name, Fop_Name, extract(YEAR from
Register_RegDate) as TheYear
from V_Register_Fop
)
group by Gender_Name, Race_Name, Fop_Name, TheYear

I hope this helps.

Regards
Tomasz

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__