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>
<!--    <style>
        ul.ui-autocomplete.ui-menu {
            width: 400px;
        }

            /*
    targets the first result's <a> element,
    remove the a at the end to target the li itself
*/

            ul.ui-autocomplete.ui-menu li:first-child a {
                color: green;
            }

        .ui-autocomplete {
            width: 424px;
            margin: 0;
            padding: 0 0 14px 0;
        }

        .ui-autocomplete-category {
            width: 100px;
            position: absolute;
            padding: 0 20px;
            margin: 20px 0 0 0;
        }

        .ui-menu-item {
            padding-left: 140px;
        }

        .ui-first {
            padding-top: 20px;
        }
    </style>-->
    <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