Tuesday, May 22, 2018

Wouldn't it be great?

Wouldn't it be great to be hugged by a large foreign woman? You can't understand what shes says but she greets you with a hug. At first you are somewhat withdrawn.Tentative at best. But then over the years you squeeze her like she is your best friend. You embraced her like a child with a teddy bear. You are held for just a moment but will remember it for a life time. Wouldn't it be great?😔

Friday, May 18, 2018

Spotify: 3 month Trial for 0.99

Spotify.com is offering an Introductory Trial PromotionGet 3-Months of Spotify Premiumfor $0.99. If you have subscribed to the Premium or Unlimited service or have taken a 30-day free trial offer or 60-day free trial offer previously, you are ineligible for this introductory trial offer. This is a trial and after your first 3 months, you will be charged full price unless you cancel.


Sunday, January 7, 2018

SQL: Finding an assistant using a recursive CTE

Was looking for SQL that would allow me to find an Employee's assistant. It requires a somewhat recursive function:

create table EMPLOYEES
    (EmpID    int         unique Not null,
     Name    varchar(10),
     AssistantID int,
     Primary key(EmpID));

insert into EMPLOYEES values (1,'Bob', NULL);
insert into EMPLOYEES values (2,'Susan', 3);
insert into EMPLOYEES values (3,'John', 4);
insert into EMPLOYEES values (4,'Mary', 6);
insert into EMPLOYEES values (5,'Bill', NULL);
insert into EMPLOYEES values (6,'Fred', NULL);
insert into EMPLOYEES values (7,'Anderson', 1);
insert into EMPLOYEES values (8,'Gill', 9);
--insert into EMPLOYEES values (9,'Jerry', 8);

I wanted to for example find Mary's assistant (e:g Fred). There's a recursive case where to find Susan's assistant we go: Susan -> John -> Mary -> Fred. I also want Bob's assistant which is himself.
Here's one solution:

WITH RCTE AS
(
    SELECT * , EmpID AS TopEmp
    FROM EMPLOYEES c
 
    UNION ALL
    SELECT c.* , r.TopEmp
    FROM dbo.EMPLOYEES c
     INNER JOIN RCTE r ON c.EmpID = r.AssistantID
)
select EmployeeName,Assistant from
(
SELECT
  e.EmpID AS EmployeeId,
  e.Name AS EmployeeName,
  r.Name AS Assistant,
  r.EmpID AS AssistantId
FROM RCTE r
inner join EMPLOYEES e ON r.TopEmp = e.EmpID
where r.AssistantID is null
) Records

Result:

| EmployeeName | Assistant |
|--------------|-----------|
|          Bob |       Bob |
|         Bill |      Bill |
|         Fred |      Fred |
|     Anderson |       Bob |
|         Mary |      Fred |
|         John |      Fred |
|        Susan |      Fred |