解决方案 »
- 在sql2005的 Management Studio 中修改表的列视图如何自定义?
- Gridview控件, 能不能在没有数据源的时候也显示出来
- 【求验证日期的方法】
- case语句,很简单的
- 如何利用XP操作系统连接其他XP操作系统中的SQL Server服务器?在线等
- 如何备份和还原MYSQL的数据表
- 想问delphi变量怎么给到存储过程中
- 如何用ado交替访问本地和远程数据库?
- 求救 6 张表同时查询,统计,求和和排序?????????
- 疑惑。。。。希望高手能帮忙解惑。。。谢了`
- SQLSERVER browser 无法启动 选项是灰的求破
- 怎么全文搜索数据库数据 ? sqlservice
我只需要所选月份及前一个月的数据。
sql server 2008。
SET @MONTH='2014-07-01'
SELECT ID,Name
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [preSelectedMonth-f-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [preSelectedMonth-f-Total2]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [preSelectedMonth-m-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [preSelectedMonth-m-Total2]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [preSelectedMonth-l-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [preSelectedMonth-l-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [SelectedMonth-f-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [SelectedMonth-f-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [SelectedMonth-m-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [SelectedMonth-m-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [SelectedMonth-l-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [SelectedMonth-l-Total2]
FROM TB感觉只能这样了
create table test(id int,[name] varchar(30),total1 decimal(18,6),total2 decimal(18,6),date1 smalldatetime)
insert into test
select 1, 'Name1', 8.981132, 476 ,'6/15/2014' union all
select 1, 'Name2', 9 , 18 ,'7/9/2014' union all
select 1, 'Name3', 14.589062, 1203.16 ,'7/25/2014' union all
select 1, 'Name4', 30.750759, 2023.4 ,'7/11/2014' union all
select 2, 'Name1', 7 , 7 ,'6/13/2014' union all
select 2, 'Name2', 13.491984, 5714.8 ,'7/22/2014' union all
select 2, 'Name2', 26.976811, 5921.68 ,'7/18/2014' declare @sql varchar(max)
select @sql=isnull(@sql+',','')+' sum(case when day(date1) between 1 and 10 then total1 else 0 end) as ['+ cast(month(date1) as varchar)+'-上旬-total1],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total1 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total1],'+char(10)+
' sum(case when day(date1)>20 then total1 else 0 end) as [' +cast(month(date1) as varchar)+'-下旬-total1],'+char(10)+
' sum(case when day(date1) between 1 and 10 then total2 else 0 end) as ['+ cast(month(date1) as varchar)+'-上旬-total2],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total2],'+char(10)+
' sum(case when day(date1)>20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'下旬-total2]'
from test group by month(date1)set @sql='select id,[name],'+@sql
+'from test group by id,name,convert(varchar(6),date1,112)'
--print @sql
exec(@sql)