T SQL Concatenated Row Field

When creating new reports you sometimes need to concatenate data:

 Below is sample script which will allow you to do this using a function.



----------------------------------------------------------------------------
------ create table
----------------------------------------------------------------------------
with cte as (select 123 as customer, '2012-09-18 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-19 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-20 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-21 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-22 00:00:00.000' as orderdate
)

select * into CustomerOrder from cte




----------------------------------------------------------------------------
------ create function to concatenate dates
----------------------------------------------------------------------------
create    function [dbo].[GetCustomerOrderDates](
@customer varchar(20)

)
returns varchar (8000)
as
begin
declare @str varchar (8000)
  set @str = ''
 
if @customer is null
  return ''



select @str = @str + cast(dayz as varchar(300))+': ' FROM
(select top 1000 customer, orderdate,  convert(char(2),datepart(d,orderdate))+' '
+CONVERT(char(3),DATENAME(mm,orderdate))  as
dayz
from  CustomerOrder
where customer =@customer
order by orderdate
) as derived



if (len (@str) > 1)
  set @str = left(@str, len(@str) - 1)

return (@str)
End




----------------------------------------------------------------------------
------ run query and use function to return concatenated dates
----------------------------------------------------------------------------


select distinct customer, dbo.[GetCustomerOrderDates](123) as orderdates
from CustomerOrder

Create a data driven subscription for the last working day

The standard scheduling reporting services 2008 r2 does not include the last day of the month.

A workaround is to use sql code in a data driven subscription to create this schedule.

The code is shown below:

I have used a function to strip out the time ...


CREATE  FUNCTION wfDateWithoutTime
 (@datewithtime DATETIME)
RETURNS SMALLDATETIME
AS
BEGIN
 DECLARE @datestring AS VARCHAR(20)
 SET @datestring = CONVERT(VARCHAR(20),@datewithtime,101)
 RETURN CONVERT(SMALLDATETIME, @datestring)
END

The t sql code is then used in the sql query of the data driven subscription as follows:

with cte as
(select DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -1 as tday)

select * from cte where tday = (select dbo.wfdatewithouttime(getdate()))


You then need to set the data driven subscription to run on a daily basis. The subscription will then only run on the last day of the month.