与如下表结构:CREATE TABLE Test (Product CHAR(1),SalesDate DATETIME,SalesQTY INT )
INSERT Test SELECT 'A','2008-01-31',100
UNION ALL SELECT 'A','2008-01-30',100
UNION ALL SELECT 'A','2008-01-21',800
UNION ALL SELECT 'A','2008-02-28',50
UNION ALL SELECT 'A','2008-02-05',500
UNION ALL SELECT 'B','2008-03-05',100
求得每个月月末两天的总销售记录结果:Product Month TotalSales
A 2008-01 200
A 2008-02 50
INSERT Test SELECT 'A','2008-01-31',100
UNION ALL SELECT 'A','2008-01-30',100
UNION ALL SELECT 'A','2008-01-21',800
UNION ALL SELECT 'A','2008-02-28',50
UNION ALL SELECT 'A','2008-02-05',500
UNION ALL SELECT 'B','2008-03-05',100
求得每个月月末两天的总销售记录结果:Product Month TotalSales
A 2008-01 200
A 2008-02 50
from
(
select * from T
where SalesDate in
(
dateadd(day,-1,dateadd(m,1,convert(char(07),SalesDate,120)+'-01')),
dateadd(day,-2,dateadd(m,1,convert(char(07),SalesDate,120)+'-01'))
)
) X
group by product, convert(char(07),SalesDate,120)
INSERT Test SELECT 'A','2008-01-31',100
UNION ALL SELECT 'A','2008-01-30',100
UNION ALL SELECT 'A','2008-01-21',800
UNION ALL SELECT 'A','2008-02-28',50
UNION ALL SELECT 'A','2008-02-05',500
UNION ALL SELECT 'B','2008-03-05',100
GOSELECT Product,Convert(Varchar(7),salesDate,120),SUM(salesQTY)
FROM test WHERE DATEDIFF(dd,CONVERT(VARCHAR(7),DATEADD(mm,1,SalesDate),120) + '-1',SalesDate) BETWEEN -2 AND -1
GROUP BY Product,Convert(Varchar(7),salesDate,120)DROP TABLE test
GO
INSERT Test SELECT 'A','2008-01-31',100
UNION ALL SELECT 'A','2008-01-30',100
UNION ALL SELECT 'A','2008-01-21',800
UNION ALL SELECT 'A','2008-02-28',50
UNION ALL SELECT 'A','2008-02-05',500
UNION ALL SELECT 'B','2008-03-05',100 goselect
Product,
convert(varchar(7),SalesDate,120)SalesDate,
sum(case when datediff(d,dateadd(m,1,convert(varchar(7),SalesDate,120)+'-01'),SalesDate) in(-1,-2) then SalesQTY else 0 end)SalesQTY
from
test
group by Product,convert(varchar(7),SalesDate,120)
Product SalesDate SalesQTY
------- --------- -----------
A 2008-01 200
A 2008-02 50
B 2008-03 0
CREATE TABLE Test (Product CHAR(1),SalesDate DATETIME,SalesQTY INT )
INSERT Test SELECT 'A','2008-01-31',100
UNION ALL SELECT 'A','2008-01-30',100
UNION ALL SELECT 'A','2008-01-21',800
UNION ALL SELECT 'A','2008-02-28',50
UNION ALL SELECT 'A','2008-02-05',500
UNION ALL SELECT 'B','2008-03-05',100
select product, convert(char(07),SalesDate,120) as [month],sum(salesQty) as totalSales
from
(
select * from test
where SalesDate in
(
dateadd(day,-1,dateadd(m,1,convert(char(07),SalesDate,120)+'-01')),
dateadd(day,-2,dateadd(m,1,convert(char(07),SalesDate,120)+'-01'))
)
) X
group by product, convert(char(07),SalesDate,120) /*
product month totalSales
------- ------- -----------
A 2008-01 200
A 2008-02 50
*/
drop table test