要求:
以天为单位,统计订单录入数据,如果中间天数没有的默认前一天的 ,而且我现在只有读取数据权限,
比如数据库表中数据,当然实际日期可能更多也不确定
表 order
字段
id ordercode inputdate(datetime型)
1 001 2009/01/01
2 002 2009/01/01
3 003 2009/01/01
4 004 2009/01/02
5 005 2009/01/02
6 006 2009/01/04
7 007 2009/01/04
8 008 2009/01/04
.
.
.
显示效果希望:
2009/01/01 2009/01/02 2009/01/03 2009/01/04
3 5 5 8
这样就能比较每天录入的数据量,当然数据越多列就越多最好是写出sql语句
我的QQ号:89250280,比较急,希望高手指点。分不够再加
以天为单位,统计订单录入数据,如果中间天数没有的默认前一天的 ,而且我现在只有读取数据权限,
比如数据库表中数据,当然实际日期可能更多也不确定
表 order
字段
id ordercode inputdate(datetime型)
1 001 2009/01/01
2 002 2009/01/01
3 003 2009/01/01
4 004 2009/01/02
5 005 2009/01/02
6 006 2009/01/04
7 007 2009/01/04
8 008 2009/01/04
.
.
.
显示效果希望:
2009/01/01 2009/01/02 2009/01/03 2009/01/04
3 5 5 8
这样就能比较每天录入的数据量,当然数据越多列就越多最好是写出sql语句
我的QQ号:89250280,比较急,希望高手指点。分不够再加
select distinct cast(convert(varchar(10),inputdate,120) as datetime) as inputdate
,(select count(1) from order where inputdate<=a.inputdate) as Num
from order a
,(select count(1) from order where inputdate<cast(convert(varchar(10),dateadd(day,1,a.inputdate),120) as datetime)) as Num
from order a
(
date3 datetime,
total1 int,
rank1 int
)
create table #f
(
date2 datetime,
total int
)
create table #T
(
date1 datetime
)
insert into #T
select '2008-2-1' union all
select '2008-2-2' union all
select '2008-2-1' union all
select '2008-2-2' union all
select '2008-2-3' union all
select '2008-2-5'
declare @maxdate datetime
declare @mindate datetime
declare @sql nvarchar(1000)
declare @total int
set @total=0
set @sql='select '
select @maxdate=MAX(date1) from #T
select @mindate=MIN(date1) from #T
insert into #f select date1,COUNT(date1) from #T group by date1
while @mindate<=@maxdate
begin
select @total=@total+total from (select * from #f where date2<=@mindate) A
insert into #Y values(@mindate,@total,1)
set @total=0
set @mindate=DATEADD(dd,1,@mindate)
end
select @sql=@sql+'max(case when date3='''+cast(date3 as nvarchar(10))+''' then total1 else 0 end) ['+convert(nvarchar(10),date3,120)+'],'
from #Y
set @sql=left(@sql,len(@sql)-1)+' from #Y group by rank1'
exec(@sql)
drop table #f
drop table #T
drop table #Y
if object_id('ta')is not null drop table ta
go
create table ta(id int, ordercode varchar(10), inputdate datetime)
insert ta select 1, '001' , '2009/01/01'
insert ta select 2, '002' , '2009/01/01'
insert ta select 3, '003' , '2009/01/01'
insert ta select 4, '004' , '2009/01/02'
insert ta select 5, '005' , '2009/01/02'
insert ta select 6, '006' , '2009/01/04'
insert ta select 7, '007' , '2009/01/04'
insert ta select 8, '008' , '2009/01/04'
insert ta select 9, '009' , '2009/01/06'
declare @mindt datetime,@maxdt datetime
select @mindt=min(inputdate),@maxdt=max(inputdate) from ta
declare @month table(inputdate datetime)
while @mindt<=@maxdt
begin
insert @month select @mindt
set @mindt=dateadd(dd,1,@mindt)
end
if object_id('tempdb..#')is not null drop table #
select b.inputdate,cnt,id=identity(int,1,1) into # from (
select distinct inputdate,cnt=(select count(1) from ta where inputdate <=t.inputdate ) from ta t) a
right join @month b on a.inputdate=b.inputdate
update a set a.cnt=b.cnt from # a inner join # b on a.id=b.id+1 and a.cnt is null
select inputdate ,cnt from #
/*inputdate cnt
------------------------------------------------------ -----------
2009-01-01 00:00:00.000 3
2009-01-02 00:00:00.000 5
2009-01-03 00:00:00.000 5
2009-01-04 00:00:00.000 8
2009-01-05 00:00:00.000 8
2009-01-06 00:00:00.000 9*/