Cisco VPN not Working on Windows 10

Error:
Cisco VPN is not working (cannot login and connect to VPN connection).

Solution:
1. Right-click on the VPN client, and Run as Administrator.
2. Open regedit and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA and rename:

• x86 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter" to "Cisco Systems VPN Adapter"
• x64 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows" to "Cisco Systems VPN Adapter for 64-bit Windows"

Source partially from: IT that should just work

Read More

Find Maximum Value from Multiple Columns

A colleague asked about how to get maximum value from multiple columns. After searching it online, I found a pretty good way to get it, and thought it would be good to share here.

For example, I have a table with MemberID and 3 date fields. I want to get the last date from the 3 date fields for each MemberID.

The expected end result look like:

Creating the sample data:

create table #member ( MemberID nvarchar(100), modifiedon datetime, deletedon datetime, addedon datetime )
insert into #member values ('M1', GETDATE(), GETDATE(), GETDATE())
insert into #member values ('M2', GETDATE()-1, GETDATE()-2, GETDATE()-3)
insert into #member values ('M3', GETDATE()-3, GETDATE()-2, GETDATE()-1)

Query to get the max for multiple columns:

SELECT 
 MemberID, 
 LastUpdateDate =
 ( SELECT MAX(LastUpdateDate)
  FROM (VALUES (modifiedon),(deletedon),(addedon)) AS UpdateDate(LastUpdateDate)
 )
FROM #member

Read More

Calculating Customer's Latency

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.

To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:

--Create temp table
CREATE TABLE #Transact
(
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE
)

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:

--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
INTO #1
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
FROM ( SELECT a.MemberID,
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
  INNER JOIN #1 b
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!

Read More