Archive

Archive for the ‘T-SQL’ Category

T-SQL : Gaps and Islands Problem – 2

There are lot of ways to find the gaps in a sequential numbers, and some of them are listed below. I have posted some 3 methods to achieve the expected. The goal is to find the gaps between the sequential number. The query should return the Gap start point and Gap end point. Please check these queries and any suggestions/comments are always welcomed.

declare @table table (num int)
insert into @table
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

--Query 1
select GapStart,GapEnd from
( select m.num+ 1 as GapStart
,(select min(num) - 1 from @table as x where x.num > m.num) as GapEnd
from @table as m left outer join @table as r on m.num = r.num- 1 where r.num is null
) as x where GapEnd is not null 

--Query 2 
select a.num1 GapStart,b.num2 GapEnd from 
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)a,
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)b
where a.ID+1 = b.ID
and (a.num1<b.num2 or a.num1 = b.num2) 

--Query 3 
select GapStart, GapEnd from 
(select m.num,m.num+1 as GapStart,ROW_NUMBER() over (order by m.num+1) as ser
from @table m
left join (select num-1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num+1<(select MAX(num) from @table ))x
join 
(select m.num,m.num-1 as GapEnd,ROW_NUMBER() over (order by m.num-1) as ser from @table m
left join (select num+1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num-1>(select MIN(num) from @table ))y
on x.ser=y.ser

Output:
Capture

Happy Querying 😉

T-SQL : Delete duplicates in a table using Query

Following are the queries that will delete all available duplicates in a table, please refer the query below

Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select 'Dinesh',26,145 union all
select 'Latheesh',26,142 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,154 union all
select 'Dinesh',26,145 union all
select 'Dinesh',26,145  

delete del
from
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates) del
where del.row_id >1

select * from @Duplicates

Before deletion:

blog1

Output:

blog2

Alternatively, the same operation can be done using a CTE as shown below

Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select 'Dinesh',26,145 union all
select 'Latheesh',26,142 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,154 union all
select 'Dinesh',26,145 union all
select 'Dinesh',26,145  

;With del
as
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates) 

delete from del
where del.row_id >1

select * from @Duplicates

T-SQL : Query to fetch report subscription details from SSRS

In SSRS, to find who has created/modified the report subscription without using the management site, the following query can be used,

select
reps.Name as ReportName
,usr.UserName createdby
from Subscriptions subs
join Catalog reps on subs.Report_OID = reps.ItemID
join Users usr on subs.OwnerID = usr.UserID​

This is will help in finding who has created the subscriptions in report server DB, rather trying by each users in report manager. When it would be useful ? take for instance, if your customer wants some of the email IDs to be removed in the existing subscriptions. Unless we have the information that who has created the existing subscriptions, the email ids cannot be edited.

Happy Querying 😉

Categories: T-SQL

T-SQL : Get all folder names from SSRS Reports Manager/Server

We all know, we can query the report server database and get very useful information, such as reports details, directory and schedules etc. This particular thread belongs to one such category, this query will provide you all available folders from a report server


select
ltrim(rtrim(Name))
from [dbo].[Catalog]
where type =1
and ParentID is not null

I will soon post other queries that will provide much more useful properties/information from report server.

Happy Querying 😉

T-SQL : Get count of all tables in a database

The following query will list the count of records in all tables of a database. These queries are based on CURSORS,

Query 1:
Query to get individual results,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'
DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName
OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount

Query 2:
Results stored in singe temp table,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'

CREATE TABLE #TableCount
  (
     TableName  VARCHAR (255 ),
     TableCount INT
  )

DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName

OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'insert into #TableCount
      select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount
SELECT *
FROM    #tableCount
DROP TABLE #tableCount

Happy Querying 😉

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 😉

%d bloggers like this: