T-SQL : List of scheduled reports in SSRS

The following query will list all the available report subscriptions in SSRS. The reportserver database basically holds all metadata information [reports, users, schedules, roles etc] of all reports and thereby, querying the same will provide us a lot of useful results, one such example is shown below

SELECT cat.Name        AS ReportName,
       cat.Path        AS ReportPath,
       sub.Description AS Destination,
       usr.UserName    AS ModifiedBy,
       sub.LastStatus  LatestStatus
FROM   ReportServer..Catalog cat
       JOIN ReportServer..reportSchedule repsch
         ON cat.ItemID = repsch.ReportID
       JOIN ReportServer..Subscriptions sub
         ON repsch.SubscriptionID = sub.SubscriptionID
       JOIN ReportServer..Users usr
         ON usr.UserID = cat.ModifiedByID
WHERE  cat.Type = 2
ORDER  BY reportname 

Happy Querying 😉

Advertisements

T-SQL : How to get columns names of all tables

How to get columns names from all tables of a database ? The query should return all the columns of all tables in a particular database, in a order like how it is stored in the tables

The following queries will perform the same,

Query 1:


SELECT b.name AS TableName,
       a.name AS ColumnName
FROM   sys.columns a
       JOIN sys.tables b
         ON a.object_id = b.object_id
ORDER  BY tablename,
          column_id

Query 2:


SELECT a.table_name,
       column_name
FROM   information_schema.columns a
       JOIN information_schema.tables b
         ON a.table_name = b.table_name
WHERE  b.table_type = 'BASE TABLE'
ORDER  BY a.table_name,
          ordinal_position

Query 3:


SELECT b.name,
       a.name
FROM   sys.all_columns a
       JOIN sys.objects b
         ON a.object_id = b.object_id
WHERE  b.type = 'U'
ORDER  BY b.name,
          a.column_id

Output:
ColumnNames

Happy Querying 😉

T-SQL : Gaps and Islands Problem

The table below has set of names and their date values, where some of the date values are missing by month for each Name
For example, the name ‘Test1′ have date range like ’01-Apr-2013′,’01-Jun-2013’and ’01-Aug-2013’ and it is clear that a datevalue for the month of may and July are missing.

The query has to find the missing date values and insert them back to the same table for each names available in it

A Sample of Missing record is shown below,

1, ‘Test1′,’01-May-2013’
1, ‘Test1′,’01-July-2013’

DECLARE @table TABLE
  (
     ID         INT,
     Name       VARCHAR(10 ),
     DateValue DATETIME
   )

Insert into @table
Select 1, 'Test1', '01-Apr-2013' 
Union All
Select 1, 'Test1', '01-Jun-2013' 
Union All
Select 1, 'Test1', '01-Aug-2013' 
Union All
Select 2, 'Test2', '01-Jun-2013' 
Union All
Select 2, 'Test2', '01-Aug-2013' 
Union All
Select 2, 'Test2', '01-Oct-2013' 
Union All
Select 3, 'Test3', '01-Sep-2013' 
Union All
Select 3, 'Test3', '01-Nov-2013' 

select * from @table -- Before Inserting


;WITH cte AS
(
SELECT ID, Name,DateValue FROM
(SELECT ID ,Name, Min( DateValue ) DateValue FROM @table GROUP BY ID,Name) x
UNION ALL
SELECT ID, Name,Dateadd (mm , 1, DateValue ) FROM cte b
WHERE EXISTS
(SELECT 1 FROM @table a
WHERE a. ID = b .id
AND b. DateValue<a .DateValue)

)

INSERT INTO @table
SELECT b. ID,
       b .Name,
       b . DateValue
FROM   @table a
       RIGHT JOIN cte b
               ON a. ID = b .ID
                  AND a. DateValue = b .DateValue
WHERE  a. ID IS NULL

SELECT *
FROM   @table
ORDER  BY ID,
          DateValue 

T-SQL : An Interesting Question – 3

The requirement is as follows,

Filter out records that has three or more words in it

(or)

Filter out records that has two or more spaces in it

There are several ways to perform this and i would like to show some of them,

Declare @table Table(words varchar(50))
insert into @table
select 'Get the query' union
select 'Two words' union
select 'Three' union
select 'Explain' union
select 'See the output' union
select 'Execute the Query' union
select 'Find the Names' union
select 'single' union
select 'see the output below'

select words from @table
where words like '% % %'

select words from @table
where words  LIKE '%| %| %' ESCAPE '|'

select words from @table
where words LIKE '_% _% _%'

select words from @table
where DATALENGTH(words) - DATALENGTH(REPLACE(words,' ',''))>=2

select words from @table
where CHARINDEX(' ',words, CHARINDEX(' ',words, 1) + 1) >  0

select words from @table
where PATINDEX('% % %',words)> 0

Happy Querying 😉

T-SQL : An Interesting Question – 2

Another interesting question that I have read recently, which involves some string operations.

Question:
The whole string given is a alphanumeric (for ex: AP1AA). The requirement is to Stuff ‘0’ before the single digit number (0-9) which is enclosed by Alphabets (AP1AA)).

For Ex:

If you are given a string like AP2JJ then it should be stuffed with a zero before the single digit numerical (2 in this case) and the output should be AP02JJ. When the numeric is of 2 digits, no actions are needed.

Query:

DECLARE @table TABLE (ExString varchar (12))
INSERT INTO @table
SELECT 'AP10AA' UNION
SELECT 'AA10B' UNION
SELECT 'AAA123' UNION
SELECT 'AA1AB1' UNION
SELECT 'AA2B1' UNION
SELECT 'AA03A' UNION
SELECT 'AA1234' UNION
SELECT '2BB' UNION
SELECT 'A9B' UNION
SELECT 'A0B' UNION
SELECT 'A10B'

SELECT
ExString AS ExString_Before ,
CASE WHEN ExString LIKE '%[a-z][0-9][a-z]%'
THEN STUFF (ExString, patindex('%[0-9]%' ,ExString), 0,'0' )
ELSE ExString
END ExString_After
FROM @table

I have shared my query to perform the above stuff operations. Any suggestions and new ideas are welcomed

Happy Querying 😉

T-SQL : An Interesting Question – 1

Recently I found this interesting question asked in a SQL forum,

Let’s say you have 5 columns in a table and you want to know whether these columns values satisfies the following conditions,

  • Three columns should have values > 19
  • Two columns should have values < 20
  • Two columns should have values in Odd
  • Three columns should have values in Even

I have tried and come up with the following query. Share your thoughts and queries through comments,


declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)
insert into @table
select 4, 7, 25, 38, 40 union
select 2, 5, 21, 40, 42 union
select 2, 4, 18, 17, 15 union
select 1, 3, 19, 17, 15 union
select 2, 4, 21, 22, 23 union
select 1, 5, 45, 40, 48 union
select 1, 42, 40, 5, 56

;with cte as (
select Col1,Col2,Col3,Col4,Col5,
case when Col1%2=0 then 1 else 0 end EvenCheck1,
case when Col2%2=0 then 1 else 0 end EvenCheck2,
case when Col3%2=0 then 1 else 0 end EvenCheck3,
case when Col4%2=0 then 1 else 0 end EvenCheck4,
case when Col5%2=0 then 1 else 0 end EvenCheck5,
case when Col1%2=1 then 1 else 0 end OddCheck1,
case when Col2%2=1 then 1 else 0 end OddCheck2,
case when Col3%2=1 then 1 else 0 end OddCheck3,
case when Col4%2=1 then 1 else 0 end OddCheck4,
case when Col5%2=1 then 1 else 0 end OddCheck5,
case when Col1>19 then 1 else 0 end GtrCheck1,
case when Col2>19 then 1 else 0 end GtrCheck2,
case when Col3>19 then 1 else 0 end GtrCheck3,
case when Col4>19 then 1 else 0 end GtrCheck4,
case when Col5>19 then 1 else 0 end GtrCheck5,
case when Col1<20 then 1 else 0 end LsrCheck1,
case when Col2<20 then 1 else 0 end LsrCheck2,
case when Col3<20 then 1 else 0 end LsrCheck3,
case when Col4<20 then 1 else 0 end LsrCheck4,
case when Col5<20 then 1 else 0 end LsrCheck5
from @table
)

select Col1,Col2,Col3,Col4,Col5  from cte
where EvenCheck1+EvenCheck2+EvenCheck3+EvenCheck4+EvenCheck5 = 3
and OddCheck1+OddCheck2+OddCheck3+OddCheck4+OddCheck5 = 2
and GtrCheck1+GtrCheck2+GtrCheck3+GtrCheck4+GtrCheck5 = 3
and LsrCheck1+LsrCheck2+LsrCheck3+LsrCheck4+LsrCheck5 = 2

The same case when can be also used directly in a where condition as shown below,

declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)
insert into @table
select 4, 7, 25, 38, 40 union
select 2, 5, 21, 40, 42 union
select 2, 4, 18, 17, 15 union
select 1, 3, 19, 17, 15 union
select 2, 4, 21, 22, 23 union
select 1, 5, 45, 40, 48 union
select 1, 5, 40, 42, 56

SELECT     * FROM     @table
WHERE
(case when Col1%2=0 then 1 else 0 end+
case when Col2%2=0 then 1 else 0 end+
case when Col3%2=0 then 1 else 0 end+
case when Col4%2=0 then 1 else 0 end+
case when Col5%2=0 then 1 else 0 end) =3
and
(case when Col1%2=1 then 1 else 0 end +
case when Col2%2=1 then 1 else 0 end +
case when Col3%2=1 then 1 else 0 end +
case when Col4%2=1 then 1 else 0 end +
case when Col5%2=1 then 1 else 0 end ) = 2
and
(case when Col1>19 then 1 else 0 end +
case when Col2>19 then 1 else 0 end +
case when Col3>19 then 1 else 0 end +
case when Col4>19 then 1 else 0 end +
case when Col5>19 then 1 else 0 end ) = 3
and
(case when Col1<20 then 1 else 0 end +
case when Col2<20 then 1 else 0 end +
case when Col3<20 then 1 else 0 end +
case when Col4<20 then 1 else 0 end +
case when Col5<20 then 1 else 0 end ) = 2

Output:

image

 

Happy Querying 😉

Split Comma Separated values

September 6, 2012 1 comment

In this thread I would like to share my SQL scripts to split the comma separate values in to  individual values. This script can be effectively used when passing multivalue parameters from the SSRS report to stored procedures.

TSQL – CODE:

DECLARE @text VARCHAR (50)

DECLARE @Delimiter CHAR (1)

DECLARE @length INT

DECLARE @table TABLE

(

value VARCHAR(50 )

)

SET @text = '1,2,4,887,77889,1,132,4,5,455,4478'

SET @Delimiter = ','

SET @length = Len( @text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter , @text) <> 0 )

BEGIN

INSERT INTO @table(value )

SELECT LEFT( @text, Charindex(@Delimiter , @text) - 1 )

SET @text = RIGHT(@text , Len (@text) - Charindex(@Delimiter , @text))

SET @length= Len(@text )

END

ELSE

BEGIN

INSERT INTO @table(value )

SELECT @text

SET @length = 0

END

END

SELECT *

FROM   @table

Result:

image_thumb

This can be also created as a function in SQL Server as shown below,

CREATE FUNCTION dbo.Split(@text      VARCHAR (MAX),

@Delimiter CHAR (1))

RETURNS @table TABLE (VALUE VARCHAR(50))

AS

Begin

DECLARE @length INT

SET @length = Len(@text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter, @text) <> 0 )

BEGIN

INSERT INTO @table(value)

SELECT LEFT(@text, Charindex(@Delimiter, @text) - 1)

SET @text = RIGHT(@text, Len (@text) - Charindex(@Delimiter, @text))

SET @length= Len(@text)

END

ELSE

BEGIN

INSERT INTO @table(value)

SELECT @text

SET @length = 0

END

END

RETURN

END;

To use the function, execute below code,

SELECT * FROM dbo.SPLIT ( '1,2,4,887,77889,1,132,4,5,455,4478',',') 

OUTPUT:

c

%d bloggers like this: