您的位置：程序门 -> ms-sql server -> 基础类

求一存储过程！

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

求一存储过程！[已结贴,结贴人:hongrisoft]

发表于：2007-05-06 11:44:59 楼主

求一存储过程：
xstd表：xstd_shdkh（客户编号)，xstd_tdls（提单流水号), xstd_djrq（日期)，
xstdmx表：xstdmx_tdls（提单流水号),xstdmx_zsl（本日销售量),xstdmx_bhse（本日应收款)

xsfp表：xsfp_shdkh（客户编号)，xsfp_fpls（发票流水),xsfp_djrq（日期)
xsfpmx表：xsfpmx_hkbz（回款标志为0），xsfpmx_fpls（发票流水),xsfpmx_c2（开票日期)xsfpmx_bhke（本日实收款)
if 输入日期（日期格式20070404 char（8)）-xsfpmx_c2（开票日期) <一个月 then
一个月到期未付款 else 二个月到期未付款
未付款为:xstdmx_bhse-xsfpmx_bhke
形成表格
客户名称一个月到期未付款二个月到期未付款
0101 230000.00 2300000.00
回款标志为0的为欠款的！

发表于：2007-05-06 11:54:241楼得分:0

还差
zwwldw表 zwwldw_dwbh（客户编号),zwwldw_dwmc（客户名称)
xstd_shdkh（客户编号)与zwwldw_dwbh（客户编号)与xsfp_shdkh（客户编号)对应
xstd_tdls（提单流水号)与xstdmx_tdls（提单流水号)对应
xsfp_fpls（发票流水)与xsfpmx_fpls（发票流水)对应

发表于：2007-05-06 13:59:422楼得分:0

好多表，最好能給點數據，另外給出的數據最好能包含你所有可能出現的情況。

发表于：2007-05-06 14:29:553楼得分:0

只告诉我
if 输入日期（日期格式20070404 char（8)）-xsfpmx_c2（开票日期) <一个月 then
一个月到期未付款 else 二个月到期未付款
怎么写就可以！其他我自己解决

发表于：2007-05-06 14:36:484楼得分:0

hongrisoft（求知者) （ ) 信誉：100 blog 加为好友 2007-05-06 14:29:55 得分: 0

只告诉我
if 输入日期（日期格式20070404 char（8)）-xsfpmx_c2（开票日期) <一个月 then
一个月到期未付款 else 二个月到期未付款
怎么写就可以！其他我自己解决

-------------

暈，關鍵就是沒有數據，看上面的分析，我也看不出“一个月到期未付款”是怎麼計算的。

大概的代碼是這樣的。

select
a.客户名称,
sum（case when datediff（mm, @输入日期, 开票日期) < 1 then ... else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, @输入日期, 开票日期) > = 1 then ... else 0 end) as 二个月到期未付款
from
zwwldw a
inner join
...
on ...
group by
a.客户名称

省略號中的代碼需要你自己補充。

发表于：2007-05-06 14:47:465楼得分:0

日期格式不对！我的日期格式是字符串 char（8) 如：20070404

发表于：2007-05-06 14:51:356楼得分:0

hongrisoft（求知者) （ ) 信誉：100 blog 加为好友 2007-05-06 14:47:46 得分: 0

日期格式不对！我的日期格式是字符串 char（8) 如：20070404

-----------
只要你的字符串的格式沒有問題，一樣可以比較的。

eg:

declare @输入日期 char（8)
select @输入日期 = '20070404 '
select datediff（mm, @输入日期, getdate（))

发表于：2007-05-06 15:07:327楼得分:0

select d.zwwldw_dwmc as 客户名称,sum（a.xstdmx_bhse) as 应收款,sum（c.xsfpmx_bhke) as 回收款
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 ' group by d.zwwldw_dwmc
结果为：
客户名称应收款回收款
中国华电工程（集团）有限公司 700000.0 280000.0
中冶华天工程技术有限公司 324000.0 108000.0

declare @输入日期 char（8)
select @输入日期 = '20070404 '
select datediff（mm, @输入日期, getdate（))
？？？？？
求：if 输入日期（日期格式20070404 char（8)）-xsfpmx_c2（开票日期) <一个月 then
一个月到期未付款（应收款-回收款） else 二个月到期未付款（应收款-回收款）

一个月未到为：
应收款-回收款

发表于：2007-05-06 15:11:358楼得分:0

try

declare @输入日期 char（8)
select @输入日期 = '20070404 '
select
d.zwwldw_dwmc as 客户名称,
sum（case when datediff（mm, @输入日期, 开票日期) < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, @输入日期, 开票日期) > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where
b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc

发表于：2007-05-06 15:25:529楼得分:0

select
d.zwwldw_dwmc as 客户名称,
sum（case when datediff（mm, '20070404 ', c.xsfpmx_c2) < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, '20070404 ', c.xsfpmx_c2) > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where
b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc
查询结果为：
新疆八一钢铁集团有限责任公司 3140150.0 0.0
新疆华电昌吉热电二期有限责任公司 564000.0 0.0
新浦化学工业（泰兴)有限公司 335640.0 0.0
新泰正大焦化有限公司 145200.0 0.0
邢台钢铁有限责任公司 2981105.0 0.0

开票日期c.xsfpmx_c2 为20070101 和20070401 结果不对！
‘20070404'-‘20070401'〈一个月应该去最后四位判断吧！

发表于：2007-05-06 15:30:4110楼得分:0

开票日期c.xsfpmx_c2 为20070101 和20070401 结果不对！
‘20070404'-‘20070401'〈一个月应该去最后四位判断吧！

------------
你沒看懂這個例子

declare @输入日期 char（8)
select @输入日期 = '20070404 '
select datediff（mm, @输入日期, getdate（))

它實際是轉換為datetime做比較的。

发表于：2007-05-06 15:31:5311楼得分:0

--上面的有點理解錯誤，再試試這個

select
d.zwwldw_dwmc as 客户名称,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where
b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc

发表于：2007-05-06 15:36:4312楼得分:0

我把getdate（)我数据表中为c.xsfpmx_c2（已经全修改为‘20070104'）
这样datediff（mm, '20070404 ', c.xsfpmx_c2) > = 1 没查询出来

发表于：2007-05-06 15:38:5713楼得分:0

hongrisoft（求知者) （ ) 信誉：100 blog 加为好友 2007-05-06 15:36:43 得分: 0

我把getdate（)我数据表中为c.xsfpmx_c2（已经全修改为‘20070104'）
这样datediff（mm, '20070404 ', c.xsfpmx_c2) > = 1 没查询出来

---------
這麼查詢

datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1

開始寫的語句有問題，用剛寫的那個

发表于：2007-05-06 15:42:1014楼得分:0

呵呵，老大和我想的一样！我试了下，不对！我又查了下，知道原因了
示例
此示例确定在 pubs 数据库中标题发布日期和当前日期间的天数。

use pubs
go
select datediff（day, pubdate, getdate（)) as no_of_days
from titles
go
结果是天数
我们用的是1天，用30我试了下对！不过问题又出来了，并不是每个月都是30天啊

发表于：2007-05-06 15:46:4515楼得分:0

我们用的是1天

-------
我用的不是1天比較，而是用1個月做比較的。

指定的參數是mm

datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1

試下這個，看看結果

select
d.zwwldw_dwmc as 客户名称,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where
b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc

发表于：2007-05-06 15:54:3816楼得分:0

不对
我用
sum（case when datediff（mm, '20070404 ',c.xsfpmx_c2) < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
试也不对！
c.xsfpmx_c2为： '20070325 ' 结果为：
新疆八一钢铁集团有限责任公司 3140150.0 3140150.0
新疆华电昌吉热电二期有限责任公司 564000.0 564000.0
新浦化学工业（泰兴)有限公司 335640.0 335640.0
新泰正大焦化有限公司 145200.0 145200.0
邢台钢铁有限责任公司 2981105.0 2981105.0
张家港沙洲电力有限公司 1986208.0 1986208.0

发表于：2007-05-06 15:56:2817楼得分:50

貼點測試數據，以及你要的結果出來，我來測試下看看問題出在哪。

我這裡是將 '20070404 '放在後面的，應該沒有問題的。

sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款

发表于：2007-05-06 16:06:1918楼得分:0

当 xsfpmx_c2= '20070325 '时：
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') < 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（mm, c.xsfpmx_c2, '20070404 ') > = 1 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
查询结果：
新疆八一钢铁集团有限责任公司 0.0 3140150.0
新疆华电昌吉热电二期有限责任公司 0.0 564000.0
新浦化学工业（泰兴)有限公司 0.0 335640.0
新泰正大焦化有限公司 0.0 145200.0
邢台钢铁有限责任公司 0.0 2981105.0
而用day查询就对！为什么？？？
sum（case when datediff（day, c.xsfpmx_c2, '20070404 ') < 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 一个月到期未付款,
sum（case when datediff（day, c.xsfpmx_c2, '20070404 ') > = 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as 二个月到期未付款
结果：
新疆八一钢铁集团有限责任公司 3140150.0 0.0
新疆华电昌吉热电二期有限责任公司 564000.0 0.0
新浦化学工业（泰兴)有限公司 335640.0 0.0
新泰正大焦化有限公司 145200.0 0.0
邢台钢铁有限责任公司 2981105.0 0.0

发表于：2007-05-06 16:08:3019楼得分:0

呵呵，老板说了，就按天来算了！一个月30天！呵呵！~我终于解脱了！

发表于：2007-05-06 16:14:2020楼得分:0

datediff（mm, c.xsfpmx_c2, '20070404 ') < 1

再想想你的需求，這麼判斷的確有些問題

比如這個

select datediff（mm, '20070301 ', '20070430 ')

實際它們的間隔是1個月，但是按照你的要求應該是大於一個月。

改用天數可能的確是更符合你的要求。

发表于：2007-05-06 17:00:1421楼得分:0

datediff（mm, c.xsfpmx_c2, '20070404 ') < 1
放在存储过程里面执行可以！但是用的时候报转换成datetime 出错

发表于：2007-05-06 17:20:4622楼得分:0

如果報錯，說明的傳入的字符串不符合時間格式，比如傳入 "20070230 ",這樣就會報錯，這個是你傳入的字符串的問題，而不是存儲過程的問題.

发表于：2007-05-06 17:43:0723楼得分:0

是因为在存储过程当中含有（datediff（mm, c.xsfpmx_c2, '20070404 ') < 1
）的不允许动态创建表
我原来的是：
select
d.zwwldw_dwbh,d.zwwldw_dwmc,
sum（case when datediff（day, c.xsfpmx_c2, '20070404 ') < 30 then a.xstdmx_bhse

- c.xsfpmx_bhke else 0 end) as sms1,
sum（case when datediff（day, c.xsfpmx_c2, '20070404 ') > = 30 then

a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms2
into smsaaa（出错误了！）
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where b.xstd_djrq <= '20070404 ' and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc,d.zwwldw_dwbh

select

d.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_da

y,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,a.sms1,a.

sms2
from smsi as d left join smsaaa as a on a.zwwldw_dwbh=d.zwwldw_dwbh
上面的into smsaaa（出错误了！）
现在修改为：
select d.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,a.sms1,a.sms2
from smsi as d left join
（select
d.zwwldw_dwbh,d.zwwldw_dwmc,
sum（case when datediff（day, c.xsfpmx_c2,@date) < 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms1,
sum（case when datediff（day, c.xsfpmx_c2,@date) > = 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms2
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where b.xstd_djrq <=@date and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc,d.zwwldw_dwbh) as a on a.zwwldw_dwbh=d.zwwldw_dwbh
可以了！
谢谢老大了！我给老大的msn发了个邮件！老大是台湾人？打字是繁体？

发表于：2007-05-06 17:53:1024楼得分:0

報的錯誤是什麼？按道理應該沒有問題的。

发表于：2007-05-07 08:09:5925楼得分:0

mark!

发表于：2007-05-07 11:08:5826楼得分:0

http://community.csdn.net/expert/topic/5513/5513571.xml?temp=.6879541
大家帮帮我，看一下我的贴子

发表于：2007-05-07 13:32:3827楼得分:0

该回复于2007-12-29 16:16:07被管理员或版主删除

发表于：2007-05-07 13:32:4528楼得分:0

该回复于2007-12-29 16:16:07被管理员或版主删除

发表于：2007-05-08 10:59:0829楼得分:0

女上司对我特别照顾，可我是个结过婚的人，我该怎么办？

http://community.csdn.net/expert/topic/5515/5515498.xml?temp=.4312403

发表于：2007-05-08 15:37:2530楼得分:0

if exists （select * from sysobjects
where name = 'sp_test ' and type = 'p ')
drop procedure sp_test
go
substring（)
create procedure sp_test（@date char（8),@dqbh char（20))
as
begin

if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsa] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsa]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsb] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsb]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsc] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsc]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsaa] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsaa]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsbb] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsbb]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smscc] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smscc]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsd] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsd]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smse] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smse]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsf] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsf]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsg] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsg]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsh] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsh]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsi] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsi]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsaaa] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsaaa]
if exists （select * from dbo.sysobjects where id = object_id（n '[lc0059999].[smsbbb] ') and objectproperty（id, n 'isusertable ') = 1)
drop table [lc0059999].[smsbbb]
/*日报表smsc*/
select w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc,sum（a.xstdmx_zsl) as xstdmx_zsl_day,
sum（a.xstdmx_bhse) as xstdmx_bhse_day into smsa
from （（xstdmx as a inner join xstd as b on a.xstdmx_tdls = b.xstd_tdls) left join zwwldw as z on z.zwwldw_dwbh=b.xstd_shdkh)
inner join zwdqzd as w on w.zwdqzd_dqbh=z.zwwldw_dqbh
where b.xstd_djrq=@date group by w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc

select w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc,sum（c.xshk_bhke) as xshk_bhke_day into smsb
from （xshk as c inner join zwwldw as z on z.zwwldw_dwbh=c.xshk_shdkh) inner join zwdqzd as w on w.zwdqzd_dqbh=z.zwwldw_dqbh
where c.xshk_djrq=@date group by w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc

select a.zwdqzd_dqmc,a.zwwldw_dqbh,a.zwwldw_dwbh,a.zwwldw_dwmc,a.xstdmx_zsl_day,a.xstdmx_bhse_day,b.xshk_bhke_day into smsc
from smsa as a left join smsb as b on b.zwwldw_dwbh=a.zwwldw_dwbh

insert into smsc select b.zwdqzd_dqmc,b.zwwldw_dqbh,b.zwwldw_dwbh,b.zwwldw_dwmc,a.xstdmx_zsl_day,a.xstdmx_bhse_day,b.xshk_bhke_day
from smsb as b left join smsa as a on b.zwwldw_dwbh=a.zwwldw_dwbh where b.zwwldw_dwbh not in （select zwwldw_dwbh from smsc)

/*月报表smscc*/
select w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc,sum（a.xstdmx_zsl) as xstdmx_zsl_m,
sum（a.xstdmx_bhse) as xstdmx_bhse_m into smsaa
from （（xstdmx as a inner join xstd as b on a.xstdmx_tdls = b.xstd_tdls) left join zwwldw as z on z.zwwldw_dwbh=b.xstd_shdkh)
inner join zwdqzd as w on w.zwdqzd_dqbh=z.zwwldw_dqbh
where b.xstd_djrq <=@date and b.xstd_djrq> =left（@date,6)+ '01 ' group by w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc

select w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc,sum（c.xshk_bhke) as xshk_bhke_m into smsbb
from （xshk as c inner join zwwldw as z on z.zwwldw_dwbh=c.xshk_shdkh) inner join zwdqzd as w on w.zwdqzd_dqbh=z.zwwldw_dqbh
where c.xshk_djrq <=@date and c.xshk_djrq> =left（@date,6)+ '01 ' group by w.zwdqzd_dqmc,z.zwwldw_dqbh,z.zwwldw_dwbh,z.zwwldw_dwmc

select a.zwdqzd_dqmc,a.zwwldw_dqbh,a.zwwldw_dwbh,a.zwwldw_dwmc,a.xstdmx_zsl_m,a.xstdmx_bhse_m,b.xshk_bhke_m into smscc
from smsaa as a left join smsbb as b on b.zwwldw_dwbh=a.zwwldw_dwbh

insert into smscc select b.zwdqzd_dqmc,b.zwwldw_dqbh,b.zwwldw_dwbh,b.zwwldw_dwmc,a.xstdmx_zsl_m,a.xstdmx_bhse_m,b.xshk_bhke_m
from smsbb as b left join smsaa as a on b.zwwldw_dwbh=a.zwwldw_dwbh where b.zwwldw_dwbh not in （select zwwldw_dwbh from smscc)

/*合并日报表和月报表smsd*/
select cc.zwdqzd_dqmc,cc.zwwldw_dqbh,cc.zwwldw_dwbh,cc.zwwldw_dwmc,c.xstdmx_zsl_day,c.xstdmx_bhse_day,c.xshk_bhke_day,
cc.xstdmx_zsl_m,cc.xstdmx_bhse_m,cc.xshk_bhke_m into smsd
from smscc as cc left join smsc as c on c.zwwldw_dwbh=cc.zwwldw_dwbh

/*合同应收款smse*/
select hmhtgy_dwbh,hmhtgy_htje into smse
from hmhtgy where hmhtgy_qdrq <=@date

/*合并日报表和月报表后加入合同应收款smsf*/
select d.zwdqzd_dqmc,d.zwwldw_dqbh,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,h.hmhtgy_htje into smsf
from smsd as d left join smse as h on h.hmhtgy_dwbh=d.zwwldw_dwbh

/*合同实收款*/
select f.xsfp_shdkh,sum（x.xsfpmx_bhke) as xsfpmx_bhke into smsg
from xsfpmx as x inner join xsfp as f on f.xsfp_fpls=x.xsfpmx_fpls where xsfp_djrq <=@date group by f.xsfp_shdkh

/*合同余额smsh*/
select e.hmhtgy_dwbh,（e.hmhtgy_htje-g.xsfpmx_bhke) htye into smsh
from smse as e left join smsg as g on g.xsfp_shdkh=e.hmhtgy_dwbh

/*smsf加入余额*/
select d.zwdqzd_dqmc,d.zwwldw_dqbh,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,h.htye into smsi
from smsf as d left join smsh as h on h.hmhtgy_dwbh=d.zwwldw_dwbh

发表于：2007-05-08 15:37:3231楼得分:0

/*smsaaa加入余未付款*/
select d.zwwldw_dqbh,d.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,a.sms1,a.sms2 into smsaaa
from smsi as d left join
（select
d.zwwldw_dwbh,d.zwwldw_dwmc,
sum（case when datediff（day, c.xsfpmx_c2,@date) < 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms1,
sum（case when datediff（day, c.xsfpmx_c2,@date) > = 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms2
from （（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh
where b.xstd_djrq <=@date and c.xsfpmx_hkbz= '0 '
group by d.zwwldw_dwmc,d.zwwldw_dwbh) as a on a.zwwldw_dwbh=d.zwwldw_dwbh
order by d.zwwldw_dqbh,d.zwwldw_dwbh

select a.zwdqzd_dqbh,a.zwdqzd_dqmc,a.zwwldw_dwbh,a.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,a.sms1,a.sms2 into smsbbb
from
（select
w.zwdqzd_dqbh,w.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,
sum（case when datediff（day, c.xsfpmx_c2,@date) < 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms1,
sum（case when datediff（day, c.xsfpmx_c2,@date) > = 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms2
from （（（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh)
inner join zwdqzd as w on w.zwdqzd_dqbh=d.zwwldw_dqbh
where b.xstd_djrq <=@date and c.xsfpmx_hkbz= '0 '
group by w.zwdqzd_dqbh,w.zwdqzd_dqmc,d.zwwldw_dwmc,d.zwwldw_dwbh) as a left join smsi as d on d.zwwldw_dwbh=a.zwwldw_dwbh
order by a.zwdqzd_dqbh,a.zwwldw_dwbh

insert into smsaaa（zwwldw_dqbh,zwdqzd_dqmc,zwwldw_dwbh,zwwldw_dwmc,xstdmx_zsl_day,xstdmx_bhse_day,xshk_bhke_day,
xstdmx_zsl_m,xstdmx_bhse_m,xshk_bhke_m,hmhtgy_htje,htye,sms1,sms2)
select d.zwdqzd_dqbh,d.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,d.sms1,d.sms2
from smsbbb as d where 1=1 and d.zwwldw_dwbh not in （select zwwldw_dwbh from smsaaa)

if @dqbh <> ' '
begin
select * from smsaaa where 1=1 and zwwldw_dqbh = @dqbh order by zwwldw_dqbh,zwdqzd_dqmc,zwwldw_dwbh
end
else
select * from smsaaa order by zwwldw_dqbh,zwdqzd_dqmc,zwwldw_dwbh

end

go
动态生成数据窗口失败！从字符串转换为datetime时发生语法错误

发表于：2007-05-08 15:42:1132楼得分:0

運行這個語句，將非法的日期找出來，手動修改掉

select * from xsfpmx where isdate（xsfpmx_c2) = 0

发表于：2007-05-08 16:00:2033楼得分:0

select a.zwdqzd_dqbh,a.zwdqzd_dqmc,a.zwwldw_dwbh,a.zwwldw_dwmc,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,
d.xstdmx_zsl_m,d.xstdmx_bhse_m,d.xshk_bhke_m,d.hmhtgy_htje,d.htye,a.sms1,a.sms2 into smsbbb
from
（select
w.zwdqzd_dqbh,w.zwdqzd_dqmc,d.zwwldw_dwbh,d.zwwldw_dwmc,
sum（case when datediff（day, c.xsfpmx_c2,@date) < 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms1,
sum（case when datediff（day, c.xsfpmx_c2,@date) > = 30 then a.xstdmx_bhse - c.xsfpmx_bhke else 0 end) as sms2
from （（（xstdmx as a inner join xstd as b on b.xstd_tdls=a.xstdmx_tdls)
inner join xsfpmx as c on c.xsfpmx_tdls=b.xstd_tdls)
inner join zwwldw as d on d.zwwldw_dwbh=b.xstd_shdkh)
inner join zwdqzd as w on w.zwdqzd_dqbh=d.zwwldw_dqbh
where b.xstd_djrq <=@date and c.xsfpmx_hkbz= '0 '
group by w.zwdqzd_dqbh,w.zwdqzd_dqmc,d.zwwldw_dwmc,d.zwwldw_dwbh) as a left join smsi as d on d.zwwldw_dwbh=a.zwwldw_dwbh
order by a.zwdqzd_dqbh,a.zwwldw_dwbh
这个出的问题

其他资讯