Hi Experts
I have this query which works well. The only problem is that it is too long. Is it possible to shorten it?
select (case when Group_B.months <> Group_A.months OR Group_A.months is null then
Group_B.months else Group_A.months end) as [Month],
Group_A.Year as Year, Group_A.[Value] as [Value], Group_B.[Value] as [Value]
from
--Tyres
(select year(invoicedate) as Year, (case when month(invoicedate)= 1 then 'January' when month(invoicedate)= 2 then 'February'
when month(invoicedate)= 3 then 'March' when month(invoicedate)= 4 then 'April' when month(invoicedate)= 5
then 'May' when month(invoicedate)= 6 then 'June' when month(invoicedate)= 7 then 'July'
when month(invoicedate)= 8 then 'August' when month(invoicedate)= 9 then 'September' when month(invoicedate)= 10
then 'October' when month(invoicedate)= 11 then 'November' when month(invoicedate)= 12 then 'December'
end) as months, sum(total_excl_vat)as [Value]
from cb_invoices a
left outer join cust_master b
on
a.tt_product_code = b.product_code
where manage = 55040
and status not in (0,4,9)
and trans_code = 100
and year(invoicedate) = 2004
and left(tt_product_code,1) <> 9
and type_code = 'AA'
group by year(invoicedate), month(invoicedate), type_code, type_desc
) as Group_A
--Batteries
full outer join (select year(invoicedate) as Year, (case when month(invoicedate)= 1 then 'January' when month(invoicedate)= 2 then 'February'
when month(invoicedate)= 3 then 'March' when month(invoicedate)= 4 then 'April' when month(invoicedate)= 5
then 'May' when month(invoicedate)= 6 then 'June' when month(invoicedate)= 7 then 'July'
when month(invoicedate)= 8 then 'August' when month(invoicedate)= 9 then 'September' when month(invoicedate)= 10
then 'October' when month(invoicedate)= 11 then 'November' when month(invoicedate)= 12 then 'December'
end) as months, sum(total_excl_vat)as [Value]
from cb_invoices a
left outer join cust_master b
on
a.tt_product_code = b.product_code
where manage = 55040
and status not in (0,4,9)
and trans_code = 100
and year(invoicedate) = 2004
and type1_code = 'DAA'
group by year(invoicedate), month(invoicedate), type_desc) as Group_B
on Group_B.months = Group_A.months
order by [Month]
Thanx
Kind Regards
Bookmarks