select a.Item_number, a.customer_group_id, a.submitted_year, a.submitted_mth,sum(a.sumOfSMFQty) as sumOfSMFQty

from

(select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 1, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 1, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period

UNION

select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 2, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 2, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period

UNION

select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 3, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS Submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 3, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period

UNION

select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 4, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS Submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 4, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period

UNION

select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 5, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 5, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period

UNION

select smf.Item_number, cl.customer_group_id,
'F'+Right(DatePart(yyyy, DateAdd(m, 6, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2) AS submitted_Year,
'P'+Replace(Str(DatePart(m, DateAdd(m, 6, '20'+Replace(smf.fiscal_year,'F','')+Replace(smf.fiscal_period,'P','')+'01' )), 2), ' ', '0') as submitted_mth,
sum(quantity)as sumOfSMFQty

from
shipment_month_figure smf left join customer_list cl
on cl.customer_number= smf.customer_number
and is_invoiced='invoiced'

group by item_number, cl.customer_group_id, fiscal_year, fiscal_Period


) a

Group by a.Item_number, a.customer_group_id, a.submitted_year, a.submitted_mth



原來咁樣做可以囉到六個月既total數....好crazy