Scripts

Scripts http://www.prlsoftware.com/t-sql.aspx T-SQL Scripts Copyright 2009 Philip Leitch http://www.rssboard.org/rss-specification mojoPortal Blog Module Philip@prlsoftware.com 120 Working Days Create function Working_Days(
@Date1 Smalldatetime,
@Date2 Smalldatetime)
/*www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function determines the number of working days between two dates.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes: The logic is simply the number of days different between the two dates minus 2 times the number of weeks between
the two dates. Holidays and other issues may need to be factored in.
*/
returns int
as
begin
return abs(datediff(Day, @Date1, @Date2) - 2 * abs(datediff(week, @date1, @date2)))
end
go
select dbo.working_days ('12/4/2010', '16/4/2010') --During Week
select dbo.working_days ('16/4/2010', '19/4/2010') --Over weekend

Philip Leitch  ...]]> http://www.prlsoftware.com/working-days.aspx Philip Leitch http://www.prlsoftware.com/working-days.aspx http://www.prlsoftware.com/working-days.aspx Thu, 15 Apr 2010 03:51:20 GMT Bearing Create function [dbo].[Bearing]
( @Lat1 float, @Lng1 float, @Lat2 float, @Lng2 float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts two sets of latitude and longatude points in to a bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes: Bearing changes during travel, for instance, if you travel heading due north, after enough time you will end up heading due
south even though you haven’t changed direction. Therefore this is the outset direction (direction from point A to get to point B).
*/
returns numeric(10,6) as
begin
Declare @Bearing as numeric(10,6)

    set @Lat1 = @Lat1 * (pi()/180.0)
    set @Lng1 = @Lng1 * (pi()/180.0)
    set @Lat2 = @Lat2 * (pi()/180.0)
    set @Lng2 = @Lng2 * (pi()/180.0)

    set @Bearing = atn2(sin(@Lng2-@Lng1)*cos(@Lat2),cos(@Lat1)*sin(@Lat2)-sin(@Lat1)*cos(@Lat2)*cos(@Lng2-@Lng1))/ (pi()/180.0)

    if @Bearing < 0
        set @Bearing = 360 + @Bearing
    return @Bearing

end

Philip Leitch  ...]]> http://www.prlsoftware.com/bearing.aspx Philip Leitch http://www.prlsoftware.com/bearing.aspx http://www.prlsoftware.com/bearing.aspx Tue, 13 Apr 2010 00:11:10 GMT Point_Bearing_Distance_To_Point CREATE    function [dbo].[Point_Bearing_Distance_To_Point]
( @Latitude float, @Longitude float, @Bearing float, @Distance as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a latitude and longatude, bearing and distance to another latitude and longitude (concatenated together).
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns varchar(50)
as
begin
Declare @Lat2 float, @Long2 float
set @Latitude = cast(@latitude as numeric(9,6))
Declare @Radius float
    set @Radius = dbo.Earth_Radius(@Latitude)

    set @Bearing = @bearing * (pi()/180.0)
    set @Latitude = @Latitude * (pi()/180.0)
    set @Longitude = @Longitude * (pi()/180.0)

set @lat2 = asin( sin(@Latitude)*cos(@Distance/@Radius) +
                      cos(@Latitude)*sin(@Distance/@Radius)*cos(@Bearing) );
set @long2 = @Longitude + atn2(sin(@Bearing)*sin(@Distance/@Radius)*cos(@Latitude),
                             cos(@Distance/@Radius)-sin(@Latitude)*sin(@lat2));

    return cast(cast(@lat2 * (180/pi()) as numeric(10,6)) as varchar(50)) + ', ' + cast(cast( @long2 * (180/pi()) as numeric(10,6)) as varchar(50))
end
Philip Leitch  ...]]> http://www.prlsoftware.com/point_bearing_distance_to_point.aspx Philip Leitch http://www.prlsoftware.com/point_bearing_distance_to_point.aspx http://www.prlsoftware.com/point_bearing_distance_to_point.aspx Tue, 09 Mar 2010 03:42:41 GMT Distance CREATE function [dbo].[Distance]

(@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the distance between two geospatial points.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
Based on the "ellipsoidal Vincenty formula". This might not be quite as accurate as others, but is close on accuracy and very fast.
Please visit this web site for scripts that are easily converted into any language:
http://www.movable-type.co.uk/scripts/latlong.html

The result is in kilometres Multiply by 1000 for metres

*/
returns float
as
begin

    if abs(@lat1 - @lat2) < 0.00001 and abs(@long1 - @long2) < 0.00001
        return 0

    Declare @Radius float
    set @Radius = dbo.Earth_Radius((@lat1 + @lat2)/cast(2 as float))

    --Convert to radians
    set @Lat1 = @lat1 * (pi()/180)
    set @Long1 = @long1 * (pi()/180)
    set @Lat2 = @lat2 * (pi()/180)
    set @Long2 = @long2 * (pi()/180)

    Declare @return float

    set @return = acos(
    Cos(@Lat1) * cos(@Long1) *
    Cos(@Lat2) * cos(@Long2) +

    cos(@lat1) * sin(@Long1) *
    cos(@lat2) * sin(@Long2) +

    sin(@lat1) * sin(@lat2)

    ) * @Radius

    return @return
end

Philip Leitch  ...]]> http://www.prlsoftware.com/distance.aspx Philip Leitch http://www.prlsoftware.com/distance.aspx http://www.prlsoftware.com/distance.aspx Tue, 09 Mar 2010 03:37:37 GMT Earth_Radius CREATE Function [dbo].[Earth_Radius](@Latitude numeric(9,6))
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function determines the Earth's Radias at a specific latitude.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns float
as
begin

    declare @Return as float

    Declare @a as float
    declare @b as float
    set @a = 6378.1370 --Equatorial radius
    set @b = 6356.7523 --Polar radius
    Declare @LatitudeRadians float
    --Convet to radians
    set @LatitudeRadians = @latitude * (pi()/180)
    set @Return = sqrt(
            (power(power(@a, 2)*Cos(@Latitude),2) + power(power(@b, 2)*sin(@Latitude),2))
                                /
            (power(@a*Cos(@Latitude),2) + power(@b*sin(@Latitude),2))
                )
    return @Return
end

Philip Leitch  ...]]> http://www.prlsoftware.com/earth_radius.aspx Philip Leitch http://www.prlsoftware.com/earth_radius.aspx http://www.prlsoftware.com/earth_radius.aspx Tue, 09 Mar 2010 03:33:00 GMT Compass_Direction CREATE Function [dbo].[Compass_Direction] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a Bearing into human readable compass text.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Varchar(5) as
Begin
declare @North_Distance float
set @north_distance = dbo.North_Distance(@Bearing)
declare @East_Distance float
set @East_Distance = dbo.East_Distance(@Bearing)
return case when @East_Distance between 11.25 and 33.75 then 'E' when @East_Distance between 146.25 and 168.75 then 'W' else '' end +
case when @North_Distance <= 56.26 then 'N' when @North_Distance >= 123.74 then 'S' else '' end +
case when @North_Distance between 11.25 and 33.75 then 'N' when @North_Distance between 146.25 and 168.75 then 'S' else '' end +
case when @East_Distance between 45+11.25 and 45+33.75 then 'E' when @East_Distance between 146.25-45 and 168.75-45 then 'W' else '' end +
case when @North_Distance between 45+11.25 and 45+33.75 then 'N' when @North_Distance between 146.25-45 and 168.75-45 then 'S' else '' end +

case when @East_Distance <= 56.26 then 'E' when @East_Distance >= 123.74 then 'W' else '' end
end
Philip Leitch  ...]]> http://www.prlsoftware.com/compass_direction.aspx Philip Leitch http://www.prlsoftware.com/compass_direction.aspx http://www.prlsoftware.com/compass_direction.aspx Tue, 09 Mar 2010 03:30:24 GMT Angle_To_Bearing CREATE function [dbo].[Angle_To_Bearing]( @North as float, @East as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function converts a North and East distance to a bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns float
as
begin
    return case when @east <= 90 then @North else 360 - @North end
End
Philip Leitch  ...]]> http://www.prlsoftware.com/angle_to_bearing.aspx Philip Leitch http://www.prlsoftware.com/angle_to_bearing.aspx http://www.prlsoftware.com/angle_to_bearing.aspx Tue, 09 Mar 2010 03:18:51 GMT East_Distance CREATE Function [dbo].[East_Distance] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the angular distance from due East to the vector bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Float as
begin
    return (case when (@Bearing > 270) then (450 - @Bearing) else (abs((@Bearing - 90))) end)
end
Philip Leitch  ...]]> http://www.prlsoftware.com/east_distance.aspx Philip Leitch http://www.prlsoftware.com/east_distance.aspx http://www.prlsoftware.com/east_distance.aspx Tue, 09 Mar 2010 03:18:10 GMT North_Distance CREATE Function [dbo].[North_Distance] (@Bearing as float)
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2010
Purpose: This function measures the angular distance from due North to the vector bearing.
Copyright: Philip Leitch 2010
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
*/
returns Float as
begin
    return (case when (@Bearing > 180) then (360 - @Bearing) else @Bearing end)
end
Philip Leitch  ...]]> http://www.prlsoftware.com/north_distance.aspx Philip Leitch http://www.prlsoftware.com/north_distance.aspx http://www.prlsoftware.com/north_distance.aspx Tue, 09 Mar 2010 03:16:31 GMT Working with Angles When working on directional geospatial data it is often useful to use a bearing (direction of travel). Although using the bearing is very helpful, it does pose a problem: what to do with North. I recently worked on a large project where I needed to determine average bearing of a vehicle at a specific location (the location would be repeated weekly).

If the vehicle repeatedly travels due North then the bearing reading that is taken will variously be just over 0 degrees or just under 360 degrees (either side of due North). The average of this would actually be due South, which is the polar opposite of the direction actually being travelled. This issue doesn’t only apply to premises that are exactly due North, but by any premises where the bearing of the truck occasionally crosses due North. For example, 350 degrees direction might occasionally has a value of 0-5 degrees.

I’ve come up with a way to resolve this by using “Angular Distance”. That is, instead of using bearing which comes back on itself, I’ve used the number of degrees away from North. This way NW would be 45 degrees, as would also be NE, meaning that a truck travelling due North would have an average Angular Distance from north of zero. Similar to a normal compass, South would also be 180 degrees. By doing this, we lose any ability to identify East/West travel, so a second angular distance is required, and I’ve chosen the distance from due East, where 0 is due east and 180 is due West.

Therefore, due north has a Northern Angular Distance Using these two angular distances the original bearing can be easily determined. For instance, an Angular Distance of 45 from North and 45 from East means the truck must be travelling NE. This type of information becomes essential to determining which side of the road a truck is collecting bins. Calculating the total change in bearing can be unintuitive. A change from NE (45 Degrees) to NW (315 Degrees) is a total angular distance of zero from North (as this is the angular distance, not angle). However, over this same change in bearing the angular distance from east is 90 Degrees (45 to 135). Therefore the actual net angular distance of to bearings is calculated by using the Bearing, not the angular distances.

Compass_Direction – Turns a standard bearing (such as 223) into a bearing (SW). The divisions are N, NNE, NE, ENE, E, ESE, SE, SSE, S, SSW, SW, WSW, W, WNW, NW, NNW

North_Distance – Provides the Northern Angular Distance from a bearing.

East_Distiance – Provides the Eastern Angular Distance from a bearing.

Angular_Distance - Given the North_Distance and East_Distance it returns the smallest angle between the two vectors.

I will post each of these functions seperately to aid finding them.

Philip Leitch ...]]> http://www.prlsoftware.com/working-with-angles.aspx Philip Leitch http://www.prlsoftware.com/working-with-angles.aspx http://www.prlsoftware.com/working-with-angles.aspx Tue, 09 Mar 2010 03:07:23 GMT Create Insert Create proc Create_Insert(@Table_Name as varchar(500))
as
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: This procedure creates an easy "Insert" script - useful for transfering data.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
*/
begin
Declare @SQL as varchar(8000)
select @sql = case when @sql is null then '' else @SQL + ', '''''' +' end + char(10) +
'isnull(' + case when syscolumns.collation is not null then
' replace([' + Name + '] , '''''''','''''''''''') '
else
' cast([' + Name + '] as varchar) '
end + ', ''null'') + '''''' '
from syscolumns where id = object_id(@Table_Name)
set @sql = 'Select ''insert into [' + @Table_Name + '] Values('''''' + ' + @sql + ')''' +
char(10) + ' from [' + @Table_Name + ']'
print @sql
exec (@sql)

end
Philip Leitch ...]]> http://www.prlsoftware.com/create-insert.aspx Philip Leitch http://www.prlsoftware.com/create-insert.aspx http://www.prlsoftware.com/create-insert.aspx Tue, 10 Nov 2009 07:40:42 GMT Remove Backup History Taken from http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx

NOTE - there are two procedures here, the first for SQL Server 2000, the second for 2005/2008

SQL Server 2000

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int

BEGIN TRAN

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh
ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh
ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms
ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc

SQL Server 2005 or 2008

CREATE PROCEDURE [dbo].[Delete_Backup_History]
(@days_to_retain int = 186)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int
DECLARE @target_date datetime

SET @target_date = GETDATE() - @days_to_retain

BEGIN TRAN

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg
INNER JOIN msdb..backupset bs ON bfg.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc
END

Philip Leitch  ...]]> http://www.prlsoftware.com/remove-backup-history.aspx Philip Leitch http://www.prlsoftware.com/remove-backup-history.aspx http://www.prlsoftware.com/remove-backup-history.aspx Mon, 09 Nov 2009 23:11:50 GMT OverlapDates CREATE FUNCTION OverlapDates
    (@Startdate as datetime= null,
    @EndDate as datetime = null,
    @Startdate2 as datetime= null,
    @EndDate2 as datetime = null)
RETURNS bit
AS
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: This function determines whether pairs of dates overlap or not.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
The ranges overlap if the first start or end is between the start and end of the second time range.
The ranges overlap i the second start or end is between the start and end of the first time range.
The "order" of start/finish is assumed to be in advancing time, nulls are considered as "N/A".

For instance, does my life overlap with another person's life. if we are both still alive then we both
have no end date defined because it hasn't happened yet. Therefore we automatically overlap.
*/
BEGIN
    Declare @ReturnValue as bit

    if (    @Startdate Between isnull(@Startdate2,@Startdate-1) and isnull(@EndDate2, @startdate+1)
        or
        @EndDate Between isnull(@Startdate2,@EndDate-1) and isnull(@EndDate2, @EndDate+1)
        or
        @Startdate2 Between isnull(@Startdate,@Startdate2-1) and isnull(@EndDate, @Startdate2+1)
        or
        @EndDate2 Between isnull(@Startdate,@EndDate-1) and isnull(@EndDate, @EndDate+1)
    )
        set @ReturnValue = 1
    else
        set @ReturnValue = 0
    return @ReturnValue
End
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.OverlapDates('1/1/1999', '1/1/2000', '1/1/2002','1/1/2008'), 'Standard ranges not overlapping, first earlier than second'
union all SELECT dbo.OverlapDates('1/1/02','1/1/2008', '1/1/1999', '1/1/2000'), 'Standard ranges not overlapping, first later than second'
union all SELECT dbo.OverlapDates('1/1/1999', '1/1/2008', '1/1/2002','1/1/2003'), 'Standard ranges overlapping'
union all SELECT dbo.OverlapDates('1/1/2003', null, '1/1/05',null) As Result, 'Second range higher than first, neither have a known end' as Test
union all SELECT dbo.OverlapDates('1/1/2003', null, '1/1/01',null), 'Second range lower than first, nether have a known end'
union all SELECT dbo.OverlapDates('1/1/2003', '1/1/2004', '1/1/01',null), 'Second range started before first but without an end'
union all SELECT dbo.OverlapDates('1/1/2003', null, null,'1/5/2005'), 'Second range ended after first started. First range has no end, second range no beginning'
union all SELECT dbo.OverlapDates(null, '1/1/2000', '1/1/02',null), 'Second range starts but has no known end, second range has no known start but ends before the start of the second range'
Philip Leitch  ...]]> http://www.prlsoftware.com/1overlapdates.aspx Philip Leitch http://www.prlsoftware.com/1overlapdates.aspx http://www.prlsoftware.com/1overlapdates.aspx Sun, 11 Oct 2009 22:00:00 GMT Data_Size create View Data_Size as

/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: Provides a way of viewing the size of tables, and what part of the table contribute to that size.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:

A number of columns have been added to provide more insight into the tables.

A K_Per_Row column is shown to give an idea as to how "dense" the inforamtion is. A low value indicates that each new row adds very little to space. This is useful when trying to identify where size savings can be made.

The number of Indexes is normally very tightly aligned with the K_Per_Row. The more indexes on a table the more space each row adds (an additional record will mean more information in each and every index).

An additional column "Has_Clustered Index" is included as a general warning. The Clustered Index of a table is how the data is stored on the physical disk. A large table with no clustered index and no primary key is called a "Heap" and quickly becomes very fragmented since there is literally no logic as to how data is organised for this table on the disk. A primary key is a proxy for a clustered index in that it acts as a clustered index when no clustered index exists.

*/
select
sysobjects.name as Table_Name,
sum(cast(reserved as bigint)*8) as Total_Size ,
sum ( case when (indid =0 or indid=1) then cast(reserved as bigint)*8 else 0 end) as Data_and_Indexes,
sum ( case when (indid not in (255,0,1)) then cast(reserved as bigint)*8 else 0 end) as Index_Size,
sum ( case when (indid =0 or indid=1) then cast(reserved as bigint)*8 else 0 end) -
sum ( case when (indid not in (255,0,1)) then cast(reserved as bigint)*8 else 0 end) as Data_Size,
sum ( case when (indid=255) then cast(reserved as bigint)*8 else 0 end) as Text_Size,
sum(case when indid in (0,1) then rowcnt else 0 end) as Rough_row_Count,
cast(sum(cast(reserved as bigint)*8)/ cast(case when sum(case when indid in (0,1) then rowcnt else 0 end) = 0 then 1 else sum(case when indid in (0,1) then rowcnt else 0 end) end as float) as numeric(12,2)) as [K_Per_Row],

sum(case when indid > 1 then 1 else 0 end) as Indexes,
case when isnull(sum(case when indid = 0 then 1 else 0 end),1) > 0 then 'No Clustered Index' else '' end as Has_Clustered_Index
from dbo.sysindexes
join dbo.sysobjects on
sysindexes.id = sysobjects.id
where reserved <> 0 and sysobjects.xtype = 'U '
group by
sysobjects.name
go
select * from Data_Size order by total_size desc

Philip Leitch  ...]]> http://www.prlsoftware.com/data_size.aspx Philip Leitch http://www.prlsoftware.com/data_size.aspx http://www.prlsoftware.com/data_size.aspx Thu, 08 Oct 2009 01:40:03 GMT Progress Bar Create procedure Progress(
    @Min as float,
    @Max as float,
    @Value as float,
    @Length as int = 100)
as
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2009
Purpose: Creates a text based progress bar.
Copyright: Philip Leitch 2009
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
Best used with text return.
*/
begin
    if @min > @max or @min > @value
        begin
        print 'Invalid Range.'
        return
        end
    declare @Stars as int

    set @Stars = round((((@value - @min) )/ (@max - @min) * @Length) ,0)
    print '[' + replicate('*', @Stars) + replicate ('-', @Length-@Stars) + '] ' + cast(cast(round((((@value - @min) )/ (@max - @min) * 100) ,2) as numeric(8,2)) as varchar) + '%'
    return
end
Philip Leitch  ...]]> http://www.prlsoftware.com/progress-bar.aspx Philip Leitch http://www.prlsoftware.com/progress-bar.aspx http://www.prlsoftware.com/progress-bar.aspx Mon, 31 Aug 2009 03:41:33 GMT Prime Numbers

Recently there was a competition on creating prime nubmers in SQL Server. Although there were faster versions, I believe that this is the fastest version that will scale.

create table Primes (p integer NOT NULL Primary Key)

go

set nocount on

-- Seed the pot with the first two Primes

declare @testval integer, @First as int, @Second as int, @SQRT as int

-- Start testing at n=6, stepping 6 per test.

set @testval = 6

BEGIN TRANSACTION

while @testval < 5000000

begin

set @First = @testval -1

set @Second = @testval +1

set @SQRT = SQRT(@testval +1)

if not exists (select @First from Primes where (@First) % p = 0 and p <= @SQRT)

insert Primes select @First

if not exists (select @Second from Primes where (@Second) % p = 0 and p <= @SQRT)

insert Primes select @Second

set @testval = @testval + 6

end

INSERT Primes select 2

INSERT Primes select 3

COMMIT

Philip Leitch ...]]> http://www.prlsoftware.com/prime-numbers.aspx Philip Leitch http://www.prlsoftware.com/prime-numbers.aspx http://www.prlsoftware.com/prime-numbers.aspx Fri, 07 Aug 2009 05:41:27 GMT SP_Kill_Users

use master

go

create Proc SP_Kill_Users
@Database_Name as varchar(1000) = null
as
/*

www.prlsoftware.com
Author: Philip Leitch
Date: 2002
Purpose: Kills all processes currently using a specified database, or all databases if no database is supplied.
Copyright: Philip Leitch 2002
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.

Notes:
Be very careful with this procedure Killing processes without first checking what they are is dangerous.

This procedure is very useful when a restore is required urgently.

Please note this procedure is intended to be placed into the master database, which combined with the SP_ prefix provides global calling scope.

*/

Declare [Kill_All] cursor FORWARD_ONLY
for
select databases.name, processes.spid from master.dbo.sysprocesses as processes
join master.dbo.sysdatabases as databases on
processes.dbid = databases.dbid
where hostname <> @@servername and hostname <>''
and (databases.name = @database_name or @database_name is null)
and processes.spid <> @@spid
group by databases.name, processes.spid
order by databases.name

open [Kill_All]

declare @KillSQL as varchar(1000)
declare @DatabaseName as varchar(100)
declare @SPID as varchar(10)

fetch [Kill_All] into @DatabaseName, @SPID
while @@fetch_status = 0
begin
            set @KillSQL = 'Kill ' + cast(@spid as varchar(100))
            exec (@KillSQL)
            fetch [Kill_All] into @DatabaseName, @SPID
end

deallocate [Kill_All]
go

Philip Leitch ...]]> http://www.prlsoftware.com/sp_kill_users.aspx Philip Leitch http://www.prlsoftware.com/sp_kill_users.aspx http://www.prlsoftware.com/sp_kill_users.aspx Sat, 13 Jun 2009 10:22:49 GMT Time To Text create FUNCTION Time_To_Text
(@Time as datetime)
returns Varchar(255)
begin
/*

www.prlsoftware.com
Author: Philip Leitch
Date: 2005
Purpose: Displays just the time component of a datetime/smalldatetime value.
Copyright: Philip Leitch 2005
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.

Notes:
Currently returns the time in 24 hour time.
*/
DECLARE @RETURN AS VARCHAR(255)
SET @RETURN = right('0' + cast(datepart(HOUR, @Time) as varchar),2) + ':' +
right('0' + cast(datepart(minute, @Time) as varchar),2) + ':' +
right('0' + cast(datepart(SECOND, @Time) as varchar),2) + '.' +
right('00' + cast(datepart(MILLISECOND, @Time) as varchar),3)

return @RETURN

end
go
-- =============================================
-- Example to execute function
-- =============================================
--One of these values will be in 24 hour format.
select dbo.Time_To_Text(getdate()), dbo.Time_To_Text(getdate()+.5)

Philip Leitch ...]]> http://www.prlsoftware.com/time-to-text.aspx Philip Leitch http://www.prlsoftware.com/time-to-text.aspx http://www.prlsoftware.com/time-to-text.aspx Thu, 11 Jun 2009 23:30:03 GMT Tax Component

IF EXISTS (SELECT 'x' FROM sysobjects WHERE name = 'TAX_Component')
DROP FUNCTION TAX_Component
GO
Create FUNCTION dbo.TAX_Component
(@Charge as Money,
@TAX_Rate as numeric(12,4))
RETURNS Money
AS
BEGIN
/*
Author: Philip Leitch
Date: 2001
Purpose: Determines the tax component of an already taxed value.
Copyright: Philip Leitch 2001
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.

Notes:
This function was created for Goods and Services (GST) tax, a type of Value Added Tax (VAT). The Tax portion
of a value is frequently required When only the total net (inclusive of tax) value is provided. This function
will provide the tax component, given any charge and tax rate.

*/
RETURN case when @TAX_Rate = 0 then 0 else cast(@Charge * 1/((1/@TAX_Rate)+1) as numeric(12,2)) end

END
GO

-- =============================================
-- Example to execute function
-- =============================================
declare @Amount as money
declare @TAX_Rate as float
set @amount = $100
set @TAX_Rate = 0.10 --Equates to TAX
select @Amount as Amount, @TAX_Rate as rate, dbo.TAX_Component(@Amount, @TAX_Rate) as TAX_Component, @Amount - dbo.TAX_Component(@Amount, @TAX_Rate) as Total_Exclusive_Charge
select 9.0900+ 90.9100

Philip Leitch ...]]> http://www.prlsoftware.com/tax-component.aspx Philip Leitch http://www.prlsoftware.com/tax-component.aspx http://www.prlsoftware.com/tax-component.aspx Sun, 07 Jun 2009 11:59:48 GMT OverlapDates IF EXISTS (SELECT 'x' FROM sysobjects WHERE name = 'OverlapDates')
DROP FUNCTION OverlapDates
GO
CREATE FUNCTION OverlapDates
(@Startdate as datetime,
@EndDate as datetime = null,
@Startdate2 as datetime,
@EndDate2 as datetime = null)
RETURNS bit
AS
BEGIN
Declare @ReturnValue as bit
/*
Author: Philip Leitch
Date: 2005
Purpose: Determines if one pair of dates overlaps with another pair of dates.
Copyright: Philip Leitch 2005
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.

Liability: The developer assumes all liability when using this code.

Notes:
This function assumes that the start is not null in either pair but the end date can be null of either pair.
The start and end date are common for any duration based activity, such as pricing or activities.

The ranges overlap if the first start is before the second start,
and the first end is after the second start.
OR
The ranges overlap if the first start is betwene the second start and the second end.
*/

if (@Startdate <= @Startdate2 and (@Startdate2 <= @EndDate or @EndDate is null))
  or
  (@Startdate > @Startdate2 and (@Startdate <= @EndDate2 or @EndDate2 is null))
  set @ReturnValue = 1
else
  set @ReturnValue = 0
return @ReturnValue
End
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.OverlapDates('1/1/2003', getdate(), '1/1/05','')

Philip Leitch ...]]> http://www.prlsoftware.com/overlapdates.aspx Philip Leitch http://www.prlsoftware.com/overlapdates.aspx http://www.prlsoftware.com/overlapdates.aspx Sun, 07 Jun 2009 09:46:18 GMT