Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Method 1: Go to report property. Open Code section and paste the below code:
Public Function SecondsToText(ByVal intTotalSeconds) As String
    Dim hours As String =INT(intTotalSeconds/3600)
    If Len(hours) < 2 Then
        hours = RIGHT(("0" & hours), 2)
    End If
    Dim mins As String = RIGHT("0" & INT((intTotalSeconds MOD 3600)/60), 2)
    Dim secs AS String = RIGHT("0" & ((intTotalSeconds MOD 3600) MOD 60), 2)

    SecondsToText= hours & ":" & mins & ":" & secs

End Function

Method 2: Go to report property. Open Code section and paste the below code:

Function SecondsToText(Seconds) As String
Dim bAddComma As Boolean
Dim Result As String
Dim sTemp As String
Dim days as String
Dim hours as String
Dim minutes as String
 
If Seconds <= 0 Or Not IsNumeric(Seconds) Then
     SecondsToText = "0 seconds"
     Exit Function
End If
Seconds = Fix(Seconds)
If Seconds >= 86400 Then
  days = Fix(Seconds / 86400)
Else
  days = 0
End If
If Seconds - (days * 86400) >= 3600 Then
  hours = Fix((Seconds - (days * 86400)) / 3600)
Else
  hours = 0
End If
If Seconds - (hours * 3600) - (days * 86400) >= 60 Then
 minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)
Else
 minutes = 0
End If
Seconds = Seconds - (minutes * 60) - (hours * 3600) - _
   (days * 86400)
If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)
If minutes > 0 Then
    bAddComma = Result <> ""
   
    sTemp = minutes & " minute" & AutoS(minutes)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If hours > 0 Then
    bAddComma = Result <> ""
   
    sTemp = hours & " hour" & AutoS(hours)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If days > 0 Then
    bAddComma = Result <> ""
    sTemp = days & " day" & AutoS(days)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
SecondsToText = Result
End Function

Function AutoS(Number)
    If Number = 1 Then AutoS = "" Else AutoS = "s"
End Function

Then in field paste the below code:

=code.SecondsToText(fields!TimeInSeconds.value)

Method 3:

=DATEADD("s", SUM(Fields!TimeinSeconds.Value), CDate("00:00")).ToString("HH:mm:ss")

Attach mdf file to Database in SQL Server

Below is the code to attach *.mdf file to existing database:

USE [master]
GO
Method 1:
EXEC sp_attach_single_file_db @dbname='BISource',
@physname=N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf'
GO
Method 2:
CREATE DATABASE BISource ON
(FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3:
CREATE DATABASE BISource ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO

Determine Size of SQL Table

How to determine size of SQL server table:


Use built-in code: sp_spaceused ‘Tablename’

Example: sp_SpaceUsed 'Employee'

Mandatory Environment Variable for Pentaho

To work efficiently in Pentaho, a user must install JDK and JRE (JDK 1.7.1 and JRE7.0 or any higher version) and create below Environment Variable in Syste:

Variable Name: JRE_HOME
Variable Value: C:\Program Files\Java\jre7


Variable Name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre7

These variable allows user to browse Pentaho Admin and Server web pages.

C-Sharp Script to Derive HashValue for Multiple Columns

The below script help us to create hashvalue which can be used to compare records while inserting/updating rows in a table using SSIS packages.

In script task select the columns you want to consider for deriving hashvalue and copy-paste the below code:


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using System.Text;
using System.Security.Cryptography;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent

{

private PipelineBuffer inputBuffer;

public override void ProcessInput(int InputID, PipelineBuffer Buffer)

{

inputBuffer = Buffer;

base.ProcessInput(InputID, Buffer);

}



public override void Input0_ProcessInputRow(Input0Buffer Row)

{

var counter = 0;

var values = new StringBuilder();



//loop through input columns

for (counter = 0; counter < inputBuffer.ColumnCount; counter++)

{

object value;

value = inputBuffer[counter];

//add each column value to one big string

values.Append(value);

}

//set output column as results of hash method

Row.HashColumn = CreateHash(values.ToString());



base.Input0_ProcessInputRow(Row);

}



private string CreateHash(string data)

{

//get byte array of long data string

var dataToHash = (new UnicodeEncoding()).GetBytes(data);

//create hash provider and compute hash of byte array

var sha1 = new SHA1CryptoServiceProvider();

var hashedData = sha1.ComputeHash(dataToHash);

RNGCryptoServiceProvider.Create().GetBytes(dataToHash);

//convert results to hexadecimal string (SQL friendly format)

var result = BitConverter.ToString(hashedData).Replace("-", "");

return result;

}

}

C-Sharp Script to get IncrementalDate

The below script helps to get maximum date used for incremental load for the SSIS packages:

In below example, we have "TimeStarted" as a incremental date column. You need to create 2 string variables 'IncrementalDate' and 'NewIncrementalDate' in the package.

In script task set 'TimeStarted' (incrementaldate column) as Readonly variable and copy paste the below code and modify the highlighted text (in Yellow) as per the input column name. On executing the package 'NewIncrementalDate' variable will get updated with MaxIncremantal date.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
      DateTime NewIncrementalDate;
      String strNewIncrementalDate;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        strNewIncrementalDate = Variables.IncrementalDate.ToString();
        NewIncrementalDate = DateTime.Parse(strNewIncrementalDate);
        //MessageBox.Show("Before: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
              
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */

        /*NewMaxEventDate.AddMonths(-1);*/
        //MessageBox.Show("After: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        Variables.NewIncrementalDate = NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff");

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        try
        {
            if (!Row.TimeStarted_IsNull  && Row.TimeStarted  > NewIncrementalDate)
            {
                //MessageBox.Show("CreateDate: " + Row.createdate.ToString("yyyy-MM-dd hh:mm:ss.fff") + "; NewDate: "
                 //              + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));
                NewIncrementalDate = Row.TimeStarted;
                //MessageBox.Show("NewDate: " + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));


            }

            Row.etlcreateddate = Variables.ContainerStartTime;
            Row.etllastmodifieddate = Variables.ContainerStartTime;

        }
        catch (Exception ex)
        {
            throw new Exception("Error in ExtractTestSample script.  Message: " + ex.Message);
        }
    }

}

How to determine largest value comparing two or multiple columns using T-SQL

The below function can be used as a alternate for GREATEST() function in MYSQL:

Create a function with below code in your SQL SERVER database:

Below function compares 11 columns in a table.

CREATE FUNCTION dbo.fnGreatest
   (  @Value0  sql_variant,
      @Value1  sql_variant,
      @Value2  sql_variant,
      @Value3  sql_variant,
      @Value4  sql_variant,
      @Value5  sql_variant,
      @Value6  sql_variant,
      @Value7  sql_variant,
      @Value8  sql_variant,
      @Value9  sql_variant,
      @Value10  sql_variant
    )
   RETURNS sql_variant
AS
   BEGIN
     
      DECLARE @ReturnValue sql_variant

      DECLARE @MaxTable table
         (  RowID      int  IDENTITY,
            MaxColumn sql_variant
         )

        INSERT INTO @MaxTable VALUES ( @Value0 )
        INSERT INTO @MaxTable VALUES ( @Value1 )
        INSERT INTO @MaxTable VALUES ( @Value2 )
        INSERT INTO @MaxTable VALUES ( @Value4 )
        INSERT INTO @MaxTable VALUES ( @Value5 )
        INSERT INTO @MaxTable VALUES ( @Value6 )
        INSERT INTO @MaxTable VALUES ( @Value7 )
        INSERT INTO @MaxTable VALUES ( @Value8 )
        INSERT INTO @MaxTable VALUES ( @Value9 )
        INSERT INTO @MaxTable VALUES ( @Value10 )

       SELECT @ReturnValue = MAX(MaxColumn)
      FROM @MaxTable

      RETURN @ReturnValue

   END
GO








SELECT fnGreatest(date1,date2,date3.....date11)  from <tablename>