颜色部分都是相同的。
declare @Startmonth datetime;
declare @Endmonth datetime;
set @Startmonth='2012-1-1';
set @Endmonth='2012-8-8';
declare @month nvarchar(500);
set @month='[2012-01],[2012-02],[2012-03],[2012-04],[2012-05]';
declare @strSql nvarchar(4000);
select DepartmentMark,sum(InvoiceMoneyRMB) InvoiceMoneyRMB,InvoiceConfirmDate
from (
--跟据销售单号，关联到部门
select tbCN.DepartmentMark,tb1.InvoiceMoneyRMB,tb1.InvoiceConfirmDate
from (
--销售单号对应的已开票的金额
SELECT[OrderNo]
,(select top 1 [ExchangeRate] from [TM_ExchangeRate] where [ExchangeDate]<=[TT_Invoice].InvoiceDate and [TT_Invoice].[CurrencyTypeID]=[CurrencyTypeID] order by [ExchangeDate] Desc)*[SubtoalMoney] as InvoiceMoneyRMB
,CONVERT(varchar(7) , [InvoiceConfirmDate], 23 ) InvoiceConfirmDate
FROM [TT_InvoiceDetail]
left join [TT_Invoice] on [TT_InvoiceDetail].InvoiceNo=[TT_Invoice].InvoiceNo
where [TT_Invoice].IsInvoiceConfirm=1 and InvoiceConfirmDate>=@Startmonth and InvoiceConfirmDate<=@Endmonth
) as tb1
left join TT_ContractNotice as tbCN on tb1.OrderNo=tbCN.OrderNo
) as tb2 group by DepartmentMark,InvoiceConfirmDate
SELECT DepartmentMark
,[2012-01],[2012-02],[2012-03],[2012-04],[2012-05]
FROM
(
select DepartmentMark,sum(InvoiceMoneyRMB) InvoiceMoneyRMB,InvoiceConfirmDate
from (
--跟据销售单号，关联到部门
select tbCN.DepartmentMark,tb1.InvoiceMoneyRMB,tb1.InvoiceConfirmDate
from (
--销售单号对应的已开票的金额
SELECT[OrderNo]
,(select top 1 [ExchangeRate] from [TM_ExchangeRate] where [ExchangeDate]<=[TT_Invoice].InvoiceDate and [TT_Invoice].[CurrencyTypeID]=[CurrencyTypeID] order by [ExchangeDate] Desc)*[SubtoalMoney] as InvoiceMoneyRMB
,CONVERT(varchar(7) , [InvoiceConfirmDate], 23 ) InvoiceConfirmDate
FROM [TT_InvoiceDetail]
left join [TT_Invoice] on [TT_InvoiceDetail].InvoiceNo=[TT_Invoice].InvoiceNo
where [TT_Invoice].IsInvoiceConfirm=1 and InvoiceConfirmDate>=@Startmonth and InvoiceConfirmDate<=@Endmonth
) as tb1
left join TT_ContractNotice as tbCN on tb1.OrderNo=tbCN.OrderNo
) as tb2 group by DepartmentMark,InvoiceConfirmDate
) AS tb
PIVOT
(
SUM(InvoiceMoneyRMB) FOR InvoiceConfirmDate IN ([2012-01],[2012-02],[2012-03],[2012-04],[2012-05])
) AS pt
SELECT DepartmentMark
,sum (case when InvoiceConfirmDate = '2012-01' then InvoiceMoneyRMB else 0 end) [2012-01]
,sum (case when InvoiceConfirmDate = '2012-02' then InvoiceMoneyRMB else 0 end) [2012-02]
,sum (case when InvoiceConfirmDate = '2012-03' then InvoiceMoneyRMB else 0 end) [2012-03]
,sum (case when InvoiceConfirmDate = '2012-04' then InvoiceMoneyRMB else 0 end) [2012-04]
,sum (case when InvoiceConfirmDate = '2012-05' then InvoiceMoneyRMB else 0 end) [2012-05]
FROM
(
select DepartmentMark,sum(InvoiceMoneyRMB) InvoiceMoneyRMB,InvoiceConfirmDate
from (
--跟据销售单号，关联到部门
select tbCN.DepartmentMark,tb1.InvoiceMoneyRMB,tb1.InvoiceConfirmDate
from (
--销售单号对应的已开票的金额
SELECT[OrderNo]
,(select top 1 [ExchangeRate] from [TM_ExchangeRate] where [ExchangeDate]<=[TT_Invoice].InvoiceDate and [TT_Invoice].[CurrencyTypeID]=[CurrencyTypeID] order by [ExchangeDate] Desc)*[SubtoalMoney] as InvoiceMoneyRMB
,CONVERT(varchar(7) , [InvoiceConfirmDate], 23 ) InvoiceConfirmDate
FROM [TT_InvoiceDetail]
left join [TT_Invoice] on [TT_InvoiceDetail].InvoiceNo=[TT_Invoice].InvoiceNo
where [TT_Invoice].IsInvoiceConfirm=1 and InvoiceConfirmDate>=@Startmonth and InvoiceConfirmDate<=@Endmonth
) as tb1
left join TT_ContractNotice as tbCN on tb1.OrderNo=tbCN.OrderNo
) as tb2 group by DepartmentMark,InvoiceConfirmDate
)as tb3 GROUP BY DepartmentMark
