Find physical path for Database backup file in SQL Server

This is the query to find the physical location for backed up database file.


SELECT        
physical_device_name,
    backup_start_date,
    backup_finish_date,
    backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE database_name = 'Testdb'
ORDER BY backup_finish_date DESC

Output :

Get XML Response From Web URL

First set the following option in sql :
sp_configure 'show advanced option',1
reconfigure

Sp_configure 'Ole Automation Procedures',1
Reconfigure

Then after run the following script by passing your web URL to get the XML response.

DECLARE @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object

--load into Xml
Declare @XmlResponse as xml;


select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse

Find exact Age using Birth date

Create this scalar function to get the exact Age.

ALTER FUNCTION GetAgeByBirthDate(
@BirthDate DATETIME,
@CurrentDate DATE
)
RETURNS VARCHAR(200)
BEGIN
DECLARE @Year INT,
@Month INT,
@Day INT,
@Age VARCHAR(200)



IF MONTH(@BirthDate) <> MONTH(@CurrentDate) BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)-1
SELECT @Month = DATEDIFF(mm,DATEADD(yy,@Year,@BirthDate),@CurrentDate)

END ELSE BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)
SELECT @Month = 0
END

IF DAY(@BirthDate) <= DAY(@CurrentDate) BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END ELSE BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month-1,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END

SELECT @Age = 'Your age is ' + CAST(@Year AS VARCHAR(4)) + ' YEARS ' + CAST(@Month AS VARCHAR(4)) + ' MONTHS and ' + CAST(@Day AS VARCHAR(4)) + ' DAYS.'

RETURN @Age
END

After creating this function run this query :
SELECT dbo.GetAgeByBirthDate('1981-12-19',GETDATE())

First argument is the birth date and second is the current date.

Recursive CTE in SQL SERVER

This shows the use of recursive cte that displays a next six month based on passing the values (Year and Month).


DECLARE @year int,
@month int

SELECT @year = 2010,
@month = 9

;WITH cte AS (
SELECT
DATEADD(MONTH,@month-1,DATEADD(year,@year-1,cast('00010101' as date))) as date1
UNION ALL
SELECT DATEADD(month,1,date1)
FROM cte
WHERE date1 < DATEADD(MONTH,@month + 4,DATEADD(year,@year-1,cast('00010101' as date)))
)

SELECT * FROM cte
GO

And Output will be :

date1
-------------
2010-09-01
2010-10-01
2010-11-01
2010-12-01
2011-01-01
2011-02-01

UNPIVOT in SQL SERVER with Example

Here in this example it shows the Max column value for the specific ID
how it is get by UnPIVOT let see :

DECLARE @Test AS TABLE(id INT, col1 INT, col2 INT, col3 INT, col4 INT)
INSERT INTO @Test VALUES(1, 100, 500, 300, 200)
INSERT INTO @Test VALUES(2, 600, 300, 500, 400)
INSERT INTO @Test VALUES(3, 400, 100, 800, 300)

SELECT ID,MAX(data) AS MaxValue
FROM
(
SELECT ID,Data
FROM
(SELECT ID, col1, col2, col3, col4
FROM @Test
) p
UNPIVOT
(Data FOR Datavalue IN
(col1, col2, col3, col4)
)AS unpvt
) AS x
GROUP BY ID
GO

The output will be :
-------------
ID MaxValue
----- ------------
1 500
2 600
3 800

Count the difference of character in two string in SQL Server

Create the sql function and use it like below :


--Select dbo.DiffCount('BOB', 'BOB')
CREATE FUNCTION dbo.DiffCount(@Str1 varchar(500), @Str2 varchar(500))
Returns int As
BEGIN
DECLARE @len1 INT,
@len2 INT,
@DiffLen INT,
@CharIndex INT,
@Inc INT,
@diffcount INT

SELECT @len1 = DATALENGTH(@str1),
@len2 = DATALENGTH(@str2),
@DiffLen = @len1 - @len2,
@diffcount = 0,
@Inc = 1

IF @len1 <= @len2 BEGIN

WHILE @Inc <= @len2 BEGIN

IF SUBSTRING(@str1,@inc,1) = NULLIF(SUBSTRING(@str2,@inc,1),' ') BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END
END ELSE IF @len1 > @len2 BEGIN
WHILE @Inc <= @len1 BEGIN

IF NULLIF(SUBSTRING(@str1,@inc,1),' ') = SUBSTRING(@str2,@inc,1) BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END

END
RETURN @diffcount
END

Retrieve comma separated values into a table

Hi ,

You can use this table vale function to get the comma separated string into form of table retrieve value.

just create this function and select result from that .

--SELECT * from dbo.CommaSeparatedToTableVale('app,birds,cow,paresh,dhaval')
CREATE FUNCTION CommaSeparatedToTableVale
(
@str VARCHAR(100)
)
RETURNS @Table
TABLE(rec VARCHAR(50))
AS
BEGIN
DECLARE @len INT
DECLARE @inc int
DECLARE @offset INT
DECLARE @newstr VARCHAR(100)

select @len = DATALENGTH(@str),
@inc = 0,
@offset = 0

while @inc <= @len+1 begin

IF ISNULL(@newstr,'') = '' BEGIN
SELECT @offset = charindex(',',@str)
INSERT INTO @Table
SELECT SUBSTRING(@str,0,@offset)
SELECT @newstr = SUBSTRING(@str,@offset+1,@len)
END ELSE BEGIN
SELECT @offset = charindex(',',@newstr)
if @offset = 0 begin
INSERT INTO @Table
SELECT @newstr
break
end else begin
INSERT INTO @Table
SELECT SUBSTRING(@newstr,0,@offset)
end
SELECT @newstr = SUBSTRING(@newstr,@offset+1,@len)
END
SET @len = @len - @offset
SET @inc = @inc + 1
end
--RETURN @table
return
END

And you may also refer this for using XML :

http://sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html