“TSQL Counting Number of Occurrences of a Text Value “

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c6fbca5f-32dc-421a-8e16-08a9848dfd1e/tsql-counting-number-of-occurrences-of-a-text-value?forum=transactsql

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

Advertisements

REPLACE Function — Multiple Times (T-SQL)

  • 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.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/91b8c523-c6c3-4179-a517-193f7180d6a1/how-to-discard-patterns-in-a-column?forum=transactsql

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


Row_Number In Action — a Sample Code

CREATE TABLE test (Person VARCHAR(20), Action VARCHAR(10), [Date] DATE)
INSERT INTO test
VALUES (‘MAX’,’Sale’,’20100720′),
(‘MAX’,’Sale’,’20101026′),
(‘MAX’,’Sale’,’20101103′),
(‘MAX’,’Sale’,’20110401′),
(‘MAX’,’No Sale’,’20110528′),
(‘MAX’,’Sale’,’20110613′),
(‘MAX’,’Sale’,’20110711′),
(‘MAX’,’Sale’,’20110819′),
(‘MAX’,’No Sale’,’20111001′),
(‘MAX’,’No Sale’,’20111209′),
(‘MAX’,’Sale’,’20120201′),
(‘MAX’,’No Sale’,’20120201′),
(‘MAX’,’Sale’,’20120207′),
(‘MAX’,’Sale’,’20120213′),
(‘MAX’,’No Sale’,’20130706′);
;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
from test)
, 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
FROM mycte2
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

*/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/70d0a54f-a7c1-42c7-afe0-300ecd6d57bd/sql-query?forum=transactsql


FORMAT with SQL Server 2012 –Another Sample Code (escape currency or number format)

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


SSRS Multiple Select Parameter For LIKE Syntax

I came across this question from this thread at MSDN forum:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3e9400ca-cfc3-4f66-8b48-d7e0ee61d969/sql-in-and-like-in-a-sinlge-query-for-ssrs-report?forum=transactsql

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

Web Service Task — SSIS

You can find generate information about Web Service Task:

http://msdn.microsoft.com/en-us/library/ms140114(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms181267(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms187617(v=sql.105).aspx

 

You can check out a step by step walk-through from this link:

http://www.bidn.com/blogs/BillBrannen/bidn-blog/619/ssis-web-service-task-step-by-step


SSRS Report Definition From a Simple Query (SSRS)

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:
http://www.sqlservercentral.com/blogs/juggling_with_sql/2013/08/07/ssrs-download-all-rdl-files-from-report-server-in-one-go/

-- 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