create table test (业务日期 datetime,业务金额 int) insert test select '2014-4-26',1000select 年= datepart(year,业务日期), 月= case when datepart(DAY,业务日期)>25 then datepart(MONTH,业务日期)+1 else datepart(MONTH,业务日期) end, 周=datename(dw,业务日期) from test
create table test (业务日期 datetime,业务金额 int) insert test select '2014-4-24',1000 union all select '2014-4-25',1000 union all select '2014-4-26',1000 union all select '2014-4-27',1000 union all select '2014-4-28',1000 union all select '2014-4-29',1000 union all select '2014-5-1',1000 select 业务日期, 年= datepart(year,业务日期), 月= case when datepart(DAY,业务日期)>25 then datepart(MONTH,业务日期)+1 else datepart(MONTH,业务日期) end, 周=case when datepart(DAY,业务日期)>25 then datepart(WEEK,业务日期)-datepart(WEEK,CONVERT(datetime,CONVERT(varchar(10),datepart(year,业务日期))+'-'+CONVERT(varchar(10),datepart(MONTH,业务日期))+'-25'))+1 else datepart(WEEK,业务日期)-datepart(WEEK,CONVERT(datetime,CONVERT(varchar(10),datepart(year,业务日期))+'-'+CONVERT(varchar(10),datepart(MONTH,业务日期)-1)+'-25'))+1 end, 业务金额 from test /* 2014-04-24 00:00:00.000 2014 4 5 1000 2014-04-25 00:00:00.000 2014 4 5 1000 2014-04-26 00:00:00.000 2014 5 1 1000 2014-04-27 00:00:00.000 2014 5 2 1000 2014-04-28 00:00:00.000 2014 5 2 1000 2014-04-29 00:00:00.000 2014 5 2 1000 2014-05-01 00:00:00.000 2014 5 2 1000 */
如果有这个表,查询的时候怎么关联?52个Case when?
如果有这个表,查询的时候怎么关联?52个Case when?
between 月初 and 月末
create table test (业务日期 datetime,业务金额 int)
insert test
select '2014-4-24',1000 union all
select '2014-4-25',1000 union all
select '2014-4-26',1000 union all
select '2014-4-27',1000 union all
select '2014-4-28',1000 union all
select '2014-4-29',1000 union all
select '2014-5-1',1000
select 业务日期,
年= datepart(year,业务日期),
月= case when datepart(DAY,业务日期)>25 then datepart(MONTH,业务日期)+1
else datepart(MONTH,业务日期)
end,
周=case when datepart(DAY,业务日期)>25 then datepart(WEEK,业务日期)-datepart(WEEK,CONVERT(datetime,CONVERT(varchar(10),datepart(year,业务日期))+'-'+CONVERT(varchar(10),datepart(MONTH,业务日期))+'-25'))+1
else datepart(WEEK,业务日期)-datepart(WEEK,CONVERT(datetime,CONVERT(varchar(10),datepart(year,业务日期))+'-'+CONVERT(varchar(10),datepart(MONTH,业务日期)-1)+'-25'))+1
end,
业务金额
from test /*
2014-04-24 00:00:00.000 2014 4 5 1000
2014-04-25 00:00:00.000 2014 4 5 1000
2014-04-26 00:00:00.000 2014 5 1 1000
2014-04-27 00:00:00.000 2014 5 2 1000
2014-04-28 00:00:00.000 2014 5 2 1000
2014-04-29 00:00:00.000 2014 5 2 1000
2014-05-01 00:00:00.000 2014 5 2 1000
*/