/*
用途：向网站用户显示发布的拍卖信息
说明：@oper=1 --待开盘
@oper=2 --交易中
@oper =3 --已交割
@intsyle=0 --全部品种
@intstyle=1 --空间交易
@intstyle=2 --短信交易
@intstyle=3 --月亮交易
@oper=4 --未结算
@oper=5 --根据交易时间段
Made by:Huazhen
*/
CREATE PROCEDURE [2006_DisplayExchangesForUser]
@oper int=0,
@intstyle smallint=0,
@memberid int=0,
@att smallint=0,
@start nvarchar(50)='',
@end nvarchar(50)=''
AS
declare @s datetime,@e datetime
set @start=isnull(@start,'')
set @end=isnull(@end,'')
if @oper=1 --待开盘
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))>0
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @att=1
begin
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as
avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]
b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not null and
c.userid=@memberid
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @oper=2
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
begin
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
end
else if @att=1
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as
avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]
b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not
null and c.userid=@memberid
and datediff(d,getdate(),cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime))<=0
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime)))>0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @oper=3
begin
if @att=0
begin
if @intstyle>0 --指定交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intstyle=@intstyle and intyear is not null and intmonth is not null and
intday is not null
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else if @intstyle=0 --所有交易品种
begin
if @start<>'' and @end<>'' --指定开盘时间段
begin
set @s=cast(@start as datetime)
set @e=cast(@end as datetime)
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and (cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @s and @e or cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar) as datetime) between @e and @s)
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
else --未指定开盘时间段
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum
(b.sonprice*b.sonnum)/sum(b.sonnum)) as avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a INNER JOIN [2006_Stock_son] b on a.mainid=b.mainid
where isopen=1 and intyear is not null and intmonth is not null and intday is not null
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as
nvarchar)+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast
(intday as nvarchar) as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
end
else if @att=1
select a.mainid as mainid,exgNo,(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar)) as startExg,intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,
--intprice=(case isnull(intprice,-1) when -1 then '-' else cast(intprice as nvarchar) end),
intprice=isnull(intprice,0),
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)) as balDate,
kaitong=(case isopen when 0 then '关闭' when 1 then '开通' else '' end),
max(b.sonprice) as maxsellprice,min(b.sonprice) as minsellprice,(sum(b.sonprice*b.sonnum)/sum(b.sonnum)) as
avgsellprice,sum(b.sonnum) as sumsellnum
from [2006_Stock_Main] a inner join [2006_stock_attention] c on a.mainid=c.mainid inner join [2006_stock_son]
b on a.mainid=b.mainid
where a.isopen=1 and a.intyear is not null and a.intmonth is not null and a.intday is not null and
c.userid=@memberid
and datediff(d,getdate(),dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)
+'-'+cast(intday as nvarchar) as datetime)))<=0
group by b.mainid,a.mainid,exgNo,
dateadd(d,intnum,cast(cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as nvarchar)
as datetime)),
cast(intyear as nvarchar)+'-'+cast(intmonth as nvarchar)+'-'+cast(intday as
nvarchar),intnum,intstyle,ChrStore,Chrdesc,a.Userid,a.dtappenddate,intprice,isopen
order by a.mainid desc
end
GO
晕死,看完都天亮了.
