sql 日期30 60 90天

[GoogleUsers]

testtb:

ID SN Brand CreateDate
1 1001 b1 2012-08-30
2 1002 b1 2012-08-31
3 1003 b2 2012-07-03
4 1004 b2 2012-06-12
5 1005 b1 2012-07-09
6 1006 b1 2012-09-30


条件:

统计
Brand 15天 30天 60天 90天 大于90天
b1 now-15~now now-15~now-30 now-30~now-60 以此类推 now-90~-now-无穷
b2 .. .. ... .. ....

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] INT,[SN] INT,[Brand] VARCHAR(2),[CreateDate] DATETIME)
INSERT [tb]
SELECT 1,1001,'b1','2012-08-30' UNION ALL
SELECT 2,1002,'b1','2012-08-31' UNION ALL
SELECT 3,1003,'b2','2012-07-03' UNION ALL
SELECT 4,1004,'b2','2012-06-12' UNION ALL
SELECT 5,1005,'b1','2012-07-09' UNION ALL
SELECT 6,1006,'b1','2012-09-30'
--------------开始查询--------------------------
SELECT [Brand],
SUM(CASE WHEN [CreateDate] BETWEEN GETDATE()-15 AND GETDATE()THEN 1 ELSE 0 END) ,
SUM(CASE WHEN [CreateDate] BETWEEN GETDATE()-30 AND GETDATE()THEN 1 ELSE 0 END) , 
SUM(CASE WHEN [CreateDate] BETWEEN GETDATE()-60 AND GETDATE()THEN 1 ELSE 0 END) , 
SUM(CASE WHEN [CreateDate] BETWEEN GETDATE()-90 AND GETDATE()THEN 1 ELSE 0 END)  
FROM [tb]
GROUP BY [Brand]
----------------结果----------------------------
/* 
Brand	(无列名)	(无列名)	(无列名)	(无列名)
b1	0	1	2	3
b2	0	0	0	1
*/

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] INT,[SN] INT,[Brand] VARCHAR(2),[CreateDate] DATETIME)
INSERT [tb]
SELECT 1,1001,'b1','2012-08-30' UNION ALL
SELECT 2,1002,'b1','2012-08-31' UNION ALL
SELECT 3,1003,'b2','2012-07-03' UNION ALL
SELECT 4,1004,'b2','2012-06-12' UNION ALL
SELECT 5,1005,'b1','2012-07-09' UNION ALL
SELECT 6,1006,'b1','2012-09-30'
--------------开始查询--------------------------
SELECT [Brand],
SUM(CASE WHEN [CreateDate] > GETDATE()-15 AND  [CreateDate]<= GETDATE() THEN 1 ELSE 0 END) ,
SUM(CASE WHEN [CreateDate] > GETDATE()-30 AND  [CreateDate]<= GETDATE()-15 THEN 1 ELSE 0 END) , 
SUM(CASE WHEN [CreateDate] > GETDATE()-60 AND  [CreateDate]<= GETDATE()-30 THEN 1 ELSE 0 END) , 
SUM(CASE WHEN [CreateDate] < GETDATE()-90 THEN 1 ELSE 0 END)  
FROM [tb]
GROUP BY [Brand]
----------------结果----------------------------
/* 
Brand	(无列名)	(无列名)	(无列名)	(无列名)
b1	0	1	1	0
b2	0	0	0	1*/