SQL - Create Unique ID using SQL Server

DECLARE @STR VARCHAR(50)

---This is with seconds

SET @STR='ID'+(SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),5),'-','')+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''))

---This is with milliseconds
PRINT @STR

SET @STR='ID'+(SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),5),'-','')

+(REPLACE(CONVERT(VARCHAR,GETDATE(),114),':','')))

PRINT @STR

Output:

1)ID131014102125

2)ID131014102145910

Formatted Invoice No.

Select 'EI-U5/'+Cast(right(Year(DateAdd(YY,-1,getdate())),2) as varchar)+'-'+Cast(right(Year(getdate()),2) as varchar)+'/'+REPLACE(CONVERT(VARCHAR,GETDATE(),114),':','')

Output:

EI-U5/14-15/113021013

SSRS - List Demo using SSRS

You can get RDL file from this link 

https://drive.google.com/file/d/0B5rqPoeNMlf-ZzVJQzFGcmNQc28/edit?usp=sharing

Result:

SSRS - Multiple value (Parameter) in textbox

=IIF(Parameters!type.Count=6,"ALL",(JOIN(Parameters!type.Value,",")))

--Here 6 is total no of values in parameter

Result:

SQL - Dynamic Pivot in SQL Server

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

 

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')

       + QUOTENAME(Course)

FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

 

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

  N'SELECT Year, ' + @ColumnName + '

    FROM #CourseSales

    PIVOT(SUM(Earning)

          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery




--OR




EXEC(@DynamicPivotQuery)

Result:







SQL - Encrypt and Decrypt Password using SQL Server

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)

set @source = convert(varbinary(max), 'MyPassword')

set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar


Result: