Swim in SQL server

Archive for November, 2011

Self Join in SQL Server

Self Join:

Self Join is nothing but joining one table with different names. We can use Inner Join or Outer Join to join the table.
Here is the small example which gives the difference between consecutive days of temperature.

CREATE TABLE #TBL (CDATE DATETIME , TEMP INT)

INSERT INTO #TBL
VALUES( ‘2011-11-30 16:11:59.797’ , 20),
(‘2011-12-01 16:11:59.797’ , 30),
(‘2011-12-02 16:11:59.797’ , 10),
(‘2011-12-03 16:11:59.797’ , 50),
(‘2011-12-04 16:11:59.797’ , 20),
(‘2011-12-05 16:11:59.797’ , 70),
(‘2011-12-06 16:11:59.797’ , 40),
(‘2011-12-07 16:11:59.797’ , 30),
(‘2011-12-08 16:11:59.797’ , 10),
(‘2011-12-09 16:11:59.797’ , 50)

SELECT CONVERT(VARCHAR(30) , T.CDATE,6) + ‘———‘ + CONVERT(VARCHAR(30) , TT.CDATE,6) CURRENT_DT
,T.TEMP CURRENT_DATE_TEMP , TT.TEMP TOMORROW_TEMP
, T.TEMP – TT.TEMP DIFF
FROM #TBL T
INNER JOIN #TBL TT ON TT.CDATE = DATEADD(DD,1,T.CDATE )

DROP TABLE #TBL

How to get the last execution Time of Stored Procedure

–With Text:
SELECT
                 qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
                 qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM      sys.dm_exec_query_stats AS qs
                 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
                 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE  st.text like '%S_GET_SerialNos%'
 
 
–Tip2:
SELECT      db_name(database_id) DatabaseName, object_name(object_id) ObjectName, *
FROM          sys.dm_exec_procedure_stats
WHERE      database_id = DB_ID()
ORDER BY database_id