sql时间段统计

Question

有两张表 TB1   TB2
TB1结构
name(唯一)              cdate
AA                      2005/10/15
BB                      2006/11/30
TB2结构
name(有重复)            date                  TTL
AA                      2008/10/15            10
AA                      2008/11/30            11
AA                      2007/10/10            5
BB                      2009/11/23            11
BB                      2008/11/30            10
BB                      2009/10/10            6  
*******************************************************************
假设当前时间为2009-11-24,则n=11月24  Y=2009
1.如果n<cdate   则m1=(Y-1)/cdate(月/日)  m2=Y/cdate(月/(日-1))
2.如果n>=cdate   则m1=Y/cdate(月/日)  m2=(Y+1)/cdate(月/(日-1))
select sum(TTL) as 和 from  TB2 where date between  m1 and m2结果如下:
name             时间段                            和
AA               2009/10/15 to 2010/10/14          22
BB               2008/11/30 to 2009/11/29          16请教大家这个结果该怎么得到,变量比较多,希望大家指导下,谢谢!

Answer 1

你这个N<CDATE 是什么是日期比较还是月日比较？

Answer 2

对题目理解有限：
DECLARE @curdate datetime 
SET @curdate='2009-11-24';WITH CTE
AS
(
SELECT *, CASE WHEN SUBSTRING(CONVERT(varchar(10),@curdate,120),6,5)
<SUBSTRING(CONVERT(varchar(10),cdate,120),6,5) THEN 
 CAST(YEAR(@curdate)-1 AS VARCHAR(10)) +'-'+SUBSTRING(CONVERT(varchar(10),cdate,120),6,5) 
ELSE 
 CAST(YEAR(@curdate)  AS VARCHAR)+'-'+SUBSTRING(CONVERT(varchar(10),cdate,120),6,5)
 END M1, CASE WHEN SUBSTRING(CONVERT(varchar(10),@curdate,120),6,5)
>=SUBSTRING(CONVERT(varchar(10),cdate,120),6,5) THEN 
 CAST(YEAR(@curdate)  AS VARCHAR)+'-'+SUBSTRING(CONVERT(varchar(10),DATEADD(D,-1,cdate),120),6,5) ELSE 
 CAST(YEAR(@curdate)+1 AS VARCHAR)+'-'+SUBSTRING(CONVERT(varchar(10),DATEADD(D,-1,cdate),120),6,5)
 END M2 FROM TB1
)
 
SELECT * FROM TB2 WHERE date between 
  (SELECT M1 FROM CTE WHERE NAME=TB2.NAME)
and
  (SELECT M2 FROM CTE WHERE NAME=TB2.NAME)
 

Answer 3

楼主你给的结果好像有问题,表2中AA根本没有2009/10/15 to 2010/10/14 这个时间段的记录, 怎么你最终结果跑出来了呢??-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/24
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB1 table([name] varchar(2),[cdate] datetime)
insert @TB1
select 'AA','2005/10/15' union all
select 'BB','2006/11/30'declare @TB2 table([name] varchar(2),[date] datetime,[TTL] int)
insert @TB2
select 'AA','2008/10/15',10 union all
select 'AA','2009/11/30',11 union all
select 'AA','2007/10/10',5 union all
select 'BB','2010/11/23',11 union all
select 'BB','2010/08/30',10 union all
select 'BB','2009/10/10',6
SELECT [name], CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,CDATE) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M1,
CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M2
FROM @TB1
SELECT A.NAME, CONVERT(NVARCHAR(12),A.M1,111)+' TO '+CONVERT(NVARCHAR(12),A.M1,111) AS 时间段, SUM(B.[TTL])
FROM (
SELECT [name], CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,CDATE) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M1,
CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M2
FROM @TB1
) AS A INNER JOIN @TB2 B ON A.[name]=B.[name]
WHERE B.[date] BETWEEN M1 AND M2
GROUP BY A.NAME, A.M1, A.M2--测试结果:
/*
name M1                      M2
---- ----------------------- -----------------------
AA   2009-10-15 00:00:00.000 2010-10-14 00:00:00.000
BB   2009-11-30 00:00:00.000 2010-11-29 00:00:00.000(2 row(s) affected)NAME 时间段                          
---- ---------------------------- -----------
AA   2009/10/15 TO 2009/10/15     11
BB   2009/11/30 TO 2009/11/30     21(2 row(s) affected)*/

Answer 4

上面的时间段搞错了
-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/24
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB1 table([name] varchar(2),[cdate] datetime)
insert @TB1
select 'AA','2005/10/15' union all
select 'BB','2006/11/30'declare @TB2 table([name] varchar(2),[date] datetime,[TTL] int)
insert @TB2
select 'AA','2008/10/15',10 union all
select 'AA','2009/11/30',11 union all
select 'AA','2007/10/10',5 union all
select 'BB','2010/11/23',11 union all
select 'BB','2010/08/30',10 union all
select 'BB','2009/10/10',6
SELECT [name], CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,CDATE) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M1,
CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M2
FROM @TB1
SELECT A.NAME, CONVERT(NVARCHAR(12),A.M1,111)+' TO '+CONVERT(NVARCHAR(12),A.M2,111) AS 时间段, SUM(B.[TTL])
FROM (
SELECT [name], CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,CDATE) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M1,
CAST((case 
when getdate()>=[cdate] then CAST(DATEPART(YEAR,DATEADD(YY,1,GETDATE())) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
ELSE CAST(DATEPART(YEAR,GETDATE()) AS NVARCHAR(10))+'-'+CAST(DATEPART(MM,CDATE) AS NVARCHAR(10))+'-'+CAST(DATEPART(DD,DATEADD(DD,-1,CDATE)) AS NVARCHAR(10))
end) AS DATETIME) AS M2
FROM @TB1
) AS A INNER JOIN @TB2 B ON A.[name]=B.[name]
WHERE B.[date] BETWEEN M1 AND M2
GROUP BY A.NAME, A.M1, A.M2--测试结果:
/*
name M1                      M2
---- ----------------------- -----------------------
AA   2009-10-15 00:00:00.000 2010-10-14 00:00:00.000
BB   2009-11-30 00:00:00.000 2010-11-29 00:00:00.000(2 row(s) affected)NAME 时间段                          
---- ---------------------------- -----------
AA   2009/10/15 TO 2010/10/14     11
BB   2009/11/30 TO 2010/11/29     21(2 row(s) affected)*/

Answer 5

7楼的好，首先感谢你的帮助，可能是我表达不清楚，不是我要的。
如果我把ta1的第二列时间改成2006/11/22，应该比当前11/24小，所以它应该统计
2008/11/22 to 2009/11/22 之间的数据，结果TTL=6,但是按照你的语句结果还是10
再次请教。