asp.net codes

Thursday, May 28, 2020

hide the jquery validationEngine closePrompt error message

$.validationEngine.closePrompt(".formError", true);

Sunday, January 19, 2020

Find Column Name From All Tables of Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%clientid%'
ORDER BY schema_name, table_name;

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%client_id%'
ORDER BY schema_name, table_name;

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%client%'
ORDER BY schema_name, table_name;

Monday, December 30, 2019

TOP 5 ITEMS FROM EACH CATEGORY

;with Rohi as
(
select
GlobalCodeName ,sortorder,GlobalCodeCategoryID,
ROW_NUMBER() OVER (PARTITION BY GlobalCodeCategoryID order by GlobalCodeCategoryID ) AS 'RowNum'

from GlobalCodes
)
select * from Rohi where RowNum <=5

select (max(SortOrder)) + 1 from GlobalCodes where GlobalCodeCategoryID=13
--pass the date
select Isnull(max(SortOrder),0) + 1 from GlobalCodes where GlobalCodeCategoryID=13

usage of ntile

CREATE TABLE ntile_usage (
ID INT NOT NULL
);

INSERT INTO ntile_usage(ID)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

SELECT * FROM ntile_usage;

declare @p int = 4

SELECT
ID,
NTILE (@p) OVER (
ORDER BY ID
) GroupIDs
FROM
ntile_usage;

Group by with use CUBE, ROLLUP AND GRUPING SETS

CREATE TABLE Sales ( Country varchar(50), Region varchar(50), Sales int );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

INSERT INTO sales VALUES (N'United States', N'NY', 300);

INSERT INTO sales VALUES (N'Canada', N'Alberta', 150);

INSERT INTO sales VALUES (N'Canada', N'Ottwa', 150);

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );

Easiest way to format number to using JavaScript

var currency = $(sender).val();
var _amtToVal = (Math.round(currency * 100) / 100).toFixed(2);//.toLocaleString('en-US');
var _ForamtValue = _amtToVal.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, "$1,");

Where sender is the control id

var currency = $('#sender').val();

Thursday, March 15, 2018

last execution time of procedure in sql server

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
where OBJECT_NAME(object_id, database_id) = 'ProcName'
ORDER BY [total_worker_time] DESC;

Wednesday, March 14, 2018

Fragmentation and Rebuild all index.

IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
DROP TABLE tempdb..#work_to_do

BEGIN TRY
--BEGIN TRAN

use [-----DBName---]

-- Ensure a USE statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @pagecount INT;
DECLARE @command NVARCHAR(4000);

DECLARE @page_count_minimum SMALLINT
SET @page_count_minimum = 50

DECLARE @fragmentation_minimum FLOAT
SET @fragmentation_minimum = 30.0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

SELECT object_id AS objectid ,
index_id AS indexid ,
partition_number AS partitionnum ,
avg_fragmentation_in_percent AS frag ,
page_count AS page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND index_id > 0
AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1
BEGIN
PRINT 'partitions CURSOR DELETED' ;
CLOSE partitions
DEALLOCATE partitions
END
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR LOCAL
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE ( 1 = 1 )
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

IF @@FETCH_STATUS < 0
BREAK;

SELECT @objectname = QUOTENAME(o.name) ,
@schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid
AND index_id = @indexid;

SELECT @partitioncount = COUNT(*)
FROM sys.partitions
WHERE object_id = @objectid
AND index_id = @indexid;

SET @command = N'ALTER INDEX ' + @indexname + N' ON '
+ @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1
SET @command = @command + N' PARTITION='
+ CAST(@partitionnum AS NVARCHAR(10));

EXEC (@command);
--print (@command); //uncomment for testing

PRINT N'Rebuilding index ' + @indexname + ' on table '
+ @objectname;
PRINT N' Fragmentation: ' + CAST(@frag AS VARCHAR(15));
PRINT N' Page Count: ' + CAST(@pagecount AS VARCHAR(15));
PRINT N' ';
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
--COMMIT TRAN

END TRY
BEGIN CATCH
--ROLLBACK TRAN
PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH

Tuesday, March 6, 2018

Adding the group id with records.

Adding the group id with records.

create table #test(

id int identity(1,1)
,grp int
)
insert into #test(grp) values(0) ,(0),(0), (0),(0),(0), (0),(0),(0) ,(0),(0),(0)

update #test set grp = ( (id-1)/3)+1-- 3 is number of records in gorup

select * from #test

Monday, March 5, 2018

Tables name and space used by them

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) DESC

Tuesday, November 14, 2017

YAHOO Finance SymbolSuggest

<!DOCTYPE html>
<html>
<head>
<title></title>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.0/themes/cupertino/jquery-ui.css"></script>
<script type="text/javascript">

$(document).ready(function () {

$("#stock").autocomplete({
source: function (request, response) {

// faking the presence of the YAHOO library bc the callback will only work with
// "callback=YAHOO.Finance.SymbolSuggest.ssCallback"
var YAHOO = window.YAHOO = { Finance: { SymbolSuggest: {} } };

YAHOO.Finance.SymbolSuggest.ssCallback = function (data) {
var mapped = $.map(data.ResultSet.Result, function (e, i) {
return {
label: e.symbol + ' (' + e.name + ')',
value: e.symbol
};
});
response(mapped);
};

var url = [
"http://d.yimg.com/autoc.finance.yahoo.com/autoc?",
"query=" + request.term,
"&callback=YAHOO.Finance.SymbolSuggest.ssCallback"];

$.getScript(url.join(""));
},
minLength: 2
});
});
</script>
<meta charset="utf-8" />
</head>
<body>
Stock Symbol
<input id="stock" type="text" placeholder="type to search stocks" />
</body>
</html>

Thursday, September 28, 2017

Get top X from each category SQL SERVER 2005 +

Get top 5 from each category

simple logic use Row_Number function with partition by the category for which you want to create group.
ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID )

Step 2: Now Row_Number will add numbers on each category, kept the result in temp table or CTE.

Step 3: Now use select statement to get records where rowNum greater than X.

;with Rohi as
(
select
TestID,CategoryID,
ROW_NUMBER() OVER (PARTITION BY CategoryID order by CategoryID ) AS 'RowNum'

from Test
)
select * from Rohi where RowNum <=5

Tuesday, May 23, 2017

All Dates or Number series between start and end position - SQL SERVER Months between two dates, Numbers series between to number

Example 1: Date Series
DECLARE @StartDate DATETIME ='20110501' ,
@EndDate DATETIME= '20110801'

SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

Example 1: Int Series
DECLARE @StartRange int ='2' ,
@EndRange int= '10'

SELECT x.number AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number between @StartRange and @EndRange

Sunday, April 23, 2017

symbol lookup

NOTE: It is not working in IE & edge.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" name="referrer" content="no-referrer" />
<meta name="viewport" content="width = device-width; initial-scale=1.0; maximum-scale=1.0; user-scalable=no;">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
<script type="text/javascript" src="http://yui.yahooapis.com/2.9.0/build/yahoo/yahoo-min.js"></script>

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<style>
#txtTicker-label {
display: block;
font-weight: bold;
margin-bottom: 1em;
}
#txtTicker-icon {
float: left;
height: 32px;
width: 32px;
}
#txtTicker-description {
margin: 0;
padding: 0;
}
.ui-draggable, .ui-droppable {
background-position: top;
}

.bold-text {
background-color:#3f7bf6;

}
.ttt{
background-color:#3f7bf6;
background: blue;
}

</style>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>
<body>
<h>Stock Quotes AutoSuggest </h>
</br></br>

<ul style="margin:0; padding:0; list-style:none; width:300px;">
<li>
<div>
<p style="color:blue; font-weight:bold;">IBP <span style="color:black; font-weight:normal; float:right;">Equity - NYSE</span></p>
<p style="text-transform:uppercase;">Installed Building Production....</p>

</div>
</li>
</ul>

<input style="width: 800px; height: 20px;" id="txtTicker" />

<script type="text/javascript">
var YAHOO = {
Finance: {
SymbolSuggest: {}
}
};
$(function () {

$("#txtTicker").autocomplete({
source: function (request, response) {
var query = request.term;
$.ajax({
type: "GET",
url: "http://d.yimg.com/autoc.finance.yahoo.com/autoc",
data: { query: query, region: 'US', lang: 'en-US' },
dataType: "jsonp",
jsonp: "callback",
jsonpCallback: "YAHOO.Finance.SymbolSuggest.ssCallback",
});
// call back function
YAHOO.Finance.SymbolSuggest.ssCallback = function (data) {
var suggestions = [];
//alert(JSON.stringify(data.ResultSet.Result));
// debugger;
$.each(data.ResultSet.Result, function (i, val) {
suggestions.push({
value: val.symbol//, + "-" + val.name + "(" + val.exchDisp + ")"
, label: val.name
, desc: val.exch +'-'+val.exchDisp
});
});

response(suggestions);
}
},
minLength: 1,
focus: function (event, ui) {
$("#txtTicker").val(ui.item.value);
return false;
},
select: function (event, ui) {

$("#txtTicker").val(ui.item.value);

return false;
},
highlightClass: "bold-text"
})
.autocomplete("instance")._renderItem = function (ul, item) {
// alert('abc');
// debugger;
return $("<li>")
.append("<div>"
+ "<p style='color:blue; font-weight:bold;'>"
+ item.value
+ "<span style='color:black; font-weight:normal; float:right;'>"
+ item.desc
+ "</span></p>"
+ " <p style='text-transform:uppercase;'>"
+ item.label
+ "</p>"
// + item.value + "<br>" + item.label + +item.desc + "</div>")
+ "</div>")

.appendTo( ul );
};
});
</script>

</body>
</html>

Friday, April 7, 2017

Executed Procedure name

select
db_name(st.dbid) as database_name,
object_name(st.objectid) as name
-- ,p.size_in_bytes / 1024 as size_in_kb,
-- p.usecounts,
-- st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('DBName_Mahesh')
and object_name(st.objectid) = 'Chat_GetUserId'
order by p.usecounts desc

Tuesday, February 21, 2017

SQL Jobs Status

SQL Jobs Status

select
sj.name,
sjs.last_run_outcome, -- 0 = fail, 1 = succeed, 2 = cancel
sjs.last_outcome_message,
case when sjs.last_run_date > 0
then datetimefromparts(sjs.last_run_date/10000, sjs.last_run_date/100%100, sjs.last_run_date%100, sjs.last_run_time/10000, sjs.last_run_time/100%100, sjs.last_run_time%100, 0)
end as last_run_datetime
from msdb.dbo.sysjobservers sjs
left outer join msdb.dbo.sysjobs sj on (sj.job_id = sjs.job_id)
order by sj.name

Tuesday, October 18, 2016

QL Server Agent Job Schedules

100

101

102

103

104

105

SELECT  [JobName] = [jobs].[name]

  ,[Category] = [categories].[name]

  ,[Owner] = SUSER_SNAME([jobs].[owner_sid])

  ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END

  ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END

  ,[Description] = [jobs].[description]

  ,[Occurs] = 

    CASE [schedule].[freq_type]

     WHEN   1 THEN 'Once'

     WHEN   4 THEN 'Daily'

     WHEN   8 THEN 'Weekly'

     WHEN  16 THEN 'Monthly'

     WHEN  32 THEN 'Monthly relative'

     WHEN  64 THEN 'When SQL Server Agent starts'

     WHEN 128 THEN 'Start whenever the CPU(s) become idle' 

     ELSE ''

    END

  ,[Occurs_detail] = 

    CASE [schedule].[freq_type]

     WHEN   1 THEN 'O'

     WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'

     WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 

      LEFT(

       CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 

       CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 

       LEN(

        CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 

        CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 

       ) - 1

      )

     WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'

     WHEN  32 THEN 'The ' + 

       CASE [schedule].[freq_relative_interval]

        WHEN  1 THEN 'First'

        WHEN  2 THEN 'Second'

        WHEN  4 THEN 'Third'

        WHEN  8 THEN 'Fourth'

        WHEN 16 THEN 'Last' 

       END +

       CASE [schedule].[freq_interval]

        WHEN  1 THEN ' Sunday'

        WHEN  2 THEN ' Monday'

        WHEN  3 THEN ' Tuesday'

        WHEN  4 THEN ' Wednesday'

        WHEN  5 THEN ' Thursday'

        WHEN  6 THEN ' Friday'

        WHEN  7 THEN ' Saturday'

        WHEN  8 THEN ' Day'

        WHEN  9 THEN ' Weekday'

        WHEN 10 THEN ' Weekend Day' 

       END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 

     ELSE ''

    END

  ,[Frequency] = 

    CASE [schedule].[freq_subday_type]

     WHEN 1 THEN 'Occurs once at ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')

     WHEN 2 THEN 'Occurs every ' + 

        CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

     WHEN 4 THEN 'Occurs every ' + 

        CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

     WHEN 8 THEN 'Occurs every ' + 

        CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')

     ELSE ''

    END

  ,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])

  ,[Next_Run_Date] = 

    CASE [jobschedule].[next_run_date]

     WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')

     ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 

       STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))

    END

FROM  [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 

   LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 

     ON [jobs].[job_id] = [jobschedule].[job_id] 

   LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 

     ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 

   INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 

     ON [jobs].[category_id] = [categories].[category_id] 

   LEFT OUTER JOIN 

     ( SELECT  [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 

                (([run_duration] % 10000) / 100 * 60) + 

                 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])

      FROM  [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)

      WHERE  [step_id] = 0 

      GROUP BY [job_id]

      ) AS [jobhistory] 

     ON [jobhistory].[job_id] = [jobs].[job_id];

GO