/*
Fields: d_id (AutoNumber), D_je (currency type), D_SXRQ (date type)
The table data is as follows:
d_id d_je D_SXRQ
1 20 2009-5-1
2 30 2009-5-6
3 10 2009-6-5
4 15 2009-6-25
5 5 2009-8-8
6 50 2009-9-9
7 20 2010-5-1
8 40 2010-5-6
9 15 2010-6-5
10 25 2010-6-25
11 55 2010-8-8
12 65 2010-9-9
Want to count by month:
If you choose to get 2009
Months 1 2 3 4 5 6 7 8 9 10 11 12
Amount 0 0 0 0 50 25 0 5 50 0 0-0
If you choose 2010 to get:
Months 1 2 3 4 5 6 7 8 9 10 11 12
Amount 0 0 0 0 60 40 0 55 65 0 0-0
*/
Select year (D_SXRQ) as Y,
Sum (IIF (Month (D_SXRQ) = 1, d_je, 0)) as M01,
Sum (IIF (Month (D_SXRQ) = 2, D_je, 0)) as M02,
Sum (IIF (Month (D_SXRQ) = 3, D_je, 0)) as M03,
Sum (IIF (Month (D_SXRQ) = 4, D_je, 0)) as M04,
Sum (IIF (Month (D_SXRQ) = 5, D_je, 0)) as M05,
Sum (IIF (Month (D_SXRQ) = 6, D_je, 0)) as M06,
Sum (IIF (Month (D_SXRQ) = 7, D_je, 0)) as M07,
Sum (IIF (Month (D_SXRQ) = 8, d_je, 0)) as M08,
Sum (IIF (Month (D_SXRQ) = 9, d_je, 0)) as M09,
Sum (IIF (Month (D_SXRQ) = ten, D_je, 0)) as M10,
Sum (IIF (Month (D_SXRQ) = one, D_je, 0)) as M11,
Sum (IIF (Month (D_SXRQ) = A, D_je, 0)) as M12
From D_fy
Group by year (D_SXRQ);