22,300
社区成员




ALTER PROCEDURE [dbo].[sp_GetNegCap]
-- Add the parameters for the stored procedure here
@Date int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET FMTONLY OFF;
if @Date=0 or @Date is null set @Date=99999999
SELECT sinfo.f_id, share.F_JNPTG * hq.F_CLOSE AS NegCap, share.F_DATE, hq.F_CLOSE, share.F_JNPTG
FROM dbo.T_STOCK_INDEX AS sinfo INNER JOIN
dbo.T_GS_SHARE AS share ON sinfo.f_gsid = share.F_GSID INNER JOIN
(SELECT F_ID, F_DATE, F_CLOSE
FROM dbo.T_HQ_INDEX AS hqinfo
WHERE (F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_HQ_INDEX AS temp
WHERE (F_ID = hqinfo.F_ID) and F_DATE<=@Date))) AS hq ON hq.F_ID = sinfo.f_id
WHERE (share.F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_GS_SHARE AS temp
WHERE (F_GSID = share.F_GSID) and F_DATE<=@Date))
AND (hq.F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_HQ_INDEX AS temp
WHERE (F_ID = hq.F_ID) and F_DATE<=@Date))
ORDER BY sinfo.f_id
END
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
join
(select F_ID, max(F_DATE)F_DATE from T_HQ_INDEX where F_DATE<=@Date group by F_ID) d
on c.F_ID=d.F_ID and c.F_DATE=d.F_DATE
join
(select f_gsid, max(F_DATE)F_DATE from T_GS_SHARE where F_DATE<=@Date group by f_gsid) e
on e.f_gsid=b.f_gsid and e.F_DATE=b.F_DATE
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
join
(select F_ID, max(F_DATE)F_DATE from T_HQ_INDEX where F_DATE<=@Date group by F_ID) d
on c.F_ID=d.F_ID and c.F_DATE=d.F_DATE
where
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE)
declare @Date int
set @Date = 20101020
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
where
c.F_DATE <= @Date and
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE) and
not exists (select 1 from T_HQ_INDEX where F_ID=c.F_ID and F_DATE<=@Date and F_DATE>c.F_DATE)
declare @Date int
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
@Date F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
where
c.F_DATE = @Date and
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE<b.F_DATE)