Sql Server & SSRS Tips

Use the query below to get all the dates, months, quarters and years with in two dates. if object_id('tempdb..#Calendar_Test')is not null drop table #Calendar_Test declare @Date_Start datetime, @Date_End datetime select @Date_Start = '2000-01-01 00:00:00.000' select @Date_End = '2020-12-31 00:00:00.000'; with CTE_Dates as (     select @Date_Start as [Date]     union all     select dateadd(dd, 1, [Date])     from CTE_Dates     where [Date] < @Date_End ), CTE_Calendar as (     select         [Date], datename(dd, [Date]) as [Day_ID],         datename(dw, [Date]) as [Day],         datepart(ww, [Date]) as [Week],         datepart(mm, [Date]) as [MonthID],         datepart(yy, [Date]) as [YearID],         datepart(qq, [Date]) as [QuarterID]      from CTE_Dates ) se...

If you want to add a  hyphen or any other sign in between a varchar column then you can you Stuff built in function of sql server. syntax is declare @test varchar(max) = '1234567890' SELECT STUFF(STUFF(@test,4,0,'-'),8,0,'-') the output of the following sql statement is "123-456-7890" 4,0 and 8,0 in stuff function means, 4 means insert hyphen at 4th position 0 means replace characters with  hyphen .In this case its 0 so it will not replace any character just insert  hyphen sign. Same as the case with 8,0

Syntax: SELECT names          , salary          ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER          ,rank () OVER (ORDER BY salary DESC) as RANK          ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK  FROM salaries Output: Row_number assign different number to them. Rank and Dense_rank both assign same rank to A and B. But interesting thing is what RANK and DENSE_RANK assign to next row? Rank assign 5 to the next row, while dense_rank assign 4. The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.   These all can be used ...

One way is tou use subquery like this: select Min(Salary) from salary where salary in ( select distinct top 3 salary from salary order by salary desc) ********************************************* Other way is to use dense rank this: select top 1 * from ( select salary,rn=dense_rank() over (order by salary desc) from salary ) as a where a.rn=3 These queries are for third highest salary, if you want your desired highest salary then replace TOP 3 with TOP (desired number) from above query. And Replace Where a.rn=3 with Where a.rn=(Desired number) from 2nd query.

If you want to kill all the connection of a database at once then use below query. you have to write your database name in place of DatabaseName (written in the below query) USE master; GO ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE DatabaseName SET MULTI_USER; GO

SELECT  L.request_session_id AS SPID,     DB_NAME(L.resource_database_id) AS DatabaseName,     O.Name AS LockedObjectName,     P.object_id AS LockedObjectId,     L.resource_type AS LockedResource,     L.request_mode AS LockType,     ST.text AS SqlStatementText,           ES.login_name AS LoginName,     ES.host_name AS HostName,     TST.is_user_transaction as IsUserTransaction,     AT.name as TransactionName,     CN.auth_scheme as AuthenticationMethod FROM    sys.dm_tran_locks L     JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id     JOIN sys.objects O ON O.object_id = P.object_id     JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id     JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id     JOIN sys.dm_tran_active_tra...

Say we have a Table (Product) ProductID, ProductName, IntParentID, IntCategoryID. With the script below the intCategoryid of Parent  will become the intCategoryID of Child. ;with cte as ( select intCategoryId, intProductID from Product where intCategoryId is not null and intProductID is not null union all select cte.intCategoryId, t.intProductID from Product t join cte on t.intParentCategory = cte.intProductID and t.intCategoryId is null ) update t set t.intCategoryId = cte.intCategoryId from Product t join cte on t.intProductID = cte.intProductID option (maxrecursion 5000)

This code is copied from the link mentioned below http://www.codeproject.com/Articles/25622/Sql-Queries-most-asked-in-job-interviews Let the table employee_test1 contain some duplicate data like:- CREATE TABLE Employee_Test1 ( Emp_ID INT, Emp_name Varchar( 100 ), Emp_Sal Decimal ( 10 , 2 ) ) INSERT INTO Employee_Test1 VALUES ( 1 , 'Anees' , 1000 ); INSERT INTO Employee_Test1 VALUES ( 2 , 'Rick' , 1200 ); INSERT INTO Employee_Test1 VALUES ( 3 , 'John' , 1100 ); INSERT INTO Employee_Test1 VALUES ( 4 , 'Stephen' , 1300 ); INSERT INTO Employee_Test1 VALUES ( 5 , 'Maria' , 1400 ); INSERT INTO Employee_Test1 VALUES ( 6 , 'Tim' , 1150 ); INSERT INTO Employee_Test1 VALUES ( 6 , 'Tim' , 1150 ); Step 1:  Create a temporary table from the main table as:- e select top 0* into employee_test1_temp from employee_test1 Step2 :  Insert the result of the GROUP BY query into the temporary tabl...

SELECT u.intUserId, ( SELECT  cast(Roles.vchrole AS varchar)+ ', ' FROM roles INNER JOIN user_roles ur ON ur.intRoleId=roles.intRoleId INNER JOIN users u1 ON u1.intUserId=ur.intUserId WHERE u.intUserId=u1.intUserId FOR XMl PATH ('') ) AS AllRoles FROM users  u GROUP BY u.intUserId Another Solution Below:   SELECT   @EmployeeIds   =   COALESCE ( @EmployeeIds   +   ',' ,   '' )   +   CAST ( EmployeeId   AS   VARCHAR ( 5 ))        FROM   Employees        WHERE   City   =   @City

UPDATE Property_New SET    PropertyNumber = ( CASE                  WHEN property.StatusId= 4 THEN 1                  when property.StatusId= 2 THEN 3                  when property.StatusId= 3 THEN 1                  when property.StatusId= 6 THEN 1                  when property.StatusId= 7 THEN 1                  when property.StatusId= 1 THEN 2                                 END ) FROM Property_New inner join Property on Property_New.PropertyID=Property.Propertyid where OrderId=1

Disable all constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" Delete data in all tables EXEC sp_MSForEachTable "DELETE FROM ?" Enable all constraints exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" if some of the tables have identity columns we may want to reseed them EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

-- Missing Index Script -- Original Author: Pinal Dave (C) 2011 SELECT TOP 25 dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, dm_migs.last_user_seek AS Last_User_Seek, OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mi...

create table #temptable     (         UName varchar(1000) null,         date datetime null,         vchstatus varchar(100) null,         ID int              )              INSERT INTO #temptable (UName,date,vchstatus,id)    select distinct isnull(users.vchFirstName,'Scrapper') + ' ' + isnull(users.vchLastName,'') as UName ,property_status.dtmdatecreated as date ,vchStatus ,'1' from property_status (nolock) left outer join users on property_status.intManualUpdateBy = users.intUserID left outer join property_enumStatus es on property_status.tntstatusid = es.tntstatusid where intpropertyid = @pintPropertyID union all SELECT ISNULL(u.vchFirstName, 'UNKNOWN') + ' ' + ISNULL(u.vchLastName, '') AS UName ,pdv.dtmmodhistory as date , null as vchStatus ,'2' FROM PropertyDeskV...

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROC [dbo].[usp_Worst_TSQL] /* Written by: Gregory A. Larsen Copyright © 2008 Gregory A. Larsen.  All rights reserved. Name: usp_Worst_TSQL Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,              I/O and Elapsed_Time as identified using DMV information.  This can be display the worst              performing queries from an instance, or database perspective.   The number of records shown,              the database, and the sort order are identified by passing pararmeters. Parameters:  There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT              and @ORDERBY.  The @DBNAME is used to constraint the output to a specific database.  If   ...

--SearchAllTables 'Jawad',joji CREATE PROC [dbo].[SearchAllTables] ( @SearchStr nvarchar(100) ,@ReplaceStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370),ColumnName2 nvarchar(370) , ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),@sql VARCHAR(2000) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN     SET @ColumnName = ''     SET @TableName =     (         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))         FROM    INFORMATION_SCHEMA.TABLES         WHERE       TABLE_TYPE = 'BASE TABLE'             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName             AND OBJ...

declare @sd date=convert(varchar(10),getdate(),120),@ed date =convert(varchar(10),getdate(),120) ;WITH CTE as (  select users.user_name,users.u_fbid  ,count([messages].MessageID) SENT from  [messages] with (nolock)    left join users on messages.SenderFbID=users.u_fbid    where [messages].IsDelevered=0     and ([messages].messagedate   between     (case @StartDate when null then @sd else @Startdate end)     AND     (Case @EndDate when null then @sd else @EndDate end)   or Convert(varchar(10),messagedate,120)= @sd   )           group by users.user_name,users.u_fbid   ),   CTE2 as (  select users.user_name,users.u_fbid  ,count([messages].MessageID) received from  [messages] with (nolock)    left join users on messages.SenderFbID=users.u_fbid    where [messages].IsDelevered=1 and   (Convert(varchar(...