Get Sum of Parent and Child using ROLL UP function

Using below queries we can find the sum of any value for parent level hierarchy and child level hierarchies:

DECLARE @STARTDATE DATETIME

DECLARE @TODATE DATETIME

SET @STARTDATE = '1/1/2011'

SET @TODATE = '1/31/2011'

SELECT      CASE WHEN (GROUPING(PP.ProjectName)= 1) THEN 'Overall Total'

            ELSE PP.ProjectName END  AS Projects

            ,CASE WHEN (GROUPING(UU.Firstname) = 1) Then 'Project Total'

            ELSE UU.Firstname END AS Employee

            ,SUM(ISNULL(Duration,0)) AS BookedHrs

FROM [Dotnet2005].[Timesheet].dbo.Timetrack TT

JOIN [Dotnet2005].[Timesheet].dbo.Projects PP ON PP.ProjectId = TT.ProjectID

JOIN [Dotnet2005].[Timesheet].dbo.[User] UU ON UU.UserID = TT.UserID

WHERE TT.Datee >= @STARTDATE AND TT.Datee <= @TODATE

GROUP BY PP.ProjectName

             ,UU.Firstname WITH ROLLUP

ORDER BY PP.Projectname

=========================================================================

DECLARE @STARTDATE DATETIME

DECLARE @TODATE DATETIME

SET @STARTDATE = '1/1/2011'

SET @TODATE = '1/31/2011'

SELECT      CASE WHEN (GROUPING(DD.DepartmentName) = 1) THEN 'ALL'

            ELSE DD.DepartmentName END AS 'Department'

            ,CASE WHEN (GROUPING(CA.CostTypeName) = 1) THEN 'Department Total'

            ELSE CA.CostTypeName END AS 'Cost Type Total'

            ,SUM(ISNULL(Duration,0)) AS BookedHrs

FROM [Dotnet2005].[Timesheet].dbo.Timetrack TT

JOIN [Dotnet2005].[Timesheet].dbo.[User] UU ON UU.UserID = TT.UserID

JOIN [Dotnet2005].[Timesheet].dbo.[Department] DD ON DD.DepartmentID = UU.DepartmentID

JOIN [Dotnet2005].[Timesheet].dbo.[Tasks] TA ON TA.Taskid = TT.Taskid

JOIN [Dotnet2005].[Timesheet].dbo.[CostType] CA ON CA.CostTypeid = TA.CostTypeid

WHERE TT.Datee >= @STARTDATE AND TT.Datee <= @TODATE

GROUP BY DD.DepartmentName

            ,CA.CostTypeName WITH ROLLUP

ORDER BY DD.DepartmentName

            ,CA.CostTypeName       

           

Difference between Rank and Dense_Rank in SQL

SELECT
DimDepartment.DepartmentName, COUNT(EmployeeKey) As Total,RANK() OVER (ORDER BY COUNT(EmployeeKey) DESC) AS 'Rank_Dep',DENSE_RANK() OVER (ORDER BY COUNT(EmployeeKey) DESC) AS 'Rank_Emp'FROM DimEMployeeJOIN DimDepartment ON DimDepartment.DepartmentKey = Dimemployee.DepartmentKeyGROUP BY DimDepartment.DepartmentNameORDER

In the above table 'Human Resource' and 'Axapta' has same total hence they are positioned in 16, but the next row operation displays position as 18 and 17 for Rank( ) and Dense_Rank( ) function. Hence dense rank is continuous numbering whereas ranking counts the tie rows for numbering and Jumps to next. BY COUNT(EmployeeKey)DESC

Query to get the rank of employee by Production Hours

 Get the Rank of employee with more working hours:

SELECT
DimEmployee.Firstname, DimDepartment.DepartmentName, SUM(DurationInHours) As Total,ROW_NUMBER() OVER (ORDER BY DimEmployee.Firstname) AS 'Row Number',RANK() OVER (ORDER BY DimDepartment.DepartmentName) AS 'Rank_Dep',RANK() OVER (ORDER BY SUM(DurationInHours) DESC) AS 'Rank_Emp'FROM FactTimetrackJOIN DimEmployee on DimEmployee.Employeekey = FactTimetrack.EmployeeKeyJOIN DimDepartment ON DimDepartment.DepartmentKey = Dimemployee.DepartmentKeyGROUP BY DimEmployee.Firstname, DimDepartment.DepartmentNameORDER



BY SUM(DurationInHours) DESC,DimEmployee.Firstname

Command to copy fiile from one server to another:

Command to copy fiile from one server to another:

set path=c:\WINDOWS\system32;
echo Copying started at %date% %time%>>_date_.txt
xcopy file://devsql2000/Datafiles/CRM/CRMXMLFile.xml file://dbnew/Datafiles/CRM/CRMXMLFile.xml /s /a /d

echo Copying finished at %date% %time%>>_date_.txt
echo Completed Successfully at %date% %time%>>_date_.txt
echo --------------------------------------------------------------------------- >>_date_.txt

Compare two identical tables and list rows that are not present in another table

Consider two tables Table_A (EmployeeID, Firstname, Lastname, Designation, Address) and Table_B (EmployeeID, Firstname, Lastname, Designation, Address).

Table_A contains 10,000 rows and Table_B contains 5000 rows. I want to get the rows from Table_A which are not present in Table_B:

SELECT * FROM Table_A
WHERE NOT EXISTS (SELECT 'X' FROM Table_B WHERE Table_B.EmployeeID = Table_A.EmployeeID)

I want to get the rows from Table_A which are present in Table_B:

SELECT * FROM Table_A
WHERE EXISTS (SELECT 'X' FROM Table_B WHERE Table_B.EmployeeID = Table_A.EmployeeID)

Query to list rows present in one table which are not present in another table

I have two tables DimCountry with Countrykey (PK), Countryname columns and DimCity with CityKey (PK), Cityname, CountryKey (FK).

I want to get all country names from DimCountry which are not present in DimCity tables. To achieve this below query helps:
 

Exclude empty rows in excel sheet while extracting data via SSIS

While extracting data from excel using SSIS tasks, we may come across the below error message due to the presence of empty rows:

"...The column status returned was: "The value violated the integrity constraints for the column....",

To avoid the above error create a conditional split tasks with the following command:

"ISNULL(Hardware) || LEN(Hardware) == 0"

Handle Null Values in Derived Column

The below command will replace the null value with text UNKNOWN. While extracting rows from excel the empty rows will also be considered and to avoid package error it is advised to use derived column with the following command:

ISNULL(Hardware) ? "UNKNOWN" : Hardware


Also Refer http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx

SSIS 2008 - Script task to Import Excel file from SharePoint

Use the below VB script to import excel file from SharePoint in SSIS 2008 Script Task:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 
 ' The execution engine calls this method when the task executes.
 ' To access the object model, use the Dts property. Connections, variables, events,
 ' and logging features are available as members of the Dts property as shown in the following examples.
 '
 ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
 ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
 ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
 '
 ' To use the connections collection use something like the following:
 ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
 ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
 '
 ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
 '
 ' To open Help, press F1.

    Public Sub Main()
        Dim URL As String = "http://citind:28000/Excel%20Library/HelpdeskSoftware/Software%20usage%20report.xls"
        Dim Req As System.Net.HttpWebRequest = DirectCast(System.Net.WebRequest.Create(URL), System.Net.HttpWebRequest)
        'Req.Credentials = System.Net.CredentialCache.DefaultCredentials
        Req.Credentials = New System.Net.NetworkCredential("datawh", "500%sec", "cit")
        Req.Method = "GET"
        Dim path As String = "C:\DataFiles\HelpDesk\Software usage report.xls"
        Dim objResponse As System.Net.WebResponse = Req.GetResponse()
        Dim fs As New System.IO.FileStream(path, System.IO.FileMode.Create)
        Dim stream As System.IO.Stream = objResponse.GetResponseStream()
        Dim buf As Byte() = New Byte(1023) {}
        Dim len As Integer = stream.Read(buf, 0, 1024)
        While len > 0
            fs.Write(buf, 0, len)
            len = stream.Read(buf, 0, 1024)
        End While
        stream.Close()
        fs.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

 

SSIS 2005 - Script Task to Import Excel file from SharePoint

Use the below VB script in SSIS Script task to Import Excel file from SharePoint:


Script task code

Option Strict Off
Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports Microsoft.VisualBasic.Compatibility.VB6
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Dim TheFile As String
Dim DestFolder As String
Dim URL As String
'Dim xml As MSXML.XMLHTTPRequest
Public Sub Main()
Try
TheFile = "CustomerList.xls"
DestFolder = "C:\SSIS_Sources\Customer List\"
URL = "http://citsharepoint/portals/SM/Reports/Shared Documents/Customer List/CustomerList.xls"
Dim xml = CreateObject("Microsoft.XMLHTTP")
xml.Open("GET", URL, False)
xml.Send()
Dim oStream = CreateObject("Adodb.Stream")
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adSaveCreateNotExist = 1
oStream.type = adTypeBinary
oStream.open()
oStream.write(xml.responseBody)
' Overwrite an existing file
oStream.savetofile(DestFolder & TheFile, adSaveCreateOverWrite)
oStream.close()
oStream = Nothing
xml = Nothing
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
End Try
End Sub
End Class

MDX Named Set to find the ratio of Dimension Members

In order to find out ratio of Non-Billable hrs of Various cost type use below named set:

[Measures].[Non-Billable Hrs]/(Root([Tasks]),[Measures].[Non-Billable Hrs])*100

where Tasks is the dimension name.

 


















Similary in order to find Sales Proportion of different country use the below named set

where Locationis the Dimension name.

[Measures].[Sales Amt]/(Root([Location]),[Measures].[Sales Amt])*100

Repeating Row Header in SSRS 2008 Page

1. Perform the following steps to repeat headers in SSRS 2008:



2. Set the prperties as shown in below image:

3. Set Fixed Data as True to view header while scrolling.

XMLA Script to Process 2008 & 2005 Cubes

XMLA Script for 2008 Cube Processing

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><Parallel><Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"><Object><DatabaseID>Mercury CRM</DatabaseID></Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process>
</


XMLA Script for 2005 Cube Processing
 Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><Parallel><Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"><Object><DatabaseID>CIT Global WareHouse</DatabaseID></Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process>
</</Parallel>Batch>

<</Parallel>Batch>

SQL Query to insert Thousand Separators in Numeric Value

SQL Query to insert Thousand Separators in Numeric Value

SELECT REPLACE(CAST(CONVERT(varchar, CAST(123006 AS money), 1) AS varchar),'.00', '')

Named Set to Display Months of Previous Year

MDX Named set to get Months of Current Year

Order by Descending Order

Non Empty