Archive

Posts Tagged ‘Sql query’

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 😉

Most frequently used T-SQL Queries

In this thread I would like to share some of the most frequently used SQL queries, following examples will be are demonstrated with Adventure Works and my own databases. There might be alternate sources of the available outside. All I wanted is to share it at the right place. Eventually I will be updating this separate thread with new queries which I am learning from time to time.

Query 1:

It is easy to calculate a cumulative sum or running sum of a measure object in a report but how about doing it in database side with correlated sub query,

DECLARE @table TABLE
(
date   DATE,
Amount INT
)

INSERT INTO @table
SELECT Getdate() – 6,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 5,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 4,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 3,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 2,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate() – 1,Round(Rand(), 2) * 100
UNION ALL
SELECT Getdate(),Round(Rand(), 2) * 100

SELECT *
FROM   @table

SELECT Date,
Amount,
(SELECT Sum(Amount)
FROM   @table
WHERE  Date <= D.Date) AS Running_SUM
FROM   @table D
ORDER  BY date

output:

imagequery1

Query 2:

To get the tables that are modified after its creation,

select * from sys.tables

where

create_date > modify_date

Query 3:

To get the list of tables that are not modified (ALTER) after creating

select * from sys.tables

where

create_date = modify_date

Query 4:

To get the number of columns used in table

select name, max_column_id_used from sys.tables

Query 5:

To know how many tables contains “col1″ as a column name in a database

SELECT COUNT(*) AS NoColumns
FROM syscolumns
WHERE (name = ‘Col1′)

Query 6:

To get the filename from a network/folder path,

Example: ‘E:\blog pictures\rolling\second\img.csv’

Use the following query to extract the filename ‘img.csv’ from the respective path,

Declare @String nvarchar (100) = 'E:\blog pictures\rolling\second\img.csv'

select Right(@string,Charindex('\',REVERSE(@string))-1)
%d bloggers like this: