How to show the table values in day wise

问题:

Table1

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, Period should create from the table1, then remaining days column should generate from the period..

Date column should create as per the period,

For example

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

I want to show the value days wise from table1

Expected Output

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 should match with from table1, remaining column should display as N, total means total days of the particular month.

How to do this in sql server.

Query Help expecting...

回答1:

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

Result:

回答2:

    DECLARE @Table1 TABLE(ID VARCHAR(3), Period VARCHAR(10), FromDate DATETIME, ToDate DATETIME, VALUE VARCHAR(2))

    INSERT INTO @Table1
    SELECT '001', '01/2012', '1/2/2012','1/10/2012', 'AB'
    UNION ALL
    SELECT '002', '01/2012', '1/5/2012', '01/18/2012', 'AL'
    UNION ALL
    SELECT '003', '02/2012', '2/10/2012', '02/18/2012', 'AX'


    ;WITH dates (ID, [Date], [Day], VALUE, Total) as (
        SELECT ID, CAST(REPLACE(r.Period,'/','/01/') AS DATETIME), 
        1, 
        CASE when CAST(REPLACE(r.Period,'/','/01/') AS DATETIME) BETWEEN FromDate AND r.ToDate THEN VALUE ELSE 'N' END ,
        day(dateadd(mm,datediff(mm,-1,fromDate),-1))  
        FROM @Table1 r
        UNION ALL
        SELECT d.ID, DATEADD(dd,1,[Date]) , 
        d.[Day] + 1,
        CASE when DATEADD(dd,1,[Date]) BETWEEN FromDate AND r.ToDate THEN r.VALUE ELSE 'N' END , 
        Total
        FROM dates d
        INNER JOIN @Table1 r on d.ID = r.ID
        WHERE [Day] < 31
    )

    select ID,[1]=MAX([1]) ,[2] = MAX([2]), [3]= MAX([3]), [4]= MAX([4]),[5]= MAX([5]), [6]= MAX([6]),[7]= MAX([7]),[8]= MAX([8]),[9]= MAX([9]),[10]= MAX([10]),
    [11]=MAX([11]) ,[12] = MAX([12]), [13]= MAX([13]), [14]= MAX([14]),[15]= MAX([15]), [16]= MAX([16]),[17]= MAX([17]),[18]= MAX([18]),[19]= MAX([19]),[20]= MAX([20]),
    [21]=MAX([21]) ,[22] = MAX([22]), [23]= MAX([23]), [24]= MAX([24]),[25]= MAX([25]), [26]= MAX([26]),[27]= MAX([27]),[28]= MAX([28]),[29]= MAX([29]),[30]= MAX([30]),
    [31]= MAX([31]), Total = MAX(total)
    from dates d
    PIVOT (MAX (d.Value) for [Day] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15]
                ,[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28]
                ,[29],[30],[31])) as x 
    GROUP BY ID