Useful scripts for creating date ranges in SQL Server

When you want to find out the number of work days in a date range

     declare @WorkDays int  
     select @WorkDays = (  
                 (datediff(dd, @StartDate, @EndDate) + 1)  
                -(datediff(wk, @StartDate, @EndDate) * 2)  
                -(case when DATENAME(dw, @StartDate) = 'Sunday' then 1 else 0 end)  
                -(case when DATENAME(dw, @EndDate) = 'Saturday' then 1 else 0 end)  
               )  

When your users want a date ranges

 set nocount on  
 declare @Now datetime  
 declare @Today datetime  
 set @Now = getdate()  
 set @Today = DATEADD(day, DATEDIFF(day, 0, @Now), 0)  
 declare @day int  
 declare @week int  
 declare @dayofweek int  
 declare @dayofyear int  
 set @day = datepart(day, @Today)  
 set @week = datepart(week, @Today)  
 set @dayofweek = datepart(weekday, @Today)  
 set @dayofyear = datepart(dayofyear, @Today)  
 declare @weekbegin datetime  
 set @weekbegin = DATEADD(day, (@dayofweek-1)* -1, @Today)  
 declare @lastweekbegin datetime  
 set @lastweekbegin = DATEADD(week, -1, @weekbegin)  
 declare @lastweekend datetime  
 set @lastweekend = DATEADD(day, 6, @lastweekbegin)  
 declare @monthbegin datetime  
 set @monthbegin = DATEADD(day, (@day-1)* -1, @Today)  
 declare @lastmonthbegin datetime  
 set @lastmonthbegin = DATEADD(month, -1, @monthbegin)  
 declare @lastmonthend datetime  
 set @lastmonthend = DATEADD(day, -1, @monthbegin)  
 declare @yearbegin datetime  
 set @yearbegin = DATEADD(day, (@dayofyear-1)* -1, @Today)  
 declare @lastyearbegin datetime  
 set @lastyearbegin = DATEADD(year, -1, @yearbegin)  
 declare @lastyearend datetime  
 set @lastyearend = DATEADD(day, -1, @yearbegin)  
 create table #daterange  
 (  
   label varchar(20),  
   startdate datetime,  
   enddate datetime  
 )  
 insert into #daterange(label, startdate, enddate) values ('Current Week', @weekbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Week', @lastweekbegin, @lastweekend)  
 insert into #daterange(label, startdate, enddate) values ('Current Month', @monthbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Month', @lastmonthbegin, @lastmonthend)  
 insert into #daterange(label, startdate, enddate) values ('Current Year', @yearbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Year', @lastyearbegin, @lastyearend)  
 update #daterange set enddate = DATEADD(day, 1, enddate)  
 select * from #daterange  
 drop table #daterange  

Getting the most of your Gear VR

The Samsung Gear VR is awesome.  The Oculus store has lots of cool apps in it.  But it does limit you to a closed system.

You can get VR content from other sources like Google / Bittorrent etc.

Here is how you can do it.

The VR player I think its really good is

VaR VR Video Player

https://play.google.com/store/apps/details?id=com.abg.VRVideoPlayer&hl=en

The reason I like it is because it allows you to select a video mode based on different file formats and your files could be stored anywhere in the phone including SD card.

But the first problem most people face it when they plug their phone in their Gear VR, the Oculus app will start up and excludes the app currently running.

The way to solve this is to use the Package Disabler Pro

https://play.google.com/store/apps/details?id=com.ospolice.packagedisablerpro&hl=en

Just install the app and disable the Gear VR Service.  Next time you plug in the phone, the Oculus app won't take over.  If you want to use the Oculus app again, just uncheck the Gear VR Service in the app.




Yes, it cost 99 cents and yes you can 'clip' your phone in the Gear VR losely without plugging it in.  But spending 99 cents might save your 800 dollars phone from falling to the floor and the damage would cost you a lot more.  Hence I would recommend getting this app.

Ok this should free you exploring the world of VR outside the confine of the Samsung store.

VR is great and I look forward to see more open license contents.

How to create a scrollable table with header

See below example.  The tricks are 

Use jQuery to unhide the right column in the table when scroll bar is not visible.

Scrollbar is only visible if the row height * number of rows > table height

   table { 
     table-layout: fixed; 
   } 

is important to control the layout

 @model C2.Models.dtSalary  
 <!doctype html>  
 @{  
   Layout = null;  
 }  
 <script src="~/Scripts/jquery-1.10.2.js"></script>  
 <STYLE type="text/css">  
   table {  
     table-layout: fixed;  
   }  
   .TableDiv {  
     overflow: auto;  
     height: 400px;  
     width: 320px;  
   }  
   .TableHeaderDiv {  
     width: 320px;  
   }  
   .TableFormat {  
     font-family: Arial;  
     font-size: 0.8em;  
     text-align: center;  
     border-collapse: collapse;  
     width: 100%;  
   }  
   .MediumColumn {  
     width: 100px;  
   }  
   .LongColumn {  
     width: 200px;  
   }  
   .TdHeight {  
     height: 20px;  
   }  
   th {  
     font-weight: 600;  
   }  
   .ColumnNoDisplay {  
     width: 17px;  
     display: none;  
   }  
 </STYLE>  
 <div class="TableHeaderDiv">  
   <table border="1" cellpadding="1" cellspacing="0" class="TableFormat">  
     <colgroup>  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col width="17px" />  
     </colgroup>  
     <tr>  
       <th>  
         IP Address  
       </th>  
       <th>  
         Country Name  
       </th>  
       <th>  
         City  
       </th>  
       <th></th>  
     </tr>  
   </table>  
 </div>  
 <div class="TableDiv">  
   <table border="1" cellpadding="1" cellspacing="0" class="TableFormat">  
     <colgroup>  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="MediumColumn" />  
       <col class="ColumnNoDisplay" />  
     </colgroup>  
     @foreach (var item in Model.salaryData)  
     {  
       <tr>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.IPAddress)  
         </td>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.CountryName)  
         </td>  
         <td class="TableCell">  
           @Html.DisplayFor(modelItem => item.City)  
         </td>  
         <td class="ColumnNoDisplay">&nbsp</td>  
       </tr>  
     }  
   </table>  
 </div>  
 <script>  
   var divHeight = 400  
   var rowHeight = 20  
   var rowcount = @Model.salaryData.Count;  
   if (rowcount * rowHeight > divHeight)  
   {  
   }  
   else  
   {  
     $(".ColumnNoDisplay").css('display','table-column');  
   }  
 </script>  

IsHex function for SQL Server

 IF EXISTS (  
   SELECT * FROM sysobjects WHERE id = object_id(N'fnIsHex')   
   AND xtype IN (N'FN', N'IF', N'TF')  
 )  
   DROP FUNCTION dbo.fnIsHex  
 GO  
 CREATE FUNCTION dbo.fnIsHex(@s VARCHAR(50) )   
 --Returns true if the string is a valid hexadecimalal number.   
 RETURNS bit  
 AS  
 BEGIN  
 /*  
 Description: Determine if a string represent an Hexadecimal value  
 Author:   Leo Fong  
 Created Date:06 Jan 2015  
 Usage: select dbo.fnIsHex('EX0429168AE12A80')  
     select dbo.fnIsHex('E0429168AE12A80')  
 */  
 DECLARE @i int, @temp char(1), @bool bit  
 SET @i=1  
 SET @bool=0  
 WHILE (@i<=LEN(@s))  
 BEGIN  
   SELECT @temp=SUBSTRING(@s,@i,1)  
   if ((@temp<='f') AND (@temp>='a')) OR ((@temp<='F') AND (@temp>='A')) OR ((@temp<='9') AND (@temp>='0'))   
     BEGIN  
       SET @bool=1  
     END  
   ELSE  
     BEGIN  
       SET @bool=0  
       RETURN @bool  
     END  
   SELECT @i=@i+1  
 END  
 RETURN @bool  
 END  

BizTalk consume web service using dynamic address

Create a dynamic port
Set URL of the port using expression

/* set port address */
portDynamicSend(Microsoft.XLANGs.BaseTypes.Address) = msgDynamicAddress.DestinationURL;
/* set port type */
portDynamicSend(Microsoft.XLANGs.BaseTypes.TransportType) = "WCF-BasicHttp";

Modify the message using expression

msgSend2 = msgSend;
/* set web service operation name */
msgSend2(BTS.Operation)="Propose";
msgSend2(WCF.PropagateFaultMessage)= true;


/* if security is required, add the below lines */
msgSend2(WCF.SecurityMode)="Transport";
msgSend2(WCF.TransportClientCredentialType)="None";

How to add legend to Chart.js graph

 @model C2.Models.ChartModel  
 @{  
   ViewBag.Title = "Chart";  
 }  
 <h2>Chart</h2>  
 <div>  
   <h3>Data</h3>  
   @*@Html.Raw(Model.ChartHtmlDisplay)*@  
 </div>  
 <div style="width:50%">  
   <div>  
     <canvas id="canvas" height="450" width="600"></canvas>  
   </div>  
   <div id="divLegend">  
   </div>  
 </div>  
 <script>  
   var lineChartData = {  
   @Html.Raw(Model.ChartHtml)  
   };  
   window.onload = function(){  
     var options = {  
       legendTemplate : '<ul>'  
               +'<% for (var i=0; i<datasets.length; i++) { %>'  
                +'<li>'  
                +'<span style=\"color:<%= datasets[i].pointColor %>;font-family:Arial;font-size:1em\">'  
                +'<% if (datasets[i].label) { %><%= datasets[i].label %><% } %>'  
                +'</span>'  
               +'</li>'  
              +'<% } %>'  
             +'</ul>'  
     }  
     var ctx = document.getElementById("canvas").getContext("2d");  
     //don't forget to pass options in when creating new Chart  
     var lineChart = new Chart(ctx).Line(lineChartData, options);  
     //then you just need to generate the legend  
     var legend = lineChart.generateLegend();  
     //and append it to your page somewhere  
     $('#divLegend').html(legend);  
     var ctx = document.getElementById("canvas").getContext("2d");  
     window.myLine = new Chart(ctx).Line(lineChartData, {  
          responsive: true  
     });  
   }  
 </script>  

An easy way to generating year, month list with SQL

DECLARE @startmonth      int = 1
DECLARE @endmonth        int = 12
DECLARE @startyear       int = 2012
DECLARE @endyear         int = 2015
;
WITH monthlist AS
(
    SELECT @startmonth AS m
    UNION ALL
    SELECT m+1 FROM monthlist WHERE m+1<=@endmonth
)
,yearlist AS
(
    SELECT @startyear AS y
    UNION ALL
    SELECT y+1 FROM yearlist WHERE y+1<=@endyear
)
        SELECT y,
               m
          FROM yearlist
    CROSS JOIN monthlist
      ORDER BY y,
               m
        OPTION (maxrecursion 1000)