如何以天为单位显示表值 [英] How to show the table values in day wise

问题描述

表1

ID period fromdate todate Value

001 01/2012 02/01/2012 10/01/2012 AB
002 01/2012 05/01/2012 18/01/2012 AL
....

从上表中，我想显示按日输出.

From the above table, i want to show the day wise output.

ID，应从table1中创建期间"，然后应从期间中生成剩余天数"列.

ID, Period should create from the table1, then remaining days column should generate from the period..

日期列应按期间创建，

例如

01/2012 means - 31 days column
02/2012 means - 29 days column
04/2012 means - 30 days column

我想从表1中明智地显示天数

I want to show the value days wise from table1

预期产量

id period 1 2 3 4 5 ...10 11 12 ..18 19 ....31  total (Column Header)

001 01/2012 N AB AB AB AB .... AB N N.... N  31
002 01/2012 N N N N AL....AL AL AL...AL N .....N 31

AL，AB应与表1中的相匹配，其余列应显示为N，总计表示该月的总天数.

AL, AB should match with from table1, remaining column should display as N, total means total days of the particular month.

如何在sql server中执行此操作.

How to do this in sql server.

查询期望的帮助...

Query Help expecting...

推荐答案

您需要根据您的区域设置调整日期格式，但是我可以在SQL 2000(美国日期格式)中使用它:

You'll need to adjust the date format to your locale, but I got this to work in SQL 2000 (USA date format):

create table Table1 (id varchar(3), period varchar(10), fromdate datetime, todate datetime, value varchar(2))
go
insert into Table1 values ('001','01/2012','1/2/2012','1/10/2012','AB')
insert into Table1 values ('002','01/2012','1/5/2012','1/18/2012','AL')
insert into Table1 values ('003','02/2012','2/10/2012','2/12/2012', 'DA')
go

select id
, period
, [1]=case when DATEADD(dd,0,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [2]=case when DATEADD(dd,1,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [3]=case when DATEADD(dd,2,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [4]=case when DATEADD(dd,3,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [5]=case when DATEADD(dd,4,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [6]=case when DATEADD(dd,5,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [7]=case when DATEADD(dd,6,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [8]=case when DATEADD(dd,7,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [9]=case when DATEADD(dd,8,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [10]=case when DATEADD(dd,9,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [11]=case when DATEADD(dd,10,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [12]=case when DATEADD(dd,11,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [13]=case when DATEADD(dd,12,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [14]=case when DATEADD(dd,13,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [15]=case when DATEADD(dd,14,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [16]=case when DATEADD(dd,15,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [17]=case when DATEADD(dd,16,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [18]=case when DATEADD(dd,17,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [19]=case when DATEADD(dd,18,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [20]=case when DATEADD(dd,19,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [21]=case when DATEADD(dd,20,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [22]=case when DATEADD(dd,21,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [23]=case when DATEADD(dd,22,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [24]=case when DATEADD(dd,23,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [25]=case when DATEADD(dd,24,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [26]=case when DATEADD(dd,25,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [27]=case when DATEADD(dd,26,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [28]=case when DATEADD(dd,27,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [29]=case when DATEADD(dd,28,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [30]=case when DATEADD(dd,29,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, [31]=case when DATEADD(dd,30,left(period,2)+'/01/'+right(period,4)) between fromdate and todate then value else 'N' end
, total=DATEDIFF(dd,left(period,2)+'/01/'+right(period,4),DATEADD(m,1,left(period,2)+'/01/'+right(period,4)))
from Table1
go

结果:

这篇关于如何以天为单位显示表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！