怎么取给出的时间段之间的每个月最后一天

Question

--测试数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',6
UNION ALL SELECT 'a',7
UNION ALL SELECT 'a',8
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
GO--缺号分布查询
SELECT a.col1,start_col2=a.col2+1,
end_col2=(
SELECT MIN(col2) FROM tb aa
WHERE col1=a.col1 AND col2>a.col2 
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=aa.col1 AND col2=aa.col2-1))
-1
FROM(
SELECT col1,col2 FROM tb
UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录
SELECT DISTINCT col1,0 FROM tb
)a,(SELECT col1,col2=MAX(col2) FROM tb GROUP BY col1)b
WHERE a.col1=b.col1 AND a.col2<b.col2 --过滤掉每组数据中,编号最大的记录
AND NOT EXISTS(
SELECT * FROM tb WHERE col1=a.col1 AND col2=a.col2+1)
ORDER BY a.col1,start_col2
/*--结果
col1       start_col2  end_col2    
-------------- -------------- ----------- 
a          1           1
a          4           5
b          2           4
--*/

Answer 1

declare @d datetime
set @d = getdate()select dateadd(m,1,@d) - day(dateadd(m,1,@d))

Answer 2

create table PricesMonthly (ASXCode varchar(10),Date smalldatetime) 
insert into PricesMonthly values('AAA','1999-07-30') 
insert into PricesMonthly values('AAA','1999-08-31') 
insert into PricesMonthly values('AAA','1999-09-30') 
insert into PricesMonthly values('AAA','1999-10-29') 
insert into PricesMonthly values('AAA','1999-11-30') 
insert into PricesMonthly values('AAA','2000-01-06') 
insert into PricesMonthly values('AAB','2002-09-30') 
insert into PricesMonthly values('AAB','2002-10-31') 
insert into PricesMonthly values('AAB','2002-11-29') 
insert into PricesMonthly values('AAB','2002-12-31') 
insert into PricesMonthly values('AAB','2003-01-31') 
insert into PricesMonthly values('AAB','2003-02-28') 
insert into PricesMonthly values('AAB','2003-03-31') 
insert into PricesMonthly values('AAB','2003-04-30') 
insert into PricesMonthly values('AAB','2003-05-30') 
insert into PricesMonthly values('AAB','2003-06-30') 
insert into PricesMonthly values('AAB','2003-07-31') 
insert into PricesMonthly values('AAB','2003-08-29') 
insert into PricesMonthly values('AAB','2003-09-30') 
insert into PricesMonthly values('AAB','2003-10-31') 
insert into PricesMonthly values('AAB','2003-11-28') 
insert into PricesMonthly values('AAB','2003-12-31') 
insert into PricesMonthly values('AAB','2004-01-30') 
insert into PricesMonthly values('AAB','2004-03-31') 
insert into PricesMonthly values('AAB','2004-04-30') 
insert into PricesMonthly values('AAB','2004-05-31') 
insert into PricesMonthly values('AAB','2004-06-30') 
insert into PricesMonthly values('AAB','2004-07-30') 
insert into PricesMonthly values('AAB','2004-08-31') 
insert into PricesMonthly values('AAB','2004-09-30') 
insert into PricesMonthly values('AAC','2001-08-31') 
insert into PricesMonthly values('AAC','2001-09-28') 
insert into PricesMonthly values('AAC','2001-10-31') 
insert into PricesMonthly values('AAC','2001-11-30') 
insert into PricesMonthly values('AAC','2001-12-31') 
insert into PricesMonthly values('AAC','2002-01-31') 
insert into PricesMonthly values('AAC','2002-02-28') 
insert into PricesMonthly values('AAC','2002-03-28') 
insert into PricesMonthly values('AAC','2002-04-30') 
insert into PricesMonthly values('AAC','2002-05-31') 
insert into PricesMonthly values('AAC','2002-06-28') 
insert into PricesMonthly values('AAC','2002-07-31') 
insert into PricesMonthly values('AAC','2002-08-30') 
insert into PricesMonthly values('AAC','2002-09-30') 
insert into PricesMonthly values('AAC','2002-10-31') 
insert into PricesMonthly values('AAC','2002-11-29') 
insert into PricesMonthly values('AAC','2002-12-31') 
go
--create table PricesMonthly (ASXCode varchar(10),Date smalldatetime) Dateselect a.ASXCode,convert(varchar(10),dateadd(month,1,a.Date),120)
from PricesMonthly a
 join (select ASXCode,Max(Date) Date from PricesMonthly group by ASXCode) b
   on a.ASXCode=b.ASXCode 
     and convert(varchar(8),a.Date,120)<convert(varchar(8),b.Date,120) 
     and not exists(select * from PricesMonthly 
                     where ASXCode=a.ASXCode and convert(varchar(8),Date,120)=convert(varchar(8),dateadd(month,1,a.Date),120))go
drop table PricesMonthly/*
ASXCode    Date
---------- ----------
AAA        1999-12-30
AAB        2004-02-29(2 row(s) affected)*/

Answer 3

－－－－－－－－－
select a.ASXCode,convert(varchar(10),dateadd(month,1,a.Date),120)
from PricesMonthly a
 join (select ASXCode,Max(Date) Date from PricesMonthly group by ASXCode) b
   on a.ASXCode=b.ASXCode 
     and convert(varchar(8),a.Date,120)<convert(varchar(8),b.Date,120) 
     and not exists(select * from PricesMonthly 
                     where ASXCode=a.ASXCode and convert(varchar(8),Date,120)=convert(varchar(8),dateadd(month,1,a.Date),120))
－－－－－－－－－
怎么我执行的时候查不到数据呢？返回空？？？

Answer 4

有没有完整的SQL语句Show一下？

Answer 5

create table #tdm (d datetime)declare @b datetime,@e datetime
select @b=min(date),@e = max(date) from PricesMonthly
while @b<@e
 begin
    set @b = dateadd(m,1,@b) - day(dateadd(m,1,@b))
    if datepart(weekday,@b) in(1,7) or exists(select 1 from Holidays_MarketClosed where Date = @b)
    begin
if datepart(weekday,@b) = 1
        begin
           if exists(select 1 from Holidays_MarketClosed where Date = @b-2)
              insert #tdm select @b-3
           else
              insert #tdm select @b-2
        end
if datepart(weekday,@b) = 7 
        begin
   insert #tdm select @b-1
        end
        if exists(select 1 from Holidays_MarketClosed where Date = @b)
        begin
           if datepart(weekday,@b-1) = 1
              insert #tdm select @b-3
           if datepart(weekday,@b-1) = 7
              insert #tdm select @b-2
        end
        
        set @b = convert(varchar(10),dateadd(month,1,@b),120)
        continue
    end
    else 
    begin
       insert #tdm select @b 
    end
  
    set @b = convert(varchar(10),dateadd(month,1,@b),120)
 end
 
 delete i from #tdm i,Holidays_MarketClosed j where i.d = j.date
 --select * from #tdm 
 
 insert into PricesMonthlyMissdate
 select a.asxcode,b.d 
 from (select ASXCode,b=min(date),e=max(date) from PricesMonthly group by asxcode ) a,#tdm b
 where d between b and e and not exists(select 1 from PricesMonthly where asxcode = a.asxcode and b.d=date)
 order by a.asxcode,b.d drop table #tdm

Answer 6

应该是1999-12-31吧
--代码--------------------------------------------------------------------------
select top 1000 id= identity(int,1,1) into # from sysobjects,syscolumns
select asxcode,d= max(dateadd(day,0-id,nd)) from (
select asxcode,nd= convert(char(7),dateadd(m,b.id+1,a.b),120)+'-01' from  
   (select asxcode,b=min(date),e = datediff(m,min(date),max(date)) from PricesMonthly group by asxcode) a,# b
    where id <e and not exists(select 1 from PricesMonthly where asxcode = a.asxcode and datediff(m,a.b,date) =b.id)
)a ,# b where b.id<=31 and datepart(dw,dateadd(day,0-id,nd)) not in (1,7) --and dateadd(day,0-id,nd) not in(select date from holidays )
group by asxcode,nd
drop table #
go/*结果--------------------------------------------------------------------------
asxcode    d                                                      
---------- ------------------------------------------------------ 
AAB        2004-02-27 00:00:00.000
AAA        1999-12-31 00:00:00.000
--清除------------------------------------------------------------------------*/

Answer 7

/******************************************************************************/
/*回复：20080522004总：00046                                                   */
/*主题：查漏                                                                              */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table PricesMonthly (ASXCode varchar(10),Date smalldatetime) 
insert into PricesMonthly values('AAA','1999-07-30') 
insert into PricesMonthly values('AAA','1999-08-31') 
insert into PricesMonthly values('AAA','1999-09-30') 
insert into PricesMonthly values('AAA','1999-10-29') 
insert into PricesMonthly values('AAA','1999-11-30') 
insert into PricesMonthly values('AAA','2000-01-06') 
insert into PricesMonthly values('AAB','2002-09-30') 
insert into PricesMonthly values('AAB','2002-10-31') 
insert into PricesMonthly values('AAB','2002-11-29') 
insert into PricesMonthly values('AAB','2002-12-31') 
insert into PricesMonthly values('AAB','2003-01-31') 
insert into PricesMonthly values('AAB','2003-02-28') 
insert into PricesMonthly values('AAB','2003-03-31') 
insert into PricesMonthly values('AAB','2003-04-30') 
insert into PricesMonthly values('AAB','2003-05-30') 
insert into PricesMonthly values('AAB','2003-06-30') 
insert into PricesMonthly values('AAB','2003-07-31') 
insert into PricesMonthly values('AAB','2003-08-29') 
insert into PricesMonthly values('AAB','2003-09-30') 
insert into PricesMonthly values('AAB','2003-10-31') 
insert into PricesMonthly values('AAB','2003-11-28') 
insert into PricesMonthly values('AAB','2003-12-31') 
insert into PricesMonthly values('AAB','2004-01-30') 
insert into PricesMonthly values('AAB','2004-03-31') 
insert into PricesMonthly values('AAB','2004-04-30') 
insert into PricesMonthly values('AAB','2004-05-31') 
insert into PricesMonthly values('AAB','2004-06-30') 
insert into PricesMonthly values('AAB','2004-07-30') 
insert into PricesMonthly values('AAB','2004-08-31') 
insert into PricesMonthly values('AAB','2004-09-30') 
insert into PricesMonthly values('AAC','2001-08-31') 
insert into PricesMonthly values('AAC','2001-09-28') 
insert into PricesMonthly values('AAC','2001-10-31') 
insert into PricesMonthly values('AAC','2001-11-30') 
insert into PricesMonthly values('AAC','2001-12-31') 
insert into PricesMonthly values('AAC','2002-01-31') 
insert into PricesMonthly values('AAC','2002-02-28') 
insert into PricesMonthly values('AAC','2002-03-28') 
insert into PricesMonthly values('AAC','2002-04-30') 
insert into PricesMonthly values('AAC','2002-05-31') 
insert into PricesMonthly values('AAC','2002-06-28') 
insert into PricesMonthly values('AAC','2002-07-31') 
insert into PricesMonthly values('AAC','2002-08-30') 
insert into PricesMonthly values('AAC','2002-09-30') 
insert into PricesMonthly values('AAC','2002-10-31') 
insert into PricesMonthly values('AAC','2002-11-29') 
insert into PricesMonthly values('AAC','2002-12-31') 
--create table Holidays(date smalldatetime)go
--代码--------------------------------------------------------------------------
print '小梁的'
select a.ASXCode,convert(varchar(10),dateadd(month,1,a.Date),120)
from PricesMonthly a
 join (select ASXCode,Max(Date) Date from PricesMonthly group by ASXCode) b
   on a.ASXCode=b.ASXCode 
     and convert(varchar(8),a.Date,120)<convert(varchar(8),b.Date,120) 
     and not exists(select * from PricesMonthly 
                     where ASXCode=a.ASXCode and convert(varchar(8),Date,120)=convert(varchar(8),dateadd(month,1,a.Date),120))print 'herb2的'
select top 1000 id= identity(int,1,1) into # from sysobjects,syscolumns
select asxcode,d= max(dateadd(day,0-id,nd)) from (
select asxcode,nd= convert(char(7),dateadd(m,b.id+1,a.b),120)+'-01' from  
   (select asxcode,b=min(date),e = datediff(m,min(date),max(date)) from PricesMonthly group by asxcode) a,# b
    where id <e and not exists(select 1 from PricesMonthly where asxcode = a.asxcode and datediff(m,a.b,date) =b.id)
)a ,# b where b.id<=31 and datepart(dw,dateadd(day,0-id,nd)) not in (1,7) --and dateadd(day,0-id,nd) not in(select date from holidays )
group by asxcode,nd
order by asxcode,nd
drop table #
go
/*结果--------------------------------------------------------------------------
小梁的
ASXCode               
---------- ---------- 
AAA        1999-12-30
AAB        2004-02-29herb2的
asxcode    d                                                      
---------- ------------------------------------------------------ 
AAA        1999-12-31 00:00:00.000
AAB        2004-02-27 00:00:00.000--清除------------------------------------------------------------------------*/
drop table PricesMonthly

Answer 8

---herb2 二等草高手，把你的代码思路说说，看起来停复杂的！

Answer 9

1.生成计数表
select top 1000 id= identity(int,1,1) into # from sysobjects,syscolumns 
2.取每个代码的最小日期,和总月份数
(select asxcode,b=min(date),e = datediff(m,min(date),max(date)) from PricesMonthly group by asxcode) a,# b 
3.通过与＃联合查出确实的月份，并将月份加1，生成日期为缺失月份的下月1号。
select asxcode,nd= convert(char(7),dateadd(m,b.id+1,a.b),120)+'-01' from --生成日期的下月1号  
   (select asxcode,b=min(date),e = datediff(m,min(date),max(date)) from PricesMonthly group by asxcode) a,# b --取每个代码的最小日期,和总月份数。
    where id <e and not exists(select 1 from PricesMonthly where asxcode = a.asxcode and datediff(m,a.b,date) =b.id)--通过最小日期加月份的方法生成每个月，并比较是否在原表中存在改月。
)a ,# b where b.id<=31 and datepart(dw,dateadd(day,0-id,nd)) not in (1,7) --and dateadd(day,0-id,nd) not in(select date from holidays )
group by asxcode,nd

Answer 10

为啥加  where b.id <=31 ？ 

Answer 11

declare @tb table(Mytime datetime)
insert into @tb select '2007-01-10'
insert into @tb select '2007-01-25'
insert into @tb select '2008-01-10'
insert into @tb select '2008-01-20'
insert into @tb select '2008-03-20'
insert into @tb select '2008-03-21'
insert into @tb select '2008-03-28'      
select
 Mytime
from
@tb tb1
where 
substring(convert (varchar,tb1.Mytime,112),7,2)
=(
select 
max(substring(convert (varchar,Mytime,112),7,2))
from 
@tb tb2
where
substring(convert (varchar,tb2.Mytime,112),1,6)=substring(convert (varchar,tb1.Mytime,112),1,6)
)
--结果如下
-- 2007-01-25 00:00:00.000
-- 2008-01-20 00:00:00.000
-- 2008-03-28 00:00:00.000