sql query to show average of each tag present in proficy historian

See more:

SQL

historians

SQL-Server-2008R2

i am asp.net developer. currently i am working on proficy historian which is very new to me. i need to develop ssrs report in which user will provide month as varchar(2) and year as varchar(4) after that when user click on view report button then ssrs report should display result as follows. (suppose if user pass month as 12 and year as 2013)

Date TagName Average

12/01/13 Tag01 45.23

12/01/13 Tag02 89.23

12/02/13 Tag01 2.363

12/02/13 Tag02 45.23

.

.

.

12/31/13 Tag01 55.24

12/31/13 Tag02 95.24

for that i designed a query

create table #tempval
(
    [timestamp] varchar(30),
    tagname varchar(300),
    tagval decimal(18,5)
)
declare @month varchar(2)
declare @year varchar(4)
set @month='08'
set @year='2014'
    declare @startdate varchar(30)
    declare @enddate varchar(30)
    set @startdate=@month+'/01/'+@year
    if (convert(int,@month)>= MONTH(GETDATE()) and convert(int,@year)>=YEAR(GETDATE()))
    begin
        set @enddate=convert(varchar,(select GETDATE()))
    end
    else
    begin
        set @enddate=convert(varchar,DATEADD(day,-1,DATEADD(month,1,convert(datetime,@startdate))))
    end
    declare @query varchar(1000)
    declare @starttime varchar(30)
    declare @endtime varchar(30)
    set @starttime= convert(varchar,datepart(month,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+'/'+convert(varchar,datepart(DAY,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+'/'+convert(varchar,datepart(YEAR,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+' '+convert(varchar,datepart(HOUR,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+':'+convert(varchar,datepart(MINUTE,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+':'+convert(varchar,datepart(SECOND,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))
    set @endtime=convert(varchar,datepart(month,dateadd(hour,22,convert(datetime,@startdate))))+'/'+convert(varchar,datepart(DAY,dateadd(hour,22,convert(datetime,@startdate))))+'/'+convert(varchar,datepart(YEAR,dateadd(hour,22,convert(datetime,@startdate))))+' '+convert(varchar,datepart(HOUR,dateadd(hour,22,convert(datetime,@startdate))))+':'+convert(varchar,datepart(MINUTE,dateadd(hour,22,convert(datetime,@startdate))))+':'+convert(varchar,datepart(SECOND,dateadd(hour,22,convert(datetime,@startdate))))
    set @query='select * from openquery(muri,''set StartTime='''''+@starttime+''''',EndTime='''''+@endtime+''''' select * from ihrawdata where tagname=MURISERVER.MURI.DCS.ASSETS.87A.87A_FI_2101.DACA.PV  and samplingmode=rawbytime'')'
    exec (@query)

but it display blank columns plz help