Running Total in SQL

Different ways to achieve running total in sql, go through below link
https://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals

Using Stored Procedure in Power Pivot

To get data from sql server using stored procedures in power pivot, follow below points:

1. SET ANSI_WARNINGS OFF (there should not be any warnings returned by the query)
2. SET NOCOUNT ON (query should not return more than 1 count. In the below example execute SPand insert returns count, then again on select another count is returned. So totally 2, and it should not be the case).

EX: 

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

CREATE TABLE #tmp

(

EmployeeID INT

, Employee VARCHAR(225)

)

INSERT INTO #tmp EXEC usp_Name

SELECT * FROM #tmp

DROP TABLE #tmp

Get latest File from a folder in SSIS

Sample code do the following:

• Reads file from a Folder
• Identifies the latest file
• Rename the file and assign it to a variable

     try
            {
                var directory = new DirectoryInfo(Dts.Variables["User::SourceFolderPath"].Value.ToString());

                FileInfo[] files = directory.GetFiles();
                DateTime fileModifiedDate = DateTime.MinValue;

                foreach (FileInfo file in files)
                {
                    if (file.LastWriteTime > fileModifiedDate)
                    {
                        fileModifiedDate = file.LastWriteTime;
                        Dts.Variables["User::SourceFileName"].Value = file.ToString();
                    }

                }

                string oldFilePath = Dts.Variables["User::SourceFolderPath"].Value.ToString() + "\\" + Dts.Variables["User::SourceFileName"].Value.ToString(); // Full path of old file
                string newFilePath = Dts.Variables["User::SourceFolderPath"].Value.ToString() + "\\" + fileModifiedDate.Date.ToString("MM.dd.yyyy") + "_"
                                                                                               + Dts.Variables["User::SourceFileName"].Value.ToString(); // Full path of new file
                                  if
(Dts.Variables["User::SourceFileName"].Value.ToString().StartsWith(fileModifiedDate.Date.ToString("MM.dd.yyyy")) == false)
                    {
                        File.Move(oldFilePath, newFilePath);    //Raname file, if it donot start with latest fileCreateDate
                        Dts.Variables["User::SourceFileName"].Value = fileModifiedDate.Date.ToString("MM.dd.yyyy") + "_" + Dts.Variables["User::SourceFileName"].Value.ToString();
                    }
               
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {  
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

Download Email Attachment using SSIS Script Task

Below are the 2 different methods that can be used to download an Email Attachment using SSIS Script Task.

Method1: Using Exchange Serivce API

• If there is no exchange service API installed, download and install it from http://www.microsoft.com/en-in/download/details.aspx?id=42951
• The connection to the Exchange Service will point to your MailBox (the credential used to run the script) by default

the below code identifies the latest mail with Attachment from inbox by checking for a specific Subject and saves the attachment.

            string filePath = "";
            string fileName = "";
            DateTime latestReceivedtime = DateTime.MinValue;
            EmailMessage latestEmail = null;
            try
            {
                //Connect to ExchangeService
                ExchangeService es = new ExchangeService(ExchangeVersion.Exchange2013);
                //es.Credentials = new WebCredentials("User", "Password", "domain");
                //es.AutodiscoverUrl("URL");
                es.Url = new Uri("https://domain/EWS/Exchange.asmx"); //mail.company.com
             
                /* bind the others mailbox looking for using service instance, provided you have access to                                others mail box
                Mailbox mb = new Mailbox(@"firstname.lastname@company.com");
                FolderId fid = new FolderId(WellKnownFolderName.Inbox, mb);
                Folder inbox = Folder.Bind(es, fid);
                 */

                 //  10 mails per page in DESC order
                 ItemView view = new ItemView(10);
                 view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Descending);
             
                //Find mails
                 FindItemsResults<Item> fir = es.FindItems(WellKnownFolderName.Inbox, "Subject", view);

                 foreach (Item item in fir.Items)
                 {
                   item.Load(); //Load the entire message with attachment
                   EmailMessage email = item as EmailMessage;
                   if (email != null)
                   {
                    if (email.HasAttachments == true && email.Attachments.Count == 1)
                    {
                       if (email.Subject.StartsWith("Subject") == true)
                       {                                                  
                            if (email.DateTimeReceived > latestReceivedtime) //get latest email
                            {
                                latestReceivedtime = email.DateTimeReceived;
                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString()
                                                        , email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name);
                                fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
                                latestEmail = email;
                            }
                         }
                      }
                   }
                 }

                 //Save attachment
                 if (File.Exists(filePath) == false && filePath != "")
                 {                
                      FileAttachment fileAttachment = latestEmail.Attachments[0] as FileAttachment;
                      fileAttachment.Load(filePath);
                  }                
               

                 Dts.Variables["User::SourceFileName"].Value = fileName;

                 Dts.TaskResult = (int)ScriptResults.Success;
             
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

Method2: Using Outlook API

• Microsoft Office Outlook need to be installed
• By default, it points to outlook that is installed on the machine and uses the same credentials as Outlook mail by default does.

below is the sample code that identifies latest Email from a sender and the subject which has an attachment and saves the attachment in a folder.

            Microsoft.Office.Interop.Outlook.Application app = null;

            Microsoft.Office.Interop.Outlook._NameSpace ns = null;

            Microsoft.Office.Interop.Outlook.MAPIFolder inbox = null;

            string filePath = "";

            string fileName = "";

            DateTime latestReceivedtime = DateTime.MinValue;

            try

            {

              app = new Microsoft.Office.Interop.Outlook.Application();

              ns = app.GetNamespace("MAPI");

              //ns.Logon(null, null, false, false);

              inbox = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);

              Microsoft.Office.Interop.Outlook.Items inBoxItems = inbox.Items;

              Microsoft.Office.Interop.Outlook.MailItem newEmail = null;

              // inBoxItems = inBoxItems.Restrict("[Unread] = true");

              Microsoft.Office.Interop.Outlook.MailItem latestEmail = null;

              foreach (object collectionItem in inBoxItems)

              {

                 newEmail = collectionItem as Microsoft.Office.Interop.Outlook.MailItem;

                 if (newEmail != null && newEmail.SenderEmailAddress == "SenderEmail") //newEmail.SenderName == "senderName")  

                 {

                    if (newEmail.Attachments.Count == 1)// && newEmail.ReceivedTime.Date == DateTime.Now.Date)

                    {

                        if(newEmail.Subject.StartsWith("Subject") == true && newEmail.Attachments[1].FileName.StartsWith("FileName") == true)

                        {

                            if (newEmail.ReceivedTime > latestReceivedtime) //get latest email

                            {

                                latestReceivedtime = newEmail.ReceivedTime;

                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString(), newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName);

                                fileName = newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName.ToString();

                                latestEmail = newEmail;                                

                            }

                        }

                    }

                 }

              }

              if (File.Exists(filePath) == false)

              {

                  latestEmail.Attachments[1].SaveAsFile(filePath);

              }

              Dts.Variables["User::SourceFileName"].Value = fileName;

              Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (System.Runtime.InteropServices.COMException ex)

            {

                //Unlock variables

                if (Dts.Variables.Locked == true)

                {

                    Dts.Variables.Unlock();

                }

                //An error occurred.

                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

Instant SQL \ MDX & DAX Formatter

To format a SQL \ MDX & DAX Script Instantly one can use the below link. Its really good !

http://www.dpriver.com/pp/sqlformat.htm

http://formatmdx.azurewebsites.net/

SSAS -Tabular Model and SSRS - DAX Query

• SSAS -Tabular Model and SSRS - DAX Query:
  1. Tabular model tutorials : http://msdn.microsoft.com/en-in/library/hh231691.aspx
  2. Points to remember for Direct query mode in Tabular Model - http://msdn.microsoft.com/en-IN/library/hh230898.aspx
  3. DAX for Tabular Model : http://msdn.microsoft.com/en-in/library/hh213101.aspx#bkmk_DAXintm
  
  Note:
  1. Multiple DataSources are not allowed in Direct Query mode.
  2. Calculated Columns are not supported in Direct query mode.
  3. MDX doesnt work if Model is in Direct Query mode. Use DAX or XMLA.
  4. Direct Query mode doesnt support some DAX functions. http://msdn.microsoft.com/en-us/library/hh213006.aspx

# Keywords for SSRS Charts

To increase readability of a chart, we can use #keywords.
Example: Disable the Legend in the chart. And append the Legend text to the DataLabels

The link gives more information http://msdn.microsoft.com/en-us/library/dd207017.aspx

JavaScript in SSRS Reports

JavaScript can be used in SSRS Reports to meet atypical requirements.

Note : Type the following code "Go To URL"  in Action Tab

1. Open a report in a new Tab from other report

="javascript:void(window.open('http://to be opened report URL from report Manager'))"

2. Alert Box

="javascript:void alert('Some Alert Message')"

3. Confirmation Box

="javascript:var check=confirm('Do you want to navigate to New report?'); if (check == true) {window.open('http://to be opened report URL from report Manager')}"

4. Prompt Box

="javascript:var FirstName=prompt('what is your First Name?');LastName=prompt('what is your Last Name?'); if(FirstName!='') {void alert(FirstName+','+LastName)} "

5. Using Parameters in javascript

="javascript:var check=confirm('Do you want to generate the report for the selected DateRange?"& Parameters!StartDate.Value & "-" & Parameters!EndDate.Value & "'); if (check == true) {window.open('http://to be opened report URL from report Manager','_self')}"

Pie Chart with data labels outside in ssrs

Fig I

Fig II

Inorder to convert Fig I to Fig II, Select LabelStyle as Outside.

Note : Select CollectedStyle as SingleSlice if you want to group smaller units into a single unit as "Others"
Select CollectedStyle as CollectedPie to get another small pie chart that gives details of "Other" sector.

Differences between Temp Tables and Temp Table Variables in SQL Server

Some facts that i have noticed while working with Temp Tables and Temp Table Variables in SQL Server

-- Create Temp Table
-- It gets Stored in TempDB ---> Temporary Tables
CREATE TABLE #Test(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20)
)

--Skips FK. FOREIGN KEY constraints are not enforced on local or global temporary tables.
CREATE TABLE #Test1(
ID INT REFERENCES #Test(ID) ,
NAME VARCHAR(20)
)

--Declare Table Variable
--It also gets stored in TempDB
DECLARE @Test TABLE(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20) UNIQUE NONCLUSTERED
)
--NON Clustered Indexing
--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON @Test(Name) -- Not possible, however we can create PK and UNIQUE constraint

--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON #Test(Name) -- possible

--Insert Records in #Test and @Test
DECLARE @i int = 0;
DECLARE @j VARCHAR(MAX);
WHILE @i < 1000000
BEGIN
    SET @i += 1;
SET @j = CAST(@i AS VARCHAR(MAX));
INSERT INTO @Test VALUES ('NAME'+@j);
END;
--SELECT * FROM @Test WHERE NAME='NAME3'
--EXEC sp_Test @Test

--SELECT Records
;With CTE1 AS(
SELECT * FROM @Test)
SELECT * FROM CTE1

SELECT * FROM @Test WHERE ID = 10  -- Fetching records from @Test Takes double time than #Test

SELECT * FROM #Test

;With CTE1 AS(
SELECT * FROM #Test)
SELECT * FROM CTE1

--Join Table Variables and Temp Tables
SELECT T.ID,T.NAME
FROM @Test T
JOIN #Test S ON S.ID = T.ID

--Table Variable with FK NOT ALLOWED
DECLARE @Test1 TABLE(
ID INT, --REFERENCES #Test(ID),
NAME VARCHAR(20)
)

--Connot Create Triggers on temp
CREATE TRIGGER trgAfterInsert ON #Test
FOR INSERT
AS
PRINT 'Records Inserted'
Go

--TABLE Valued Parameters
CREATE TYPE Testing AS TABLE(
ID int,
NAME VARCHAR(20))

CREATE PROCEDURE sp_Test
(
@T AS Testing READONLY
)
AS
BEGIN
SELECT * FROM @T
END

SQL to DAX - Sample Queries

Following queries gives an idea how to convert a SQL query to DAX.
Note: In last query WorkingDays represents the total Business days for a given period of time.

/* SELECT * FROM Projects
EVALUATE
(
'Projects'
)
*/

/*SELECT ProjectNames FROM Projects
EVALUATE
(
SUMMARIZE
(
'Projects'
,[ProjectName]
)
)
*/

/* SELECT COUNT(distinct [WorkDate]) AS Holidays
FROM [dbo].[Invoices] (NOLOCK)
WHERE [ProjectId] = 8
EVALUATE
(
ROW("TotalHolidays", COUNTROWS(
FILTER('Invoices',[ProjectId]=8)))
)
*/

/* WITH CTE1 AS (
SELECT [EmpName]
, MIN([WorkDate]) AS StartDate
, MAX([WorkDate]) AS EndDate
FROM [dbo].[Invoices] (NOLOCK)
GROUP BY [EmpName]),

CTE2 AS (
SELECT [EmpName]
, SUM([Hours]) AS BillingHours
FROM [dbo].[Invoices] (NOLOCK)
WHERE [BillingType] = 'Billable'
GROUP BY [EmpName])

SELECT C.EmpName
, C.StartDate
, C.EndDate
, T.BillingHours
, T.BillingHours/((SELECT (DATEDIFF(dd, C.StartDate, C.EndDate) + 1)
-(DATEDIFF(wk, c.StartDate, C.EndDate) * 2)
-(CASE WHEN DATENAME(dw, C.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, C.EndDate) = 'Saturday' THEN 1 ELSE 0 END)
-@Holidays)*8) AS Utilization
FROM CTE1 C
JOIN CTE2 T ON C.EmpName = T.EmpName

*/
DEFINE
MEASURE 'Invoices'[StartDate] = MIN('Invoices'[WorkDate])
MEASURE 'Invoices'[EndDate] = MAX('Invoices'[WorkDate])
MEASURE 'Invoices'[BillingHours] = CALCULATE(SUM('Invoices'[Hours]),'Invoices'[BillingType] = "Billable")
MEASURE 'Invoices'[WorkingDays] = ((1. * (MAX('Invoices'[WorkDate])-MIN('Invoices'[WorkDate])))+1)
-((WEEKNUM(MAX('Invoices'[WorkDate])))-(WEEKNUM(MIN('Invoices'[WorkDate]))))*2
-(IF(WEEKDAY( MIN('Invoices'[WorkDate]))=1,1,0))
-(IF(WeekDAy( MAX('Invoices'[WorkDate]))=7,1,0))
EVALUATE
SUMMARIZE
(
'Invoices'
,[EmpName]
,"StartDate"
,'Invoices'[StartDate]
,"EndDate"
,'Invoices'[EndDate]
,"BillingHours"
,'Invoices'[BillingHours]
,"Utilization"
,'Invoices'[BillingHours]/'Invoices'[WorkingDays]
)

Sample DAX Queries

Server Info from SSIS

We can use WMI(Windows Management Instrumentation) to find Server Information from SSIS rather than using SQL. See below link
http://www.mssqltips.com/sqlservertip/2932/using-the-ssis-wmi-task-to-gather-system-information/

Comparing T-SQL and MDX

http://www.mssqltips.com/sqlservertip/2916/comparison-of-queries-written-in-tsql-and-sql-server-mdx/

This Link may be helpful to some extent to understand MDX

Eecution Plan in SQL Server

http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

Change the width of bar in SSRS

 select the series you are interested in on the chart, either via the chart flange or by selecting the bar or column on the chart. Look over to the property grid and look for a section called "General". Under the "General" heading there is an area for custom attributes. Then change "PointWidth" accordingly. We can also write expression to change the width of bar in "pointwidth".
Example:
=IIF(CInt(CountDistinct(Fields!PaymentType.Value,"ReportMainDS"))< 3,.3,1.98)

Full Text Search in SQL

• http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Like Excel ,Trend Line in SSRS Charts

http://ericung.blogspot.com/2011/07/ssrs-2005-bar-chart-adding-linear-trend.html

Format Functions in SSRS Expressions

User-Defined Numeric Formats (Format Function) - 

http://msdn.microsoft.com/en-us/library/4fb56f4y%28v=VS.80%29.aspx

User-Defined Date/Time Formats (Format Function) -

 http://msdn.microsoft.com/en-us/library/73ctwf33%28v=VS.80%29.aspx 

Format Function - http://msdn.microsoft.com/en-us/library/59bz1f0h%28v=VS.80%29.aspx

Example:

Date Format in SSRS
=format(Fields!IssueDateTime.Value,"yyyy-MM-dd HH:mm:ss")

Validate Date Time Parameters in SSRS

If there are 4 parameters e.g start date, end date, start time, end time , then validating date range( say 7 days) and handling invalid inputs can be done with the following code in "No row Message" property of the control

IIF(Count("MainDS") = 0,
  IIF(DateDiff("s",CDate(CStr(FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate)) & " "& Parameters!StartTime.Value),CDate(CStr(FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate)) & " "& Parameters!EndTime.Value)) > 604800,"Date Range Should Not Exceed more than 7 days",

IIF(DateDiff("D",Parameters!StartDate.Value,Parameters!EndDate.Value) < 0, "Start Date should be less than End Date",
  IIF(DateDiff("s",CDate(CStr(FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate)) & " "& Parameters!StartTime.Value),CDate(CStr(FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate)) & " "& Parameters!EndTime.Value))<0,"If Start Date and End Date are same then Start Time should be less than End Time","No Data Available"))),nothing