I want to display dates of particular month in column header of gridview and
corresponding value should be display under
SELECT
distinct
cardno, name,
min(convert(char(5), time , 108)) over (partition by cardno, CONVERT(varchar(20), time,
102) ) +' | '+max(convert(char(5), time , 108)) over (partition by cardno,
CONVERT(varchar(20), time, 102) ) [exit/in out]
FROM Events
following is the output of above query
cardno name exit/in out 100 baiju 09:30 | 18:30 101 kiran 09:55 | 18:45
i want the output like this
cardno name 2013-07-01 2013-07-12 ...................................2013-07-31
100 baiju 09:30|18:30 null
101 kiran 09:35|18:45 null
means the whole day of month should be display in gridview header .if there is no value it should be null
i tried this example but showing error
declare @dte as datetime ='2013-07-01'
declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1 end DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME([date]) from events FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
SET @Query='SELECT distinct cardno,name,'+ @cols+' from ( select t.Month_date,e.cardno,e.date from #temp t right outer join events on t.Month_date=e.date ) x pivot ( min(convert(char(5), time , 108)) over (partition by cardno, CONVERT(varchar(20), time, 102) ) +' | '+max(convert(char(5), time , 108)) over (partition by cardno, CONVERT(varchar(20), time, 102)
for date in (' + @cols + ')
) p order by Month_date'
exec(@query)
drop table #temp
this is error message
Msg 207, Level 16, State 1, Line 19
Invalid column name 'date'.
Msg 402, Level 16, State 1, Line 36
The data types nvarchar(max) and varchar are incompatible in the '|' operator.
how to solve this
Regards
Baiju
