高手，

2012-09-03

高手，求救！！！！！！！！！！！！！fi_company_namefi_order_datefi_company_depfi_credit_channelfi_order_statusfi_

高手，求救！！！！！！！！！！！！！
fi_company_namefi_order_datefi_company_depfi_credit_channelfi_order_statusfi_cxxbfi_order_date_alter
城西别克2012-08-01区域上汽财务合同否2012-08-10
城西别克2012-08-01区域上汽财务合同否2012-08-10
城西别克2012-08-01区域上汽财务申请否2012-08-10
南昌别克2012-07-30区域GMAC合同否2012-08-10
南昌别克2012-07-29展厅GMAC申请否2012-08-10
南昌别克2012-07-31展厅GMAC合同否2012-08-10
南昌别克2012-07-31区域GMAC申请否2012-08-10
南昌雪佛兰2012-07-29展厅GMAC申请否2012-08-10
南昌雪佛兰2012-07-31展厅GMAC申请否2012-08-12
南昌别克2012-08-01区域GMAC合同是2012-08-10
南昌别克2012-10-01区域GMAC合同是2012-08-10
南昌别克2012-08-02展厅GMAC合同是2012-08-10
南昌别克2012-08-02展厅GMAC申请否2012-08-10
南昌别克2012-08-03展厅银行申请否2012-08-10
南昌雪佛兰2012-08-04展厅GMAC申请否2012-08-10
南昌别克2012-08-04展厅GMAC合同否2012-08-10
南昌别克2012-08-05电销中心GMAC申请否2012-08-10
南昌别克2012-08-08电销中心GMAC申请否2012-08-10
南昌别克2012-08-08展厅GMAC合同否2012-08-10
南昌别克2012-08-08区域银行取消否2012-08-10
南昌别克2012-08-08展厅GMAC合同是2012-08-10
南昌雪佛兰2012-08-09展厅GMAC拒批否2012-08-10
南昌别克2012-08-10展厅GMAC申请否2012-08-10
南昌别克2012-08-10展厅GMAC合同是2012-09-10
南昌雪佛兰2012-08-10展厅GMAC合同否2012-08-10
表格，视图代码如下：
SELECT a.fi_company AS 单店, a.fi_plan AS [计划数], year([fi_plan_date]) AS [年份],
month([fi_plan_date]) AS [月份], SUM(CASE WHEN fi_company_dep = '展厅' AND
fi_order_status = '合同' THEN 1 ELSE 0 END) 展厅合同,
SUM(CASE WHEN fi_company_dep = '区域' AND
fi_order_status = '合同' THEN 1 ELSE 0 END) 区域合同,
SUM(CASE WHEN fi_company_dep = '电销中心' AND
fi_order_status = '合同' THEN 1 ELSE 0 END) 电销合同,
SUM(CASE WHEN fi_order_status = '合同' THEN 1 ELSE 0 END) 实际,
达成率 = ltrim(cast(SUM(CASE WHEN fi_order_status = '合同' THEN 1 ELSE 0 END)
* 100.0 / a.[fi_plan] AS decimal(9, 2))) + '%',
SUM(CASE WHEN fi_company_dep = '展厅' AND
fi_order_status = '申请' THEN 1 ELSE 0 END) 展厅申请,
SUM(CASE WHEN fi_company_dep = '展厅' AND
fi_order_status = '拒批' THEN 1 ELSE 0 END) 展厅拒批,
SUM(CASE WHEN fi_company_dep = '展厅' AND
fi_order_status = '取消' THEN 1 ELSE 0 END) 展厅取消,
SUM(CASE WHEN fi_company_dep = '区域' AND
fi_order_status = '申请' THEN 1 ELSE 0 END) 区域申请,
SUM(CASE WHEN fi_company_dep = '区域' AND
fi_order_status = '拒批' THEN 1 ELSE 0 END) 区域拒批,
SUM(CASE WHEN fi_company_dep = '区域' AND
fi_order_status = '取消' THEN 1 ELSE 0 END) 区域取消,
SUM(CASE WHEN fi_company_dep = '电销中心' AND
fi_order_status = '申请' THEN 1 ELSE 0 END) 电销申请,
SUM(CASE WHEN fi_company_dep = '电销中心' AND
fi_order_status = '拒批' THEN 1 ELSE 0 END) 电销拒批,
SUM(CASE WHEN fi_company_dep = '电销中心' AND
fi_order_status = '取消' THEN 1 ELSE 0 END) 电销取消,
SUM(CASE WHEN fi_cxxb = '是' AND fi_order_status = '合同' THEN 1 ELSE 0 END)
促销量
FROM tb_fi_plan a, tb_fi b
WHERE a.fi_company = b.fi_company_name AND year(b.fi_order_date_alter)
= year(GetDate()) AND month(b.fi_order_date_alter) = month(GetDate())
GROUP BY a.fi_company, year(fi_plan_date), month(fi_plan_date), a.fi_plan

为什么求出来的8、9月份南昌别克的数据都是一样的？有谁知道吗？

[解决办法]

SQL code

AND year(b.fi_order_date_alter) = year(GetDate()) AND month(b.fi_order_date_alter) = month(GetDate())--改简单点吧and convert(varchar(6),b.fi_order_date_alter,112)=convert(varchar(6),getdate(),112)
------解决方案--------------------

SQL code

--> 测试数据: @tb_fideclare @tb_fi table (fi_company_name varchar(10),fi_order_date datetime,fi_company_dep varchar(8),fi_credit_channel varchar(8),fi_order_status varchar(4),fi_cxxb varchar(2),fi_order_date_alter datetime)insert into @tb_fiselect '城西别克','2012-08-01','区域','上汽财务','合同','否','2012-08-10' union allselect '城西别克','2012-08-01','区域','上汽财务','合同','否','2012-08-10' union allselect '城西别克','2012-08-01','区域','上汽财务','申请','否','2012-08-10' union allselect '南昌别克','2012-07-30','区域','GMAC','合同','否','2012-08-10' union allselect '南昌别克','2012-07-29','展厅','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-07-31','展厅','GMAC','合同','否','2012-08-10' union allselect '南昌别克','2012-07-31','区域','GMAC','申请','否','2012-08-10' union allselect '南昌雪佛兰','2012-07-29','展厅','GMAC','申请','否','2012-08-10' union allselect '南昌雪佛兰','2012-07-31','展厅','GMAC','申请','否','2012-08-12' union allselect '南昌别克','2012-08-01','区域','GMAC','合同','是','2012-08-10' union allselect '南昌别克','2012-10-01','区域','GMAC','合同','是','2012-08-10' union allselect '南昌别克','2012-08-02','展厅','GMAC','合同','是','2012-08-10' union allselect '南昌别克','2012-08-02','展厅','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-08-03','展厅','银行','申请','否','2012-08-10' union allselect '南昌雪佛兰','2012-08-04','展厅','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-08-04','展厅','GMAC','合同','否','2012-08-10' union allselect '南昌别克','2012-08-05','电销中心','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-08-08','电销中心','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-08-08','展厅','GMAC','合同','否','2012-08-10' union allselect '南昌别克','2012-08-08','区域','银行','取消','否','2012-08-10' union allselect '南昌别克','2012-08-08','展厅','GMAC','合同','是','2012-08-10' union allselect '南昌雪佛兰','2012-08-09','展厅','GMAC','拒批','否','2012-08-10' union allselect '南昌别克','2012-08-10','展厅','GMAC','申请','否','2012-08-10' union allselect '南昌别克','2012-08-10','展厅','GMAC','合同','是','2012-09-10' union allselect '南昌雪佛兰','2012-08-10','展厅','GMAC','合同','否','2012-08-10'/*fi_company_name fi_order_date fi_company_dep fi_credit_channel fi_order_status fi_cxxb fi_order_date_alter--------------- ----------------------- -------------- ----------------- --------------- ------- -----------------------城西别克 2012-08-01 00:00:00.000 区域上汽财务合同否 2012-08-10 00:00:00.000城西别克 2012-08-01 00:00:00.000 区域上汽财务合同否 2012-08-10 00:00:00.000城西别克 2012-08-01 00:00:00.000 区域上汽财务申请否 2012-08-10 00:00:00.000南昌别克 2012-07-30 00:00:00.000 区域 GMAC 合同否 2012-08-10 00:00:00.000南昌别克 2012-07-29 00:00:00.000 展厅 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-07-31 00:00:00.000 展厅 GMAC 合同否 2012-08-10 00:00:00.000南昌别克 2012-07-31 00:00:00.000 区域 GMAC 申请否 2012-08-10 00:00:00.000南昌雪佛兰 2012-07-29 00:00:00.000 展厅 GMAC 申请否 2012-08-10 00:00:00.000南昌雪佛兰 2012-07-31 00:00:00.000 展厅 GMAC 申请否 2012-08-12 00:00:00.000南昌别克 2012-08-01 00:00:00.000 区域 GMAC 合同是 2012-08-10 00:00:00.000南昌别克 2012-10-01 00:00:00.000 区域 GMAC 合同是 2012-08-10 00:00:00.000南昌别克 2012-08-02 00:00:00.000 展厅 GMAC 合同是 2012-08-10 00:00:00.000南昌别克 2012-08-02 00:00:00.000 展厅 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-08-03 00:00:00.000 展厅银行申请否 2012-08-10 00:00:00.000南昌雪佛兰 2012-08-04 00:00:00.000 展厅 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-08-04 00:00:00.000 展厅 GMAC 合同否 2012-08-10 00:00:00.000南昌别克 2012-08-05 00:00:00.000 电销中心 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-08-08 00:00:00.000 电销中心 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-08-08 00:00:00.000 展厅 GMAC 合同否 2012-08-10 00:00:00.000南昌别克 2012-08-08 00:00:00.000 区域银行取消否 2012-08-10 00:00:00.000南昌别克 2012-08-08 00:00:00.000 展厅 GMAC 合同是 2012-08-10 00:00:00.000南昌雪佛兰 2012-08-09 00:00:00.000 展厅 GMAC 拒批否 2012-08-10 00:00:00.000南昌别克 2012-08-10 00:00:00.000 展厅 GMAC 申请否 2012-08-10 00:00:00.000南昌别克 2012-08-10 00:00:00.000 展厅 GMAC 合同是 2012-09-10 00:00:00.000南昌雪佛兰 2012-08-10 00:00:00.000 展厅 GMAC 合同否 2012-08-10 00:00:00.000*/--> 测试数据: @tb_fi_plandeclare @tb_fi_plan table ( ID int,fi_company varchar(10),fi_plan int, fi_plan_date datetime,Ispost varchar(2))insert into @tb_fi_planselect 1,'南昌别克',30,'2012-08-01','是' union allselect 3,'南昌雪佛兰',27,'2012-08-01','是' union allselect 7,'城西别克',15,'2012-08-01','是' union allselect 9,'南昌别克',20,'2012-09-06','是'/*ID fi_company fi_plan fi_plan_date Ispost----------- ---------- ----------- ----------------------- ------1 南昌别克 30 2012-08-01 00:00:00.000 是3 南昌雪佛兰 27 2012-08-01 00:00:00.000 是7 城西别克 15 2012-08-01 00:00:00.000 是9 南昌别克 20 2012-09-06 00:00:00.000 是*/SELECT a.fi_company AS 单店, a.fi_plan AS [计划数], year([fi_plan_date]) AS [年份], month([fi_plan_date]) AS [月份], SUM(CASE WHEN fi_company_dep = '展厅' AND fi_order_status = '合同' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 展厅合同, SUM(CASE WHEN fi_company_dep = '区域' AND fi_order_status = '合同' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 区域合同, SUM(CASE WHEN fi_company_dep = '电销中心' AND fi_order_status = '合同'and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 电销合同, SUM(CASE WHEN fi_order_status = '合同' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 实际, 达成率 = ltrim(cast(SUM(CASE WHEN fi_order_status = '合同' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) * 100.0 / a.[fi_plan] AS decimal(9, 2))) + '%', SUM(CASE WHEN fi_company_dep = '展厅' AND fi_order_status = '申请' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 展厅申请, SUM(CASE WHEN fi_company_dep = '展厅' AND fi_order_status = '拒批' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 展厅拒批, SUM(CASE WHEN fi_company_dep = '展厅' AND fi_order_status = '取消' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 展厅取消, SUM(CASE WHEN fi_company_dep = '区域' AND fi_order_status = '申请' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 区域申请, SUM(CASE WHEN fi_company_dep = '区域' AND fi_order_status = '拒批' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 区域拒批, SUM(CASE WHEN fi_company_dep = '区域' AND fi_order_status = '取消' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 区域取消, SUM(CASE WHEN fi_company_dep = '电销中心' AND fi_order_status = '申请' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 电销申请, SUM(CASE WHEN fi_company_dep = '电销中心' AND fi_order_status = '拒批' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 电销拒批, SUM(CASE WHEN fi_company_dep = '电销中心' AND fi_order_status = '取消' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 电销取消, SUM(CASE WHEN fi_cxxb = '是' AND fi_order_status = '合同' and month(fi_order_date_alter)=month(fi_plan_date) THEN 1 ELSE 0 END) 促销量FROM @tb_fi_plan a, @tb_fi bWHERE a.fi_company = b.fi_company_name --AND year(b.fi_order_date_alter)= year(GetDate()) --AND month(b.fi_order_date_alter) = month(GetDate())GROUP BY a.fi_company, year(fi_plan_date), month(fi_plan_date), a.fi_plan--修改后的结果：/*单店计划数年份月份展厅合同区域合同 ---------- ----------- ----------- ----------- ----------- ----------- ----------- 城西别克 15 2012 8 0 2 南昌别克 30 2012 8 5 3 南昌别克 20 2012 9 1 0 南昌雪佛兰 27 2012 8 1 0 */

热点排行

SQL Server

高手，