create table #test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into #test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'B') , (2,6,2,'B') , (2,6,3,'F') , (2,6,4,'F') , (2,6,5,'B') , (2,6,6,'F') , (2,6,7,'F') ;with mycte as (select *, row_number() Over(Order by SyStudentID, AdTermID ) rn , row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by SyStudentID, AdTermID ) - row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn3 from #test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by SyStudentID,AdGradeLetterCode, rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By SyStudentID, AdTermID drop table #test
I have a table with a name and number column. The number column has a certain pattern which needs to be discarded. Say, if a record has number like “1234560000789”, only two zeroes should be retained and the rest should be discarded. More than two consecutive zeroes are not allowed as per the business rule.
My take on this question is to convert the bigint to a varchar type. Since the largest bigint is 2^63-1 (9,223,372,036,854,775,807) so Ithe varchar only need size 19.
By using Replace function to replace three ‘000’ to two ’00’ six times to cover all possible bigint numbers. Here is a sample case:
CREATE table Test_Table (Name Varchar(50), ID BIGINT) --DML for the table INSERT INTO Test_Table VALUES('John', 2588500000054) ,('Dave', 65498220004999) ,('Smith', 264955) , ('Smith', 9000000000000000000) select *, Replace(Replace(Replace(Replace(Replace(Replace(Cast(ID as varchar(20)),'000','00'),'000','00'),'000','00'),'000','00') ,'000','00') ,'000','00') as ID2 from Test_Table drop table Test_Table
CREATE TABLE test (Person VARCHAR(20), Action VARCHAR(10), [Date] DATE)
INSERT INTO test
;with mycte as
select *, row_number() Over(order By [Date],Action Desc) rn1
, row_number() Over(partition by Action order By Date, aCTION desc) rn2
, row_number() Over(order By [Date],Action DESC ) – row_number() Over(Partition by Action Order By [Date] ) rn3
, mycte2 as
( Select *,row_number() Over(partition by rn3 order By CASE WHEN Action=’Sale’Then [Date] END, CASE WHEN Action=’No Sale’Then [Date] END DESC) rn4
, row_number() Over(partition by Action, rn3 order By Action ) rn5
from mycte )
SELECT Person,Action,[Date],’Y’ AS flag
WHERE rn4 = 1 OR rn5 = 1
ORDER BY [Date], ACTION DESC
DROP TABLE test
Person Action Date flag
MAX Sale 2010-07-20 Y
MAX No Sale 2011-05-28 Y
MAX Sale 2011-06-13 Y
MAX No Sale 2011-12-09 Y
MAX Sale 2012-02-01 Y
MAX No Sale 2012-02-01 Y
MAX Sale 2012-02-07 Y
MAX No Sale 2013-07-06 Y
create table test ( myInt int) insert into test values (100),(20),(3),(4) SELECT myInt, FORMAT(myInt,'00000') , FORMAT(myInt,'\C0000') , FORMAT(myInt,'\D0000') ,FORMAT(myInt,'\B0000') ,FORMAT(myInt,'0000') FROM test drop table test
I came across this question from this thread at MSDN forum:
After a little research, I came up with a solution to work with a stored procedure. There are two options available for the stored procedure: one is a UDF Split function with a JOIN … Like and the other is to build the query dynamically.
When you use stored procedure for your Datasets, you need to add columns as fileds for the Datasets manually. Of course, you need to set the multiple Select parameter from Parameter list first.
I hope you can follow along with this solution (Two Stored Procedures in the following)
CREATE PROCEDURE mysp @ProjDept nvarchar(2000) as Begin declare @mylike nvarchar(2000)='' DECLARE @Sql NVARCHAR(MAX) SET @mylike=' ProjectDept Like ''%' + REPLACE( @ProjDept,',', '%'' OR ProjectDept Like ''%') +'%''' SET @Sql = N'SELECT ProjectName,[Owner] FROM ProjectTable Where ' + @mylike EXEC sp_executesql @sql End
Create PROCEDURE [dbo].[mysp2] @s nvarchar(2000) as Begin SELECT * from projectTable pt INNER JOIN dbo.Split(@s,',') fs ON pt.ProjectDept + fs.s + '%' End --The s is the column name returned from the SPLIT function I used
You can find generate information about Web Service Task:
You can check out a step by step walk-through from this link:
When you have a need to check your report from SSRS instance, you can run the following query and view the XML content from ReportRDL column to a new query window by clicking on the XML content. You will see the full code of your report. (The Type value 2 in query is representing Report.)SELECT ItemID,Name,[Type] , Path, ModifiedDate ,CAST(CONVERT(varbinary(max),Content) as XML) ReportRDL FROM ReportServer.dbo.Catalog WHERE Type =2 and Name = 'MyreportName'
By using dynamic SQL with bcp command to download all .rdl files to local folder.
Here is a handy script from:
-- Allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- Update the currently configured value for advanced options. RECONFIGURE GO -- Enable xp_cmdshell EXEC sp_configure 'xp_cmdshell', 1 GO -- Update the currently configured value for xp_cmdshell RECONFIGURE GO -- Disallow further advanced options to be changed. EXEC sp_configure 'show advanced options', 0 GO -- Update the currently configured value for advanced options. RECONFIGURE GO--Replace NULL with keywords of the ReportManager's Report Path, --if reports from any specific path are to be downloaded DECLARE @FilterReportPath AS VARCHAR(500) = NULL --Replace NULL with the keyword matching the Report File Name, --if any specific reports are to be downloaded DECLARE @FilterReportName AS VARCHAR(500) = NULL --Replace this path with the Server Location where you want the --reports to be downloaded.. DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\' --Used to prepare the dynamic query DECLARE @TSQL AS NVARCHAR(MAX) --Reset the OutputPath separator. SET @OutputPath = REPLACE(@OutputPath,'\','/') --Simple validation of OutputPath; this can be changed as per ones need. IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = '' BEGIN SELECT 'Invalid Output Path' END ELSE BEGIN --Prepare the query for download. /* Please note the following points - 1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc. 2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer] 3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that. 4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “ï»¿”. While it is supported, it can cause problems with the conversion to XML, so it is removed. */ SET @TSQL = STUFF((SELECT ';EXEC master..xp_cmdshell ''bcp " ' + ' SELECT ' + ' CONVERT(VARCHAR(MAX), ' + ' CASE ' + ' WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+ ' ELSE C.Content '+ ' END) ' + ' FROM ' + ' [ReportServer].[dbo].[Catalog] CL ' + ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + ' WHERE ' + ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x''' FROM [ReportServer].[dbo].[Catalog] CL WHERE CL.[Type] = 2 --Report AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/') AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name) FOR XML PATH('')), 1,1,'') --SELECT @TSQL --Execute the Dynamic Query EXEC SP_EXECUTESQL @TSQL END