zwwldw表 zwwldw_dwbh(客户编号),zwwldw_dwmc(客户名称)
XSTD表:XSTD_SHDKH(客户编号),XSTD_TDLS(提单流水号), XSTD_DJRQ(日期),
XSTDMX表:XSTDMX_TDLS(提单流水号),XSTDMX_ZSL(本日销售量),XSTDMX_BHSE(本日应收款)
XSHK表:XSHK_SHDKH(客户编号),XSHK_BHKE(本日实收款),XSHK_DJRQ(日期)存储过程实现功能:当客户输入日期后按客户统计,表如下:
日期:如:20070428 本日销售是指20070428当天的
本月销售是指从20070401到20070428之间的
客户编号 客户名称 本日销售总量 本日应收总款 本日实收总款
本月累计销售总量 本月累计应收总款 本月累计实收总款
XSTD表:XSTD_SHDKH(客户编号),XSTD_TDLS(提单流水号), XSTD_DJRQ(日期),
XSTDMX表:XSTDMX_TDLS(提单流水号),XSTDMX_ZSL(本日销售量),XSTDMX_BHSE(本日应收款)
XSHK表:XSHK_SHDKH(客户编号),XSHK_BHKE(本日实收款),XSHK_DJRQ(日期)存储过程实现功能:当客户输入日期后按客户统计,表如下:
日期:如:20070428 本日销售是指20070428当天的
本月销售是指从20070401到20070428之间的
客户编号 客户名称 本日销售总量 本日应收总款 本日实收总款
本月累计销售总量 本月累计应收总款 本月累计实收总款
CREATE PROCEDURE dbo.aabb
@date datetime
AS
begin
select zwwldw_dwbh,zwwldw_dwmc ,XSTDMX_ZS_Day,XSTDMX_BHSE_Day,XSHK_BHKE_Day,XSTDMX_ZSL_Month,XSTDMX_BHSE_Month,XSHK_BHKE_Month
from
(select zwwldw_dwbh,zwwldw_dwmc,XSTD_DJRQ
from XSTD inner join zwwldw on XSTD.XSTD_SHDKH=zwwldw.zwwldw_dwbh
where XSTD_DJRQ=@date)a
inner join
(select sum(XSTDMX_ZSL) as XSTDMX_ZS_Day,sum(XSTDMX_BHSE) as XSTDMX_BHSE_Day ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ=@date)b
on a.XSTD_DJRQ=b.XSTD_DJRQ
inner join
(
select sum(XSHK_BHKE) as XSHK_BHKE_Day,max(XSHK_DJRQ) as XSHK_DJRQ
from XSHK
where XSHK_DJRQ=@date)c
on a.XSTD_DJRQ=c.XSHK_DJRQ
inner join (
select sum(XSTDMX_ZSL) as XSTDMX_ZSL_Month, sum(XSTDMX_BHSE) as XSTDMX_BHSE_Month ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ<=@date and XSTD_DJRQ >= Convert(nvarchar,year(@date))+'-'+Convert(nvarchar,month(@date))+'-1')d
on a.XSTD_DJRQ=d.XSTD_DJRQinner join (
select sum(XSHK_BHKE) as XSHK_BHKE_Month ,max(XSHK_DJRQ) as XSHK_DJRQ
from XSHK
where XSHK_DJRQ<=@date and XSHK_DJRQ >= Convert(nvarchar,year(@date))+'-'+Convert(nvarchar,month(@date))+'-1')e
on a.XSTD_DJRQ=e.XSHK_DJRQend
GO
from
(select zwwldw_dwbh,zwwldw_dwmc,XSTD_DJRQ
from XSTD inner join zwwldw on XSTD.XSTD_SHDKH=zwwldw.zwwldw_dwbh
where XSTD_DJRQ=20070404)a
inner join
(select sum(XSTDMX_ZSL) as XSTDMX_ZS_Day,sum(XSTDMX_BHSE) as XSTDMX_BHSE_Day ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ=20070404)b
on a.XSTD_DJRQ=b.XSTD_DJRQ
inner join
(
select sum(XSHK_BHKE) as XSHK_BHKE_Day,max(XSHK_DJRQ) as XSHK_DJRQ
from XSHK
where XSHK_DJRQ=20070404)c
on a.XSTD_DJRQ=c.XSHK_DJRQ
inner join (
select sum(XSTDMX_ZSL) as XSTDMX_ZSL, sum(XSTDMX_BHSE) as XSTDMX_BHSE ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ<=20070404 and XSTD_DJRQ >= 20070401)d
on a.XSTD_DJRQ=d.XSTD_DJRQinner join (
select sum(XSHK_BHKE) as XSHK_BHKE ,max(XSHK_DJRQ) as XSHK_DJRQ
from lc0029999.XSHK
where XSHK_DJRQ<=20070404 and XSHK_DJRQ >= 20070401)e
on a.XSTD_DJRQ=e.XSHK_DJRQ
报列名不匹配
from
(select zwwldw_dwbh,zwwldw_dwmc,XSTD_DJRQ
from XSTD inner join zwwldw on XSTD.XSTD_SHDKH=zwwldw.zwwldw_dwbh
where XSTD_DJRQ='2007-04-04')a
inner join
(select sum(XSTDMX_ZSL) as XSTDMX_ZS_Day,sum(XSTDMX_BHSE) as XSTDMX_BHSE_Day ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ='2007-04-04')b
on a.XSTD_DJRQ=b.XSTD_DJRQ
inner join
(
select sum(XSHK_BHKE) as XSHK_BHKE_Day,max(XSHK_DJRQ) as XSHK_DJRQ
from XSHK
where XSHK_DJRQ='2007-04-04')c
on a.XSTD_DJRQ=c.XSHK_DJRQ
inner join (
select sum(XSTDMX_ZSL) as XSTDMX_ZSL, sum(XSTDMX_BHSE) as XSTDMX_BHSE ,max(XSTD_DJRQ) as XSTD_DJRQ
from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS
where XSTD_DJRQ<='2007-04-04' and XSTD_DJRQ >= '2007-04-01')d
on a.XSTD_DJRQ=d.XSTD_DJRQinner join (
select sum(XSHK_BHKE) as XSHK_BHKE ,max(XSHK_DJRQ) as XSHK_DJRQ
from XSHK
where XSHK_DJRQ<='2007-04-04' and XSHK_DJRQ >= '2007-04-01')e
on a.XSTD_DJRQ=e.XSHK_DJRQ
还有把select语句里面字段前面的表名字去掉,不是丛表里面出的数据,是丛查询结果里面取的数据,字段前面什么都不用加,还有日期必须要有单引号括起来,正确的日期类型应该是2007-04-01,不带横杆系统转换不成日期类型
查询出来的数据按‘所有客户’排列!这样是有原因的!因为本月的客户肯定比本日的多!
select zwwldw_dwbh as 客户编号,zwwldw_dwmc as 客户名称,XSTDMX_ZSL as 本日销售量
,XSTDMX_BHSE as 本日应收款,XSHK_BHKE 本日实收款,XSTDMX_ZSL as 本日销售量
,XSTDMX_BHSE as 本月应收款,XSHK_BHKE as 本月销售量
from
(select zwwldw_dwbh,zwwldw_dwmc,'2007-04-04' as XSTD_DJRQ from XSTD inner join zwwldw on XSTD.XSTD_SHDKH=zwwldw.zwwldw_dwbh where XSTD_DJRQ<='2007-04-04' and XSTD_DJRQ>='2007-04-01')a
inner join
(select sum(XSTDMX_ZSL) as XSTDMX_ZS_Day,sum(XSTDMX_BHSE) as XSTDMX_BHSE_Day ,'2007-04-04' as XSTD_DJRQ from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS where XSTD_DJRQ='2007-04-04')b
on a.XSTD_DJRQ=b.XSTD_DJRQ
inner join
(select sum(XSHK_BHKE) as XSHK_BHKE_Day,'2007-04-04' as XSHK_DJRQ from XSHK where XSHK_DJRQ='2007-04-04')c
on a.XSTD_DJRQ=c.XSHK_DJRQ
inner join (
select sum(XSTDMX_ZSL) as XSTDMX_ZSL, sum(XSTDMX_BHSE) as XSTDMX_BHSE ,'2007-04-04' as XSTD_DJRQ from XSTDMX inner join XSTD
on XSTDMX.XSTDMX_TDLS=XSTD.XSTD_TDLS where XSTD_DJRQ<='2007-04-04' and XSTD_DJRQ >= '2007-04-01')d
on a.XSTD_DJRQ=d.XSTD_DJRQ
inner join (
select sum(XSHK_BHKE) as XSHK_BHKE ,'2007-04-04' as XSHK_DJRQ from XSHK where XSHK_DJRQ<='2007-04-04' and XSHK_DJRQ >= '2007-04-01')e
on a.XSTD_DJRQ=e.XSHK_DJRQ
select substring ('20070429',1,4)+'-'+substring ('20070429',5,2)+'-'+substring ('20070429',7,2) 转化一下再使用
在存储过程里面加上
set @date=substring (@date,1,4)+'-'+substring (@date,5,2)+'-'+substring (@date,7,2)
开始的时候把@date 声明成char(8)类型
如果上面那个SQL还不行,我就没办法了,也没数据测,问问你公司的其他同事吧,祝你好运,早点完成工作,今天下午我们就放假了,可以休息了
http://community.csdn.net/Expert/TopicView3.asp?id=5504448