如果某一天都丢了,就查不出来了。PricesDaily原表数据如下: ASXCode,Date,Price select * from (select distinct ASXCode from PricesDaily) a, (select distinct date from PricesDaily) b where not exists (select 1 from PricesDaily where a.asxcode = asxcode and b.date = date)
偶尔丢一天的话:select ASXCode, bgnDt=min(Date), endDt=max(Date) into #t1 from PricesDaily group by ASXCodeselect ASXCode, nextDate = case datepart(weekday,a.[Date]) when 5 then 3 -- 周五跳过周六周日 when 6 then 2 -- 周六跳过周日 else 1 end + a.[Date] from PricesDaily a join #t1 b on b.ASXCode = a.ASXCode and a.[Date]<b.endDt where 1=1 and not exists (select 1 from PricesDaily a0 where a0.ASXCode=a.ASXCode and a0.[Date]=case datepart(weekday,a.[Date]) when 5 then 3 -- 周五跳过周六周日 when 6 then 2 -- 周六跳过周日 else 1 end + a.[Date])drop table #t1
declare @PricesDaily table([ASXCode] varchar(3),[Date] datetime,[Price] numeric(5,4)) insert into @PricesDaily select 'AAA','1999-01-04 00:00:00',2.5000 insert into @PricesDaily select 'AAA','1999-01-05 00:00:00',2.4200 insert into @PricesDaily select 'AAA','1999-01-06 00:00:00',2.4500 insert into @PricesDaily select 'AAA','1999-01-07 00:00:00',2.5100 insert into @PricesDaily select 'AAA','1999-01-08 00:00:00',2.5700 insert into @PricesDaily select 'AAA','1999-01-11 00:00:00',2.6200 insert into @PricesDaily select 'AAA','1999-01-12 00:00:00',2.7400 insert into @PricesDaily select 'AAA','1999-01-13 00:00:00',2.6300 insert into @PricesDaily select 'AAA','1999-01-14 00:00:00',2.6400 insert into @PricesDaily select 'AAA','1999-01-15 00:00:00',2.7200 insert into @PricesDaily select 'AAA','1999-01-19 00:00:00',2.6400 insert into @PricesDaily select 'AAB','2004-09-24 00:00:00',.8400 insert into @PricesDaily select 'AAB','2004-09-27 00:00:00',.8400 insert into @PricesDaily select 'AAB','2004-09-28 00:00:00',.8300 insert into @PricesDaily select 'AAB','2004-09-29 00:00:00',.8400 insert into @PricesDaily select 'AAB','2004-09-30 00:00:00',.8300 insert into @PricesDaily select 'AAB','2004-10-01 00:00:00',.8300 insert into @PricesDaily select 'AAB','2004-10-04 00:00:00',.8000 insert into @PricesDaily select 'AAB','2004-10-05 00:00:00',.8300 insert into @PricesDaily select 'AAB','2004-10-07 00:00:00',.8300 insert into @PricesDaily select 'AAB','2004-10-08 00:00:00',.7900--代码-------------------------------------------------------------------------- declare @td table(d datetime)--自动生成有效日期表 declare @th table(d datetime) --你自己加入对应的节假日,不包含周末。 declare @b datetime,@e datetime select @b=min(date),@e = max(date) from @pricesdaily while @b<@e begin set @b = @b+1 if datepart(weekday,@b) in(1,7) or exists(select 1 from @th where d = @b) continue insert @td select @b end select a.asxcode,b.d from (select ASXCode,b=min(date),e=max(date) from @pricesdaily group by asxcode ) a,@td b where d between b and e and not exists(select 1 from @pricesdaily where asxcode = a.asxcode and b.d=date) go /*结果-------------------------------------------------------------------------- asxcode d ------- ------------------------------------------------------ AAA 1999-01-18 00:00:00.000 AAB 2004-10-06 00:00:00.000 --清除------------------------------------------------------------------------*/
--1、假设节假日为每年的'01-01','05-01','05-02','05-03','10-01','10-02','10-03','10-04','10-05','10-06','10-07',未考虑春节及05-04到05-07。 --2、需要借助创建一临时表tmp来解决。 --3、本例查询将排除掉2004-10-06这个日期。create table tb(ASXCode varchar(10),Date datetime,Price decimal(18,4)) insert into tb values('AAA', '1999-01-04', 2.5000) insert into tb values('AAA', '1999-01-05', 2.4200) insert into tb values('AAA', '1999-01-06', 2.4500) insert into tb values('AAA', '1999-01-07', 2.5100) insert into tb values('AAA', '1999-01-08', 2.5700) insert into tb values('AAA', '1999-01-11', 2.6200) insert into tb values('AAA', '1999-01-12', 2.7400) insert into tb values('AAA', '1999-01-13', 2.6300) insert into tb values('AAA', '1999-01-14', 2.6400) insert into tb values('AAA', '1999-01-15', 2.7200) insert into tb values('AAA', '1999-01-19', 2.6400) insert into tb values('AAB', '2004-09-24', .8400) insert into tb values('AAB', '2004-09-27', .8400) insert into tb values('AAB', '2004-09-28', .8300) insert into tb values('AAB', '2004-09-29', .8400) insert into tb values('AAB', '2004-09-30', .8300) insert into tb values('AAB', '2004-10-01', .8300) insert into tb values('AAB', '2004-10-04', .8000) insert into tb values('AAB', '2004-10-05', .8300) insert into tb values('AAB', '2004-10-07', .8300) insert into tb values('AAB', '2004-10-08', .7900) goSELECT TOP 8000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b select t1.asxcode , dateadd(day , tmp.id , t1.min_date) date from ( select asxcode , min(date) min_date , max(date) max_date from tb group by asxcode ) t1 , tmp where dateadd(day , tmp.id , t1.min_date) <= t1.max_date and datepart(weekday,dateadd(day , tmp.id , t1.min_date)) <> 1 and datepart(weekday,dateadd(day , tmp.id , t1.min_date)) <> 7 and right(convert(varchar(10),dateadd(day , tmp.id , t1.min_date),120),5) not in ('01-01','05-01','05-02','05-03','10-01','10-02','10-03','10-04','10-05','10-06','10-07') and not exists(select 1 from tb where asxcode = t1.asxcode and date = dateadd(day , tmp.id , t1.min_date))drop table tb , tmp/* asxcode date ---------- ------------------------------------------------------ AAA 1999-01-18 00:00:00.000(所影响的行数为 1 行) */
try: use Test Declare @1 Table(ASXCode char(3),Date datetime,Price money) Insert Into @1 Select 'AAA', '1999-01-04 00:00:00', 2.5000 Union All Select 'AAA', '1999-01-05 00:00:00', 2.4200 Union All Select 'AAA', '1999-01-06 00:00:00', 2.4500 Union All Select 'AAA', '1999-01-07 00:00:00', 2.5100 Union All Select 'AAA', '1999-01-08 00:00:00', 2.5700 Union All Select 'AAA', '1999-01-11 00:00:00', 2.6200 Union All Select 'AAA', '1999-01-12 00:00:00', 2.7400 Union All Select 'AAA', '1999-01-13 00:00:00', 2.6300 Union All Select 'AAA', '1999-01-14 00:00:00', 2.6400 Union All Select 'AAA', '1999-01-15 00:00:00', 2.7200 Union All Select 'AAA', '1999-01-19 00:00:00', 2.6400 Union All Select 'AAB', '2004-09-24 00:00:00', .8400 Union All Select 'AAB', '2004-09-27 00:00:00', .8400 Union All Select 'AAB', '2004-09-28 00:00:00', .8300 Union All Select 'AAB', '2004-09-29 00:00:00', .8400 Union All Select 'AAB', '2004-09-30 00:00:00', .8300 Union All Select 'AAB', '2004-10-01 00:00:00', .8300 Union All Select 'AAB', '2004-10-04 00:00:00', .8000 Union All Select 'AAB', '2004-10-05 00:00:00', .8300 Union All Select 'AAB', '2004-10-07 00:00:00', .8300 Union All Select 'AAB', '2004-10-08 00:00:00', .7900 ;With T1 As ( Select ASXCode,Startdate=Min(Date),EndDate=Max(Date) From @1 Group By ASXCode ) ,T2 As ( Select ASXCode,Date=Startdate From T1 Union All Select a.ASXCode,Date=a.Date+1 From T2 a,T1 b Where a.ASXCode=b.ASXCode And a.Date<b.EndDate ) Select a.* From T2 a Left Outer Join @1 b On b.ASXCode=a.ASXCode And a.Date=b.Date Where b.Date Is null And Datename(dw,a.date) Not In('星期六','星期日') --其他假日条件可以自由加入/*(21 行受影响) ASXCode Date ------- ----------------------- AAB 2004-10-06 00:00:00.000 AAA 1999-01-18 00:00:00.000(2 行受影响) */
为了防止递归超过默认的100行,可以加参数 Option(Maxrecursion 0)e.g:.... .... Select a.* From T2 a Left Outer Join @1 b On b.ASXCode=a.ASXCode And a.Date=b.Date Where b.Date Is null And Datename(dw,a.date) Not In('星期六','星期日') Option(Maxrecursion 0)
ASXCode,Date,Price select * from
(select distinct ASXCode from PricesDaily) a,
(select distinct date from PricesDaily) b
where not exists
(select 1 from PricesDaily where a.asxcode = asxcode and b.date = date)
AAA.1999-01-18,1999-01-20~2004-10-08
AAB.1999-01-04~2004-09-23,2004-10-06
?
先找出AAA公司的丢失了哪些Date的行;
然后再找AAB公司的;
一个一个公司来判断,如果AAA公司数据很多(比如从2000年到2008年每天交易的数据),说不定这个公司丢失了好多行呢。
AAA.1999-01-18,1999-01-20~2004-10-08
AAB.1999-01-04~2004-09-23,2004-10-06
?----------回复-----
只考虑某个ASXCode 公司,Date日期从有数据的那天到结束的那天中间丢失的。(节假日和周末不算丢失)。
上面的原表例子我只是告诉你:
AAA公司从第一行1999-01-04 到最后一行1999-01-19 结束,要找其中丢失的。
-------
这个办法不现实,现在PricesDaily原表都有几百万的数据呢,再去生成一个没有丢失的表好像不好生成!
-------你的方法得到的结果不对,
不是我想要找到的:ASXCode = 'AAA',Date = '1999-01-18' 和 ASXCode = 'AAB',Date = '2004-10-06'; -------你的方法得到的是:
AAA 2004-09-24 00:00:00
AAA 2004-09-27 00:00:00
AAA 2004-09-28 00:00:00
AAA 2004-09-29 00:00:00
AAA 2004-09-30 00:00:00
AAA 2004-10-01 00:00:00
AAA 2004-10-04 00:00:00
AAA 2004-10-05 00:00:00
AAA 2004-10-07 00:00:00
AAA 2004-10-08 00:00:00
AAB 1999-01-04 00:00:00
AAB 1999-01-05 00:00:00
AAB 1999-01-06 00:00:00
AAB 1999-01-07 00:00:00
AAB 1999-01-08 00:00:00
AAB 1999-01-11 00:00:00
AAB 1999-01-12 00:00:00
AAB 1999-01-13 00:00:00
AAB 1999-01-14 00:00:00
AAB 1999-01-15 00:00:00
AAB 1999-01-19 00:00:00
into #t1
from PricesDaily
group by ASXCodeselect ASXCode,
nextDate = case datepart(weekday,a.[Date])
when 5 then 3 -- 周五跳过周六周日
when 6 then 2 -- 周六跳过周日
else 1 end + a.[Date]
from PricesDaily a
join #t1 b on b.ASXCode = a.ASXCode and a.[Date]<b.endDt
where 1=1
and not exists (select 1 from PricesDaily a0
where a0.ASXCode=a.ASXCode
and a0.[Date]=case datepart(weekday,a.[Date])
when 5 then 3 -- 周五跳过周六周日
when 6 then 2 -- 周六跳过周日
else 1 end + a.[Date])drop table #t1
/******************************************************************************/
/*回复:20080520005总:00034 */
/*主题:出入数据 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @PricesDaily table([ASXCode] varchar(3),[Date] datetime,[Price] numeric(5,4))
insert into @PricesDaily select 'AAA','1999-01-04 00:00:00',2.5000
insert into @PricesDaily select 'AAA','1999-01-05 00:00:00',2.4200
insert into @PricesDaily select 'AAA','1999-01-06 00:00:00',2.4500
insert into @PricesDaily select 'AAA','1999-01-07 00:00:00',2.5100
insert into @PricesDaily select 'AAA','1999-01-08 00:00:00',2.5700
insert into @PricesDaily select 'AAA','1999-01-11 00:00:00',2.6200
insert into @PricesDaily select 'AAA','1999-01-12 00:00:00',2.7400
insert into @PricesDaily select 'AAA','1999-01-13 00:00:00',2.6300
insert into @PricesDaily select 'AAA','1999-01-14 00:00:00',2.6400
insert into @PricesDaily select 'AAA','1999-01-15 00:00:00',2.7200
insert into @PricesDaily select 'AAA','1999-01-19 00:00:00',2.6400
insert into @PricesDaily select 'AAB','2004-09-24 00:00:00',.8400
insert into @PricesDaily select 'AAB','2004-09-27 00:00:00',.8400
insert into @PricesDaily select 'AAB','2004-09-28 00:00:00',.8300
insert into @PricesDaily select 'AAB','2004-09-29 00:00:00',.8400
insert into @PricesDaily select 'AAB','2004-09-30 00:00:00',.8300
insert into @PricesDaily select 'AAB','2004-10-01 00:00:00',.8300
insert into @PricesDaily select 'AAB','2004-10-04 00:00:00',.8000
insert into @PricesDaily select 'AAB','2004-10-05 00:00:00',.8300
insert into @PricesDaily select 'AAB','2004-10-07 00:00:00',.8300
insert into @PricesDaily select 'AAB','2004-10-08 00:00:00',.7900--代码--------------------------------------------------------------------------
declare @td table(d datetime)--自动生成有效日期表
declare @th table(d datetime)
--你自己加入对应的节假日,不包含周末。
declare @b datetime,@e datetime
select @b=min(date),@e = max(date) from @pricesdaily
while @b<@e
begin
set @b = @b+1
if datepart(weekday,@b) in(1,7) or exists(select 1 from @th where d = @b) continue
insert @td select @b
end
select a.asxcode,b.d
from (select ASXCode,b=min(date),e=max(date) from @pricesdaily group by asxcode ) a,@td b
where d between b and e and not exists(select 1 from @pricesdaily where asxcode = a.asxcode and b.d=date)
go
/*结果--------------------------------------------------------------------------
asxcode d
------- ------------------------------------------------------
AAA 1999-01-18 00:00:00.000
AAB 2004-10-06 00:00:00.000
--清除------------------------------------------------------------------------*/
--2、需要借助创建一临时表tmp来解决。
--3、本例查询将排除掉2004-10-06这个日期。create table tb(ASXCode varchar(10),Date datetime,Price decimal(18,4))
insert into tb values('AAA', '1999-01-04', 2.5000)
insert into tb values('AAA', '1999-01-05', 2.4200)
insert into tb values('AAA', '1999-01-06', 2.4500)
insert into tb values('AAA', '1999-01-07', 2.5100)
insert into tb values('AAA', '1999-01-08', 2.5700)
insert into tb values('AAA', '1999-01-11', 2.6200)
insert into tb values('AAA', '1999-01-12', 2.7400)
insert into tb values('AAA', '1999-01-13', 2.6300)
insert into tb values('AAA', '1999-01-14', 2.6400)
insert into tb values('AAA', '1999-01-15', 2.7200)
insert into tb values('AAA', '1999-01-19', 2.6400)
insert into tb values('AAB', '2004-09-24', .8400)
insert into tb values('AAB', '2004-09-27', .8400)
insert into tb values('AAB', '2004-09-28', .8300)
insert into tb values('AAB', '2004-09-29', .8400)
insert into tb values('AAB', '2004-09-30', .8300)
insert into tb values('AAB', '2004-10-01', .8300)
insert into tb values('AAB', '2004-10-04', .8000)
insert into tb values('AAB', '2004-10-05', .8300)
insert into tb values('AAB', '2004-10-07', .8300)
insert into tb values('AAB', '2004-10-08', .7900)
goSELECT TOP 8000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b select t1.asxcode , dateadd(day , tmp.id , t1.min_date) date from
(
select asxcode , min(date) min_date , max(date) max_date from tb group by asxcode
)
t1 , tmp
where dateadd(day , tmp.id , t1.min_date) <= t1.max_date and
datepart(weekday,dateadd(day , tmp.id , t1.min_date)) <> 1 and datepart(weekday,dateadd(day , tmp.id , t1.min_date)) <> 7 and
right(convert(varchar(10),dateadd(day , tmp.id , t1.min_date),120),5) not in ('01-01','05-01','05-02','05-03','10-01','10-02','10-03','10-04','10-05','10-06','10-07') and
not exists(select 1 from tb where asxcode = t1.asxcode and date = dateadd(day , tmp.id , t1.min_date))drop table tb , tmp/*
asxcode date
---------- ------------------------------------------------------
AAA 1999-01-18 00:00:00.000(所影响的行数为 1 行)
*/
use Test
Declare @1 Table(ASXCode char(3),Date datetime,Price money)
Insert Into @1
Select 'AAA', '1999-01-04 00:00:00', 2.5000 Union All
Select 'AAA', '1999-01-05 00:00:00', 2.4200 Union All
Select 'AAA', '1999-01-06 00:00:00', 2.4500 Union All
Select 'AAA', '1999-01-07 00:00:00', 2.5100 Union All
Select 'AAA', '1999-01-08 00:00:00', 2.5700 Union All
Select 'AAA', '1999-01-11 00:00:00', 2.6200 Union All
Select 'AAA', '1999-01-12 00:00:00', 2.7400 Union All
Select 'AAA', '1999-01-13 00:00:00', 2.6300 Union All
Select 'AAA', '1999-01-14 00:00:00', 2.6400 Union All
Select 'AAA', '1999-01-15 00:00:00', 2.7200 Union All
Select 'AAA', '1999-01-19 00:00:00', 2.6400 Union All
Select 'AAB', '2004-09-24 00:00:00', .8400 Union All
Select 'AAB', '2004-09-27 00:00:00', .8400 Union All
Select 'AAB', '2004-09-28 00:00:00', .8300 Union All
Select 'AAB', '2004-09-29 00:00:00', .8400 Union All
Select 'AAB', '2004-09-30 00:00:00', .8300 Union All
Select 'AAB', '2004-10-01 00:00:00', .8300 Union All
Select 'AAB', '2004-10-04 00:00:00', .8000 Union All
Select 'AAB', '2004-10-05 00:00:00', .8300 Union All
Select 'AAB', '2004-10-07 00:00:00', .8300 Union All
Select 'AAB', '2004-10-08 00:00:00', .7900 ;With T1
As
(
Select ASXCode,Startdate=Min(Date),EndDate=Max(Date)
From @1
Group By ASXCode
)
,T2
As
(
Select ASXCode,Date=Startdate
From T1
Union All
Select a.ASXCode,Date=a.Date+1
From T2 a,T1 b Where a.ASXCode=b.ASXCode And a.Date<b.EndDate
)
Select a.*
From T2 a
Left Outer Join @1 b On b.ASXCode=a.ASXCode And a.Date=b.Date
Where b.Date Is null And Datename(dw,a.date) Not In('星期六','星期日')
--其他假日条件可以自由加入/*(21 行受影响)
ASXCode Date
------- -----------------------
AAB 2004-10-06 00:00:00.000
AAA 1999-01-18 00:00:00.000(2 行受影响)
*/
Option(Maxrecursion 0)e.g:....
....
Select a.*
From T2 a
Left Outer Join @1 b On b.ASXCode=a.ASXCode And a.Date=b.Date
Where b.Date Is null And Datename(dw,a.date) Not In('星期六','星期日')
Option(Maxrecursion 0)
谢谢各位!大家都很厉害!!!