SQL Server - Common Table Expressions (CTE)

It's a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query.

Syntax

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

SELECT <column_list> FROM expression_name;

Multiple cTE

WITH expression_name1 [ ( column_name [,...n] ) ],
     expression_name2 [ ( column_name [,...n] ) ]   

select <column_list> FROM expression_name1
union all
select <column_list> FROM expression_name2

select <column_list> FROM expression_name1 en1 inner join expression_name2 en2 on en1.column_name= en2.column_name

SQL Server - Shortcut to add alias for all the columns

In SSMS, for example

I have a sql query,

select
    BusinessEntityID,
    Bonus,
    SalesLastYear
from
    Sales.SalesPerson

 now  i added alias Sales.SalesPerson sp, want to apply alias name to all the columns quickly, press ALT+Shift button and hold them down while i move my cursor to the lower right of the block.

Now i change to SalesPerson alias name to all column name

select
    SalesPerson.BusinessEntityID,
    SalesPerson.Bonus,
    SalesPerson.SalesYTD,
    SalesPerson.SalesLastYear
from
    Sales.SalesPerson

SQL Server Last executed query history

SELECT

    deqs.last_execution_time AS [Time],

    dest.TEXT AS [Query]

FROM

    sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY

    deqs.last_execution_time DESC

http://msdn.microsoft.com/en-us/library/ms188068.aspx

 

Dynamics CRM - Passing crm records id and Display report in the "Run on Current Record" menu section

1) Created SSRS report
2) Created 2 DataSets
    i) DSContact

          Query
              declare @sql as nVarchar(max)
              set @sql = 'SELECT fullname, contactid FROM (' + @CRM_FilteredContact+ ') as fc'
              exec(@sql)

          Field
             fullname - fullname
             contactid - contactid
          Parameters
             @CRM_FilteredContact - [@CRM_FilteredContact]

     ii) DSAuditHistory - sql query passing parameters @Contactid [@ContactID]

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))

Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"

Convert Dynamics CRM Entity GUID in SSRS

When i create hyperlink i passed  GUID (Fields!contactid.Value) in the query string, the link is not working. I tried to convert GUID to string, still the link is not working.

Finally i used CType function to concatenation with the querystring, it works.

 = Parameters!CRM_URL.Value + "/tools/audit/audit_details.aspx?_CreateFromId=&_CreateFromType=2&id={" & CType(Fields!contactid.Value, GUID).ToString  & "}"