Monday, August 3, 2015

Question of the Month (July)

Suppose we have a table emp_plan  which contains 4 columns "Empid" (employeeid), Planid (Projectid), Startdate(Allocation Start date) and Enddate (Allocation Enddate). Its structure is given below:-


Create table emp_plan (empid nvarchar(200),planid nvarchar(20),startdate datetime, enddate datetime)

Also below is the script to enter sample data into the table:-

insert into emp_plan(empid,planid,startdate,enddate)
select '001','planA','2015-05-15','2015-05-30'
union all
select '001','planA','2015-05-31','2015-06-14'
union all
select '001','planA','2015-06-15','2015-06-30'
union all
select '001','planA','2015-07-10','2015-07-20'
union all
select '001','planA','2015-07-21','2015-07-30'
union all
select '001','planB','2015-06-14','2015-06-30'
union all
select '001','planB','2015-07-10','2015-07-20'
union all
select '001','planB','2015-07-21','2015-07-30'
union all
select '002','planA','2015-06-14','2015-06-30'
union all
select '002','planB','2015-07-10','2015-07-20'
union all
select '002','planB','2015-07-21','2015-07-30'

So the data contain in the table is given blow:-



Now if a employee has the same project(planid) with no gap in the next allocation segment( allocation segment means from allocation startdate to allocation enddate), then we need to join the continuous allocation segment. For example if you see the data in the table, in the first 3 rows, employee having empid 001 is allocated to the Same project (PlanA) and all allocation segments are continuous (from 2015-05-15 to 2015-06-30)  as shown below:-

2015-05-15 to 2015-05-30  (1st row)
2015-05-31 to 2015-06-14  (2nd row)
2015-06-15 to 2015-06-30  (3rd row)

But the 4th row contains allocation segment which is not not continuous with the above 3 allocation segments as there is a gap between the 3rd row enddate and 4th row startdate and again 4th row is in continuation with 5th row with same employeeid 001 and project id (Plan1) as shown below:-

2015-07-10  to 2015-07-20 (4th Row)

2015-07-21 to 2015-07-30 (5th Row)

So expect result set should be


Empid     Planid               Startdate                           Enddate
001          planA     2015-05-15 00:00:00.000     2015-06-30 00:00:00.000 

(1st to 3rd Row)
001          planA     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000        (4th to 5th Row)

So according to the above rule, expected result from the sample data inserted into the table (as shown in the above picture), should be same as given below:-

Expected Result

Empid     Planid               Startdate                           Enddate
001          planA     2015-05-15 00:00:00.000     2015-06-30 00:00:00.000
001          planA     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
001          planB     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
001          planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
002          planA     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
002          planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000

So what should be the query/stored procedure to get the above result?

Saturday, July 11, 2015

Do you know about Whatsapp web?


Whatsapp claims that they currently have 500 million users worldwide. That is pretty impressive looking at the good amount of distinctively decent competitors they have within the space of Social Networking communication. ViberLineGoogle Hangout,Facebook Messenger & Hike (Commonly in India). While all these products are growing inch by inch and including new set of features into their product, whatsapp has never really seen any major makeover or a big release since Facebook took it over. Changing the paradigm, Whatsapp’s Web version has been introduced recently. Let us review and assess do we have got a Cherry with an already delicious pineapple cake or a bitter cup?
To begin using Whatsapp web, you would have to follow few steps
1. Update your whatsapp application to the latest version:  Right now, the web version is only compatible with Google Chrome when paired with the Android, Windows Phone, or Blackberry version of the mobile app. iOS users sadly have to wait for some more time till they see the sunrise.
2. Pairing: The process of bringing whatsapp to the Desktop shall begin by pairing the Web version with the Whatsapp Mobile Application.  To pair your mobile application, navigate to Web version of Whatsapp by typing in http://web.whatsapp.com on your browser’s Address bar. You shall view a similar interface as below
Whatsapp web screenshot


Now, open the Whatsapp application on your Android mobile and open Menu. You shall be prompted to scan the QR code present on the Chrome window (As depicted in figure above).
Note: QR code(abbreviated from Quick Response Code) is a complex matrix of coded information represented in a square shape. If you want to know more about QR code, head here.
Point your mobile so that QR Code can be seen within your mobile and fits within Scan area. The Mobile shall automatically pair the web version of whatsapp to load the similar view on web view.
Once you are in, you can search for your friends in the similar fashion as you’d do from your mobile. You can send messages, share images. You also have the option to record your voice using the Microphone button, that is placed exactly where you’d observe on your mobile version (Adjacent to message type-in area).
The only thing that I really miss is the ability of Web version to forward the received messages. Hopefully, whatsapp should be able to bring this feature soon. Till then, you can still enjoy the luxury of keep plugged on your desktop, without your boss knowing that you are still “Whatsapp Phantom“.

Friday, July 10, 2015

Script to find complete months between two given dates

Below script will help in finding the complete months within the 2 given dates. In the below script we takes below dates as example

Startdate =2015-07-05
Enddate =2016-01-06

/**********************Script Start**************************/

IF (object_id('tempdb..#month') is not null)
DROP TABLE #month

CREATE TABLE #month (id int identity(1,1),MonthStartDate DATETIME,MonthEndDate DATETIME)

DECLARE @startdate AS DATETIME
DECLARE @enddate AS DATETIME

DECLARE @monthstartdate AS DATETIME
DECLARE @monthenddate AS DATETIME

SET @startdate='2015-07-05'
SET @enddate='2016-01-06'

SELECT @monthstartdate= dateadd(mm,1,dateadd(DD,-day(@startdate)+1,@startdate))

SELECT @monthenddate= dateadd(dd,-1,dateadd(mm,1,@monthstartdate))

IF @enddate<@monthenddate
   BEGIN

    INSERT INTO #month
    SELECT null,null
   END
ELSE IF @startdate=@monthstartdate and @enddate=@monthenddate
  BEGIN

    INSERT INTO #month
    SELECT @monthstartdate,@monthenddate
  END
ELSE IF (@enddate>@monthenddate)
   BEGIN

    IF @startdate=@monthstartdate
    BEGIN
      INSERT INTO #month
      SELECT @monthstartdate,@monthenddate
    END

  WHILE (@enddate>=@monthenddate)

    BEGIN

   INSERT INTO #month
   SELECT @monthstartdate,@monthenddate

   SELECT @monthstartdate=dateadd(MM,1,@monthstartdate)
   SELECT @monthenddate=dateadd(day,-1,dateadd(MM,1,@monthstartdate))
  

    END

END

SELECT cast(MonthStartDate as DATE) as MonthStart,cast(MonthEndDate as DATE) as MonthEnd FROM #month

/**********************Script End**************************/

This script will gives the following result:-

MonthStart MonthEnd
2015-08-01        2015-08-31
2015-09-01        2015-09-30
2015-10-01        2015-10-31
2015-11-01        2015-11-30
2015-12-01        2015-12-31

Script to find the complete weeks within two given dates

Sometimes we need to find out the complete weeks within two given dates. Below script will help in finding the complete weeks within 2 given dates. In this script, I have used below dates as example 

Startdate=2015-04-12
Enddate=2015-06-10 

SET DATEFORMAT YMD
if (object_id('tempdb..#weekdays') is not null)
drop table #weekdays

Declare @StartDate as datetime
Declare @EndDate as datetime
Declare @Initialweekstart as datetime
Declare @weekstart as datetime
Declare @weekend as datetime
declare @day as int

set @StartDate='2015-04-12'
set @EndDate='2015-06-10'

Create table #weekdays(weekstart datetime, weekend datetime)

select @Initialweekstart=dateadd(wk, datediff(wk, 0, @StartDate), 0)


if @Initialweekstart<@StartDate
  begin
   set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@Initialweekstart),0))
   print  @weekstart
   set @weekend=DATEADD(dd,6,@weekstart)
 end
else
begin
set @weekstart=@Initialweekstart
set @weekend=DATEADD(dd,6,@weekstart)
end
while @weekend<=@EndDate
begin
  insert into #weekdays(weekstart,weekend)
  values (@weekstart,@weekend)
  set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@weekstart),0))
   print  @weekstart
   set @weekend=DATEADD(dd,6,@weekstart)
end
select @day= datepart(dw,@EndDate)
if @day=6 or @day=7
begin
insert into #weekdays(weekstart,weekend)
values (@weekstart,@weekend)
end
select CAST(weekstart AS DATE) as WeekStart, CAST(weekend AS DATE) as WeekEnd from #weekdays

This script will gives the following result:-

WeekStart        WeekEnd
2015-04-13      2015-04-19
2015-04-20      2015-04-26
2015-04-27      2015-05-03
2015-05-04      2015-05-10
2015-05-11      2015-05-17
2015-05-18      2015-05-24
2015-05-25      2015-05-31
2015-06-01      2015-06-07