Hello,
I'm struggling to find or write a SQL or .net function or SSRS expression to calculate working days between a CreatedDate and ClosedDate, the function must include bank holiday dates.
I'm working from a database where creating SQL functions are not permitted, however I can embed the SQL...
I'm getting the same error even after the change
; with aCTE
AS(
select corraddr as txtField
from dbo.househ
)
,bCTE
AS (
select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField
FROM aCTE )
,cCTE
AS (
select varcharField...
,cCTE
AS (
select varcharField as xmlField
FROM bCTE )
I changed to the above statement and had the error below;
Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.
Hi SabinUE,
I've re-written as;
; with aCTE
AS(
select corraddr as txtField
from dbo.househ
)
,bCTE
AS (
select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField
FROM aCTE )
,cCTE
AS (
select CAST(varcharField as XML) as...
I tried that but it's put all data in first column.
and returned the error;
Msg 9421, Level 16, State 1, Line 1
XML parsing: line 1, character 29, illegal name character
Thanks, where do i include the table and column?
I tried the below but returned an error;
with aCTE
AS(
select CAST(corraddr as TEXT) from dbo.house as txtField
)
,bCTE
If there were more than 5 carriage returns what would happen?
Thanks again,
Javedi
Hi SabinUE,
Just tested it and it really looks good. The text in my initial post was formatted incorrectly, i wanted it to be in columns not rows. Is this something easy to do and also will it work with a column from a table to split lots of addresses?
Thanks for your help.
Javedi
Hello,
I have searched the forum and the internet but have not found a simple solution to splitting a column with data type TEXT where there is a carriage return.
I'd like to use charindex, substring, len but don't know how to combine them to look for data in between the carriage returns (CR)...
Hello,
I have a dataset which I plan to use in a matrix, but only want to be able to show two values in the parameter from a field that has lots more values, but still return all rows associated with the unique ticket number.
Has anyone any ideas how this can be achieved?
Thanks,
Javedi
Hello
I've searched the web but found no joy. I have a chart and would like to add an additional data label to show percentage. I can only switch between a count and a percentage on the same data label, so does any one know of a way i can add another data label, or have both a count and...
Thanks Dhulbert,
Can you elaborate on Option 1 please?
I'm using one database table for each of the two queries each with grid tables in ssrs. I don't understand how to get the parameter to switch between the grid tables, so only displaying one. So the parameter should show "OPEN" and "CLOSED"...
Hello
I have the sample data below that I am trying to flatten from a view on SQL Server 2008. I was hoping there is an easy way to do this but I can't find any after Googling. The problem is with rows 4,5 and 6 which need to be in one column under Non-Reusable Item Details. Any idea's on how...
Hello
I have four datasets (in a matrix grid) in a report and becuase they all return different data based on the parameter values broken down by month (columns), they are not aligned properly and do not look nice. I was wondering if there is a way i can keep the columns consitent, to show the...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.