ЈЦЅГЇП €ΘΘΚ | <jco-blog/>

originally posted 2015-01-27

1	originally posted 2015-01-27

The database backup is named like “Something_[1-x].bak”, where x is the number of backup file parts.

$(DB) is the database name
$(CHUNKS) is the number of backup file parts
$(PATH) is the path of the first backup file (assumes all of the backup files are in the same directory)

:setvar DB "gazelle_demo"
:setvar CHUNKS "1"
:setvar PATH "H:\DatabaseBackups\Nightly\SQLA\Nightly\gazelle_demo_NPT_[1-1].bak"

USE [master];
declare @error nvarchar(100);
set @error = null;
    
if exists(select * from sys.databases where name = '$(DB)')
begin
    --if target database exists, disconnect users
    begin try
        ALTER DATABASE [$(DB)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    end try
    begin catch
        declare @dummy bit;
    end catch
end

begin try
  declare @chunks int;
  set @chunks = $(CHUNKS);
      
  declare @diskChunkSql nvarchar(max);
  set @diskChunkSql = '';
      
  declare @count int;
  set @count = 1;
  while(@count&amp;lt;=@chunks)
  begin	
    set @diskChunkSql = @diskChunkSql + 'DISK=''' + replace('$(PATH)','_[1','_[' + convert(nvarchar(10),@count))  + '''';
    if(@count&amp;lt;@chunks and @chunks &amp;gt; 1) set @diskChunkSql = @diskChunkSql + ',';
    set @count = @count + 1;
  end

  --get default dir for data files
  declare @defaultDataDir nvarchar(512)
  select @defaultDataDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultdatapath'))

  --get default dir for log files
  declare @defaultLogDir nvarchar(512)
  select @defaultLogDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultlogpath'))

  --table of files contained by the backup set
  declare @dbFilesTab table
  (
  [LogicalName] nvarchar(128)
  ,[PhysicalName] nvarchar(260)
  ,[Type] char(1)
  ,[FileGroupName] nvarchar(128)
  ,[Size] numeric(20,0)
  ,[MaxSize] numeric(20,0)
  ,[FileId] bigint
  ,[CreateLSN] numeric(25,0)
  ,[DropLSN] numeric(25,0)
  ,[UniqueId] uniqueidentifier
  ,[ReadOnlyLSN] numeric(25,0)
  ,[ReadWriteLSN] numeric(25,0)
  ,[BackupSizeInBytes] bigint
  ,[SourceBlockSize] int
  ,[FileGroupId] int
  ,[LogGroupGUID] uniqueidentifier
  ,[DifferentialBaseLSN] numeric(25,0)
  ,[DifferentialBaseGUID] uniqueidentifier
  ,[IsReadOnly] bit
  ,[IsPresent] bit
  ,[TDEThumbprint] varbinary(32)
  )

  --table of new file named derived from the backup set
  declare @dbNewFilesTab table
  (
  [LogicalName] nvarchar(128)
  ,[Ext] nvarchar(128)
  ,[Type] char(1)
  ,[Row] int
  ,[NewPhysicalName] nvarchar(260)
  )

  --get files in backup set
  insert into @dbFilesTab exec('restore filelistonly from disk = ''$(PATH)''')

  --determine the distinct list of file extensions in the backup set
  declare @extensionsTab table ([Ext] nvarchar(3));
  insert @extensionsTab select distinct right([PhysicalName],3) from @dbFilesTab

  --derive new names for each physical file in the backup set for the new location
  while(1=1)
  begin
    declare @ext nvarchar(3);
    set @ext = null;
    select top 1 @ext = [Ext] from @extensionsTab
    if(@ext is not null)
    begin
      insert @dbNewFilesTab ([LogicalName],[Ext],[Type],[Row],[NewPhysicalName])
      select 
      [LogicalName]
      ,[Ext]
      ,[Type]
      ,[Row]
      ,[NewPhysicalName] =
        case when [Type] = 'L' then @defaultLogDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]
        else @defaultDataDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]
        end
      from
      (
        select [LogicalName], [Ext] = right([PhysicalName],3), [Type], [Row] = row_number() over(order by[LogicalName])
        from @dbFilesTab 
        where right([PhysicalName],3) = @ext
      ) as q1

      delete @extensionsTab where [Ext] = @ext
    end
    else break;
  end

  --create a concatentated string of SQL that specifies the new names for each file
  declare @moveSql nvarchar(max)
  select @moveSql = stuff(
  (
    select ',move N''' + [LogicalName] + ''' TO N''' + [NewPhysicalName] + ''''
    from @dbNewFilesTab
    order by [Ext],[Row]
    for xml path('')
  ),1,1,'')
      
    declare @sql nvarchar(max);
    set @sql = 'use [master]; restore database [$(DB)] from ' +  @diskChunkSql + ' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5, ' + @moveSql;
    
  exec(@sql)
end try
begin catch
    set  @error = ERROR_MESSAGE();
end catch

--if target database exists, re-enable connections
begin try
    ALTER DATABASE [$(DB)] SET MULTI_USER;
end try
begin catch
    declare @dummy2 bit;
end catch
            
if(@error is not null) RAISERROR(@error,11,100)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

:setvar DB "gazelle_demo"

:setvar CHUNKS "1"

:setvar PATH "H:\DatabaseBackups\Nightly\SQLA\Nightly\gazelle_demo_NPT_[1-1].bak"

USE [master];

declare @error nvarchar(100);

set @error = null;

if exists(select * from sys.databases where name = '$(DB)')

begin

--if target database exists, disconnect users

begin try

ALTER DATABASE [$(DB)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

end try

begin catch

declare @dummy bit;

end catch

end

begin try

declare @chunks int;

set @chunks = $(CHUNKS);

declare @diskChunkSql nvarchar(max);

set @diskChunkSql = '';

declare @count int;

set @count = 1;

while(@count&lt;=@chunks)

begin

set @diskChunkSql = @diskChunkSql + 'DISK=''' + replace('$(PATH)','_[1','_[' + convert(nvarchar(10),@count)) + '''';

if(@count&lt;@chunks and @chunks &gt; 1) set @diskChunkSql = @diskChunkSql + ',';

set @count = @count + 1;

end

--get default dir for data files

declare @defaultDataDir nvarchar(512)

select @defaultDataDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultdatapath'))

--get default dir for log files

declare @defaultLogDir nvarchar(512)

select @defaultLogDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultlogpath'))

--table of files contained by the backup set

declare @dbFilesTab table

(

[LogicalName] nvarchar(128)

,[PhysicalName] nvarchar(260)

,[Type] char(1)

,[FileGroupName] nvarchar(128)

,[Size] numeric(20,0)

,[MaxSize] numeric(20,0)

,[FileId] bigint

,[CreateLSN] numeric(25,0)

,[DropLSN] numeric(25,0)

,[UniqueId] uniqueidentifier

,[ReadOnlyLSN] numeric(25,0)

,[ReadWriteLSN] numeric(25,0)

,[BackupSizeInBytes] bigint

,[SourceBlockSize] int

,[FileGroupId] int

,[LogGroupGUID] uniqueidentifier

,[DifferentialBaseLSN] numeric(25,0)

,[DifferentialBaseGUID] uniqueidentifier

,[IsReadOnly] bit

,[IsPresent] bit

,[TDEThumbprint] varbinary(32)

)

--table of new file named derived from the backup set

declare @dbNewFilesTab table

(

[LogicalName] nvarchar(128)

,[Ext] nvarchar(128)

,[Type] char(1)

,[Row] int

,[NewPhysicalName] nvarchar(260)

)

--get files in backup set

insert into @dbFilesTab exec('restore filelistonly from disk = ''$(PATH)''')

--determine the distinct list of file extensions in the backup set

declare @extensionsTab table ([Ext] nvarchar(3));

insert @extensionsTab select distinct right([PhysicalName],3) from @dbFilesTab

--derive new names for each physical file in the backup set for the new location

while(1=1)

begin

declare @ext nvarchar(3);

set @ext = null;

select top 1 @ext = [Ext] from @extensionsTab

if(@ext is not null)

begin

insert @dbNewFilesTab ([LogicalName],[Ext],[Type],[Row],[NewPhysicalName])

select

[LogicalName]

,[Ext]

,[Type]

,[Row]

,[NewPhysicalName] =

case when [Type] = 'L' then @defaultLogDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]

else @defaultDataDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]

end

from

(

select [LogicalName], [Ext] = right([PhysicalName],3), [Type], [Row] = row_number() over(order by[LogicalName])

from @dbFilesTab

where right([PhysicalName],3) = @ext

) as q1

delete @extensionsTab where [Ext] = @ext

end

else break;

end

--create a concatentated string of SQL that specifies the new names for each file

declare @moveSql nvarchar(max)

select @moveSql = stuff(

(

select ',move N''' + [LogicalName] + ''' TO N''' + [NewPhysicalName] + ''''

from @dbNewFilesTab

order by [Ext],[Row]

for xml path('')

),1,1,'')

declare @sql nvarchar(max);

set @sql = 'use [master]; restore database [$(DB)] from ' + @diskChunkSql + ' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5, ' + @moveSql;

exec(@sql)

end try

begin catch

set @error = ERROR_MESSAGE();

end catch

--if target database exists, re-enable connections

begin try

ALTER DATABASE [$(DB)] SET MULTI_USER;

end try

begin catch

declare @dummy2 bit;

end catch

if(@error is not null) RAISERROR(@error,11,100)

originally posted 2015-02-05

1	originally posted 2015-02-05

//determines if a path is a UNC path
[DllImport("shlwapi.dll", CharSet = CharSet.Unicode)]
[ResourceExposure(ResourceScope.None)]
[return: MarshalAsAttribute(UnmanagedType.Bool)]
public static extern bool PathIsUNC(
[MarshalAsAttribute(UnmanagedType.LPWStr), In] string pszPath);

1

2

3

4

5

6

//determines if a path is a UNC path

[DllImport("shlwapi.dll", CharSet = CharSet.Unicode)]

[ResourceExposure(ResourceScope.None)]

[return: MarshalAsAttribute(UnmanagedType.Bool)]

public static extern bool PathIsUNC(

[MarshalAsAttribute(UnmanagedType.LPWStr), In] string pszPath);

originally posted 2015-02-05

1	originally posted 2015-02-05

We have a ClickOnce application for which we are merging and/or embedding all of our dependency assemblies into a single assembly and obfuscating with Red Gate’s SmartAssembly. ClickOnce doesn’t know about the merging and embedding, so it includes the non-obfuscated individual assembly files when it publishes. It turns out that it’s easy to exclude these files from the deployment at publish-time from Visual Studio.

From Project properties …
Publish tab …
Application Files button …
Select “Exclude” for the Publish Status of the desired files …
Save project …
Publish.

To do this to an already-published ClickOnce application requires more work. Here’s a LINQPad script example to programmatically find a file, remove it and re-sign the appropriate files.

/*
Written with LINQPad 
Programmatically remove files from a published ClickOnce application
using System.Diagnostics;
using System.IO;
using System.Xml;
*/

string sourceDir = @"C:\published\my_app_1_0_0_135\";
string manifestFilePath = sourceDir + @"\my_app.exe.manifest";
string applicationManifestFilePath = sourceDir + @"\my_app.application";
string mageFilePath = @"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\mage.exe";
string certFilePath = @"C:\certificates\MyAuthenticodeCertificate.pfx";
string certPassword = "foo";

//load .manifest XML
var doc = XDocument.Load(manifestFilePath);

//rename the files w/o the .deploy extension
string[] filePaths = Directory.GetFiles(sourceDir,"*.deploy");
foreach(string filePath in filePaths)
{
  string realName = filePath.Replace(".deploy",string.Empty);
  File.Move(filePath,realName);
  File.Delete(filePath);
}

//locate and remove the desired entry (e.g. the "MyComponentToRemove.dll" of dependency type of "install")
XNamespace ns = "urn:schemas-microsoft-com:asm.v2";
doc.Descendants(ns + "dependentAssembly")
   .Where(x =&amp;gt; (string)x.Attribute("dependencyType") == "install")
   .Where(x=&amp;gt; ((string)x.Attribute("codebase")).Equals("MyComponentToRemove.dll"))
   .Select(x =&amp;gt; x.Parent)
   .Remove();
 
//save .manifest XML
doc.Save(manifestFilePath);
 
//re-sign .manifest
Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
startInfo.FileName = mageFilePath;
startInfo.Arguments = string.Format("-update \"{0}\" -cf \"{1}\" -pwd \"{2}\"",manifestFilePath,certFilePath,certPassword);
process.StartInfo = startInfo;
process.Start();

//re-sign .application
process = new System.Diagnostics.Process();
startInfo = new System.Diagnostics.ProcessStartInfo();
startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
startInfo.FileName = mageFilePath;
startInfo.Arguments = string.Format("-update \"{0}\" -appmanifest \"{1}\" -cf \"{2}\" -pwd \"{3}\"",applicationManifestFilePath,manifestFilePath,certFilePath,certPassword);
process.StartInfo = startInfo;
process.Start();

//rename files with .deploy extension
foreach(string filePath in filePaths)
{
  string realName = filePath.Replace(".deploy",string.Empty);
  File.Move(realName,filePath);
  File.Delete(realName);
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

/*

Written with LINQPad

Programmatically remove files from a published ClickOnce application

using System.Diagnostics;

using System.IO;

using System.Xml;

*/

string sourceDir = @"C:\published\my_app_1_0_0_135\";

string manifestFilePath = sourceDir + @"\my_app.exe.manifest";

string applicationManifestFilePath = sourceDir + @"\my_app.application";

string mageFilePath = @"C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\mage.exe";

string certFilePath = @"C:\certificates\MyAuthenticodeCertificate.pfx";

string certPassword = "foo";

//load .manifest XML

var doc = XDocument.Load(manifestFilePath);

//rename the files w/o the .deploy extension

string[] filePaths = Directory.GetFiles(sourceDir,"*.deploy");

foreach(string filePath in filePaths)

{

string realName = filePath.Replace(".deploy",string.Empty);

File.Move(filePath,realName);

File.Delete(filePath);

}

//locate and remove the desired entry (e.g. the "MyComponentToRemove.dll" of dependency type of "install")

XNamespace ns = "urn:schemas-microsoft-com:asm.v2";

doc.Descendants(ns + "dependentAssembly")

.Where(x =&gt; (string)x.Attribute("dependencyType") == "install")

.Where(x=&gt; ((string)x.Attribute("codebase")).Equals("MyComponentToRemove.dll"))

.Select(x =&gt; x.Parent)

.Remove();

//save .manifest XML

doc.Save(manifestFilePath);

//re-sign .manifest

Process process = new System.Diagnostics.Process();

System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();

startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;

startInfo.FileName = mageFilePath;

startInfo.Arguments = string.Format("-update \"{0}\" -cf \"{1}\" -pwd \"{2}\"",manifestFilePath,certFilePath,certPassword);

process.StartInfo = startInfo;

process.Start();

//re-sign .application

process = new System.Diagnostics.Process();

startInfo = new System.Diagnostics.ProcessStartInfo();

startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;

startInfo.FileName = mageFilePath;

startInfo.Arguments = string.Format("-update \"{0}\" -appmanifest \"{1}\" -cf \"{2}\" -pwd \"{3}\"",applicationManifestFilePath,manifestFilePath,certFilePath,certPassword);

process.StartInfo = startInfo;

process.Start();

//rename files with .deploy extension

foreach(string filePath in filePaths)

{

string realName = filePath.Replace(".deploy",string.Empty);

File.Move(realName,filePath);

File.Delete(realName);

}

originally posted 2015-02-13

1	originally posted 2015-02-13

This is a revised version of a solution I lifted from internet postings.

insert @tab ([CreateDateTime]) 
values 
(convert(datetime2,'2015-02-13 12:29:01.330'))
,(convert(datetime2,'2015-02-13 12:29:34.883'))

/*
Note: DATETIME2 is used for better compatibility with .Net types, 
but it must be converted to a DATETIME before being used in the [ElapsedTimeString] calculations.
*/

select * from @tab

select top 1 
[ElapsedTimeString] = 
  convert(nvarchar(20),
    convert(varchar(10),
      convert(int, 
        convert(float,[LatestCurrentDateTime]) - 
        convert(float, [EarliestDateTime])
      ) * 24 /* hours over 24 */
      + datepart(hh, [LatestCurrentDateTime] - [EarliestDateTime]) /* hours */
    )
    + ':' + right('0' + convert(varchar(2), datepart(mi, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* minutes */
    + ':' + right('0' + convert(varchar(2), datepart(ss, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* seconds */
  )
from @tab t
outer apply
(
  select 
  [EarliestDateTime] = convert(datetime, min([CreateDateTime]))
  ,[LatestCurrentDateTime] = convert(datetime, max([CreateDateTime]))
  from @tab
) as x
order by [CreateDateTime] desc 

/*
Input [CreateDateTime] values:

'2015-02-13 12:29:01.3300000'
'2015-02-13 12:29:34.8830000'

[ElapsedTimeString] = 0:00:33

*/&lt;/pre&gt;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

insert @tab ([CreateDateTime])

values

(convert(datetime2,'2015-02-13 12:29:01.330'))

,(convert(datetime2,'2015-02-13 12:29:34.883'))

/*

Note: DATETIME2 is used for better compatibility with .Net types,

but it must be converted to a DATETIME before being used in the [ElapsedTimeString] calculations.

*/

select * from @tab

select top 1

[ElapsedTimeString] =

convert(nvarchar(20),

convert(varchar(10),

convert(int,

convert(float,[LatestCurrentDateTime]) -

convert(float, [EarliestDateTime])

) * 24 /* hours over 24 */

+ datepart(hh, [LatestCurrentDateTime] - [EarliestDateTime]) /* hours */

)

+ ':' + right('0' + convert(varchar(2), datepart(mi, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* minutes */

+ ':' + right('0' + convert(varchar(2), datepart(ss, [LatestCurrentDateTime] - [EarliestDateTime])), 2) /* seconds */

)

from @tab t

outer apply

(

select

[EarliestDateTime] = convert(datetime, min([CreateDateTime]))

,[LatestCurrentDateTime] = convert(datetime, max([CreateDateTime]))

from @tab

) as x

order by [CreateDateTime] desc

/*

Input [CreateDateTime] values:

'2015-02-13 12:29:01.3300000'

'2015-02-13 12:29:34.8830000'

[ElapsedTimeString] = 0:00:33

*/</pre>

originally posted 2015-02-16

1	originally posted 2015-02-16

Pilfered and tweaked from internet postings.

select [SizeBytes] = convert(bigint,sum([size]))*8*1024
from sys.master_files with(nowait)
where database_id = db_id('DATABASE_NAME_HERE')

1

2

3

select [SizeBytes] = convert(bigint,sum([size]))*8*1024

from sys.master_files with(nowait)

where database_id = db_id('DATABASE_NAME_HERE')

originally posted 2014-05-14

1	originally posted 2014-05-14

s1 is the start of Window 1
e1 is the end of Window 1
s2 is the start of Window 2
e2 is the end of Window 2
s1 < e1
s2 < e2
Window 1 Total Days = (e1 – s1) + 1
>Window 2 Total Days = (e2 – s2) + 1

create function [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)
returns int
as
begin
  declare @overlap int = null;

  if(@s1 is null or @e1 is null or @s2 is null or @e2 is null) return @overlap;

  declare @swap date
  if(@s1>@e1) 
  begin 
    set @swap = null
    set @swap = @e1
    set @e1 = @s1
    set @s1 = @swap
  end

  if(@s2>@e2) 
  begin
    set @swap = null
    set @swap = @e2
    set @e2 = @s2
    set @s2 = @swap
  end

  select
  @overlap = convert(int,
    case when not (@s1>@e2 or @e1<@s2) then
      case when @s1=@e2 or @e1=@s2 then 1 --overlap of one day
      when @s1>@s2 and @e1>@e2 then abs(datediff(d,@e2,@s1)) + 1 --partial overlap
      when @s1<@s2 and @e1<@e2 then abs(datediff(d,@e1,@s2)) + 1 --partial overlap
      when (@s1>@s2 and @e1<@e2) or (@s1>@s2 and @e1=@e2) or (@s1=@s2 and @e1<@e2) then datediff(d,@s1,@e1) + 1 --overlap by total days in window 1
      when (@s1<@s2 and @e1>@e2) or (@s1<@s2 and @e1=@e2) or (@s1=@s2 and @e1=@e2) or (@s1=@s2 and @e1>@e2) then datediff(d,@s2,@e2) + 1 --overlap by total days in window 2
      end
    else 0 --no overlap
    end)

  return @overlap
end
GO

create function [SIP_NPT_tfGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)
returns @tab table 
(
[S1] date
,[E1] date
,[S2] date
,[E2] date
,[W1Total] int
,[W2Total] int
,[Overlap] int
--,[Method] nvarchar(100)
)
as
begin
  insert @tab
  select
  [S1] = @s1
  ,[E1] = @e1
  ,[S2] = @s2
  ,[E2] = @e2
  ,[W1Total] = datediff(d,@s1,@e1) + 1
  ,[W2Total] = datediff(d,@s2,@e2) + 1
  ,[Overlap] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1,@e1,@s2,@e2)
  return;
end
GO

declare @tab table
(
  [Scenario] int
  ,[S1] date
  ,[E1] date
  ,[S2] date
  ,[E2] date
  ,[W1Total] int
  ,[W2Total] int
  ,[Overlap] int
  --,[Method] nvarchar(100)
)

insert @tab select [Scenario] = 1, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/1/2014','1/10/2014')
insert @tab select [Scenario] = 2, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/15/2014')
insert @tab select [Scenario] = 3, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/18/2014')
insert @tab select [Scenario] = 4, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/18/2014')
insert @tab select [Scenario] = 5, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/17/2014','1/19/2014')
insert @tab select [Scenario] = 6, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/20/2014')
insert @tab select [Scenario] = 7, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/20/2014')
insert @tab select [Scenario] = 8, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/25/2014')
insert @tab select [Scenario] = 9, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/25/2014')
insert @tab select [Scenario] = 10, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/20/2014')
insert @tab select [Scenario] = 11, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/25/2014')
insert @tab select [Scenario] = 12, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/20/2014','1/25/2014')
insert @tab select [Scenario] = 13, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/25/2014','1/30/2014')

select * from @tab order by [Scenario

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

create function [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)

returns int

as

begin

declare @overlap int = null;

if(@s1 is null or @e1 is null or @s2 is null or @e2 is null) return @overlap;

declare @swap date

if(@s1>@e1)

begin

set @swap = null

set @swap = @e1

set @e1 = @s1

set @s1 = @swap

end

if(@s2>@e2)

begin

set @swap = null

set @swap = @e2

set @e2 = @s2

set @s2 = @swap

end

select

@overlap = convert(int,

case when not (@s1>@e2 or @e1<@s2) then

case when @s1=@e2 or @e1=@s2 then 1 --overlap of one day

when @s1>@s2 and @e1>@e2 then abs(datediff(d,@e2,@s1)) + 1 --partial overlap

when @s1<@s2 and @e1<@e2 then abs(datediff(d,@e1,@s2)) + 1 --partial overlap

when (@s1>@s2 and @e1<@e2) or (@s1>@s2 and @e1=@e2) or (@s1=@s2 and @e1<@e2) then datediff(d,@s1,@e1) + 1 --overlap by total days in window 1

when (@s1<@s2 and @e1>@e2) or (@s1<@s2 and @e1=@e2) or (@s1=@s2 and @e1=@e2) or (@s1=@s2 and @e1>@e2) then datediff(d,@s2,@e2) + 1 --overlap by total days in window 2

end

else 0 --no overlap

end)

return @overlap

end

GO

create function [SIP_NPT_tfGetDateWindowsOverlap](@s1 date,@e1 date,@s2 date, @e2 date)

returns @tab table

(

[S1] date

,[E1] date

,[S2] date

,[E2] date

,[W1Total] int

,[W2Total] int

,[Overlap] int

--,[Method] nvarchar(100)

)

as

begin

insert @tab

select

[S1] = @s1

,[E1] = @e1

,[S2] = @s2

,[E2] = @e2

,[W1Total] = datediff(d,@s1,@e1) + 1

,[W2Total] = datediff(d,@s2,@e2) + 1

,[Overlap] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](@s1,@e1,@s2,@e2)

return;

end

GO

declare @tab table

(

[Scenario] int

,[S1] date

,[E1] date

,[S2] date

,[E2] date

,[W1Total] int

,[W2Total] int

,[Overlap] int

--,[Method] nvarchar(100)

)

insert @tab select [Scenario] = 1, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/1/2014','1/10/2014')

insert @tab select [Scenario] = 2, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/15/2014')

insert @tab select [Scenario] = 3, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/18/2014')

insert @tab select [Scenario] = 4, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/18/2014')

insert @tab select [Scenario] = 5, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/17/2014','1/19/2014')

insert @tab select [Scenario] = 6, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/20/2014')

insert @tab select [Scenario] = 7, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/20/2014')

insert @tab select [Scenario] = 8, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/15/2014','1/25/2014')

insert @tab select [Scenario] = 9, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/25/2014')

insert @tab select [Scenario] = 10, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/10/2014','1/20/2014')

insert @tab select [Scenario] = 11, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/18/2014','1/25/2014')

insert @tab select [Scenario] = 12, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/20/2014','1/25/2014')

insert @tab select [Scenario] = 13, * from [SIP_NPT_tfGetDateWindowsOverlap]('1/15/2014','1/20/2014','1/25/2014','1/30/2014')

select * from @tab order by [Scenario

Example Usage:

--DECLARE A TEMP TABLE
create table #temp
(
  [EmployeeId] nvarchar(100)
  ,[ProjectId] nvarchar(100)
  ,[ProjectStart] date
  ,[ProjectEnd] date
)

--FILL TEMP TABLE WITH DATA HERE
...

--DETERMINE THE OVERLAP FOR EACH EMPLOYEE PROJECT TO EVERY OTHER EMPLOYEE PROJECT
select * 
,[PercentOverlap] = 
  case when [TotalProjectDays] <> 0 and [OverlapDays] is not null then ([OverlapDays] / convert(decimal(18,2),[TotalProjectDays])) * 100
  else 0
  end
from
(
  select  
  EmployeeProject.*
  ,[OtherProjectId] = otherproject.[ProjectId]
  ,[OtherProjectStart] = otherproject.[ProjectStart]
  ,[OtherProjectEnd] = otherproject.[ProjectEnd] 
  ,otherproject.[OverlapDays]
  ,[TotalProjectDays] = datediff(d,EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd]) + 1
  from #temp EmployeeProject
  outer apply
  (
    select 
    [ProjectId]
    ,[ProjectStart]
    ,[ProjectEnd]
    ,[OverlapDays] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd],[ProjectStart],[ProjectEnd])
    from #temp
    where [EmployeeId] = EmployeeProject.[EmployeeId] and [ProjectId] <> EmployeeProject.[ProjectId]
  ) as otherproject
) as f1
order by [EmployeeId],[ProjectId],[ProjectStart],[OtherProjectStart]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

--DECLARE A TEMP TABLE

create table #temp

(

[EmployeeId] nvarchar(100)

,[ProjectId] nvarchar(100)

,[ProjectStart] date

,[ProjectEnd] date

)

--FILL TEMP TABLE WITH DATA HERE

...

--DETERMINE THE OVERLAP FOR EACH EMPLOYEE PROJECT TO EVERY OTHER EMPLOYEE PROJECT

select *

,[PercentOverlap] =

case when [TotalProjectDays] <> 0 and [OverlapDays] is not null then ([OverlapDays] / convert(decimal(18,2),[TotalProjectDays])) * 100

else 0

end

from

(

select

EmployeeProject.*

,[OtherProjectId] = otherproject.[ProjectId]

,[OtherProjectStart] = otherproject.[ProjectStart]

,[OtherProjectEnd] = otherproject.[ProjectEnd]

,otherproject.[OverlapDays]

,[TotalProjectDays] = datediff(d,EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd]) + 1

from #temp EmployeeProject

outer apply

(

select

[ProjectId]

,[ProjectStart]

,[ProjectEnd]

,[OverlapDays] = [dbo].[SIP_NPT_fGetDateWindowsOverlap](EmployeeProject.[ProjectStart],EmployeeProject.[ProjectEnd],[ProjectStart],[ProjectEnd])

from #temp

where [EmployeeId] = EmployeeProject.[EmployeeId] and [ProjectId] <> EmployeeProject.[ProjectId]

) as otherproject

) as f1

order by [EmployeeId],[ProjectId],[ProjectStart],[OtherProjectStart]

originally posted 2014-07-22

1	originally posted 2014-07-22

public static bool IsValidDomainName(string name, out string formatMessage)
{
    /*
    http://en.wikipedia.org/wiki/Domain_Name_System
    Each label may contain up to 63 characters
    The characters allowed in a label are a subset of the ASCII character set, and includes the characters a through z, A through Z, digits 0 through 9, and the hyphen. 
    This rule is known as the LDH rule (letters, digits, hyphen). Domain names are interpreted in case-independent manner.
    Labels may not start or end with a hyphen. 
    */
    formatMessage = "Up to 64 letters, digits or hyphens. Cannot start or end with a hyphen.";

    if (name == null) return false;
    return Regex.IsMatch(name, @"(^[a-zA-Z0-9][a-zA-Z0-9\-]{0,61}[a-zA-Z0-9]{0,1}$)");
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

public static bool IsValidDomainName(string name, out string formatMessage)

{

/*

http://en.wikipedia.org/wiki/Domain_Name_System

Each label may contain up to 63 characters

The characters allowed in a label are a subset of the ASCII character set, and includes the characters a through z, A through Z, digits 0 through 9, and the hyphen.

This rule is known as the LDH rule (letters, digits, hyphen). Domain names are interpreted in case-independent manner.

Labels may not start or end with a hyphen.

*/

formatMessage = "Up to 64 letters, digits or hyphens. Cannot start or end with a hyphen.";

if (name == null) return false;

return Regex.IsMatch(name, @"(^[a-zA-Z0-9][a-zA-Z0-9\-]{0,61}[a-zA-Z0-9]{0,1}$)");

}

My ISP upgraded the site from MS Server 2003 \ IIS 6 to MS Server 2012 \ IIS 8. That’s great, but it broke my old BlogEngine. I’ll be re-creating [from backups] such gems as the largest Excel cell address, and the best ending comment for a SQL stored procedure.

They aren’t a great loss, but I miss them.

<jco-blog/>

Restore SQL database from multi-part backup

Determine if a path is a UNC

Removing files from ClickOnce application manifest

SQL elapsed time between two dates

Get the SQL database size in bytes

Overlap of Two Date Windows

Validate Format of a Domain Name

Moved from a broken BlogEngine to a working WordPress installation…

Author’s Archive: ЈЦЅГЇП €ΘΘΚ