CareerCup

Select COUNT(decision) from
(Select "Name", decision, date from "Test") as A
 INNER JOIN
(SELECT "Name",MAX(date) as maxdate
  FROM "Test"
  group by "Name") as B
ON ( A."Name" = B."Name" and A.date = B.maxdate)
  where decision = true;

SELECT NAME,DESCISION,DATE FROM RSVP
INNER JOIN
(SELECT MAX(DATE) AS LATESTDATE,NAME FROM RSVP GROUP BY NAME
)SUB
ON RSVP.DATE=SUB.LATESTDATE AND RSVP.NAME=SUB.NAME.

Select COUNT(decision) from
(Select "Name", decision, date from "Test") as A
 INNER JOIN
(SELECT "Name",MAX(date) as maxdate
  FROM "Test"
  group by "Name") as B
ON ( A."Name" = B."Name" and A.date = B.maxdate)
  where decision = true;

select r.name from rsvp r, (select name, max(date) d from rsvp group by name) t where r.name=t.name and r.date=t.d and r.descision=1;

select count(*) from rsvp as r1 where r1.name in
(select r2.name from rsvp as r2 where r2.name=r1.name and r1.decision ='y' group by r2.name having r1.date=max(r2.date));

select count(*) from rsvp as r1 where r1.name in
(select r2.name from rsvp as r2 where r2.name=r1.name and r1.decision ='y' group by r2.name having r1.date=max(r2.date));

; with c as
(
select Name,
Decision,
CAST(Date AS DATE) date
,rnk = row_number() over (partition by name order by CAST(Date AS DATE) desc)
from #T
)select * from c where Decision ='y' and rnk=1

; with c as
(
 select Name,
Decision,
CAST(Date AS DATE) date
,rnk = row_number() over (partition by name order by CAST(Date AS DATE) desc)
 from #T
)select * from c where Decision ='y' and rnk=1

create table #TempTab (Name varchar(100), Decision bit, PollDate Date)

insert into #TempTab VALUES('John', 1, '1 Jan 2016')
insert into #TempTab VALUES('John', 0, '2 Jan 2016')
insert into #TempTab VALUES('Linda', 1, '1 Jan 2016')
insert into #TempTab VALUES('Mark', 1, '5 Jan 2016')
insert into #TempTab VALUES('Rob', 0, '5 Jan 2016')

;WITH T AS
(select *
	, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY PollDate desc) r
from #TempTab)
SELECT Name 
FROM T 
WHERE r = 1 
	and Decision = 1

CREATE table RSVP (Name Varchar(5), Decision Varchar(1), d Date); 

insert into RSVP values
('Jon', 'Y', '2016-1-1'), 
('Jon', 'N', '2016-1-2'), 
('Linda', 'Y', '2016-1-1'), 
('Mark', 'Y', '2016-1-5'), 
('Rob', 'N', '2016-1-5')

SELECT count(*) FROM (SELECT * FROM RSVP r1 WHERE d>=(SELECT Max(d) FROM RSVP r2 WHERE r1.Name=r2.Name)) t WHERE t.Decision='Y'

CREATE table RSVP (Name Varchar(5), Decision Varchar(1), d Date); 

insert into RSVP values
('Jon', 'Y', '2016-1-1'), 
('Jon', 'N', '2016-1-2'), 
('Linda', 'Y', '2016-1-1'), 
('Mark', 'Y', '2016-1-5'), 
('Rob', 'N', '2016-1-5')

SELECT count(*) FROM (SELECT * FROM RSVP r1 WHERE d>=(SELECT Max(d) FROM RSVP r2 WHERE r1.Name=r2.Name)) t WHERE t.Decision='Y'

In Hive :-
select count(*) from (select * from (select *,row_number() over (partition by name order by date desc )t from rspv)tab where t = 1) where decision='Y';

In Hive :-
select count(*) from (select * from (select *,row_number() over (partition by name order by date desc )t from rspv)tab where t = 1) where decision='Y';

Insert data followed by @agarwal.uw

select Count(*) from (select * from TempTab where Decision = 1) y, (select * from TempTab where Decision = 0) n where NOT (y.name = n.name AND y.PollDate<n.PollDate);

This will also prevent if Date of decision Y is less than date of decision N.

select y.name from (select * from TempTab where Decision = 1) y, (select * from TempTab where Decision = 0) n where NOT (y.name = n.name AND y.PollDate<n.PollDate);

select count(*) from rsvp r1 where decision = 'Y' and dt = (select max(dt) from rsvp r2 where r1.name = r2.name);

--First Approach

;WITH CTE AS (
SELECT Name, MAX(DDate ) DDate
FROM RSVP
GROUP BY Name)

SELECT COUNT(*)
FROM RSVP Inner Join CTE ON RSVP.DDate = CTE.DDate 
AND RSVP.Name = CTE.Name
WHERE RSVP.Decision = 'Y'

--Second Approach
;With CTE1 AS (
SELECT Name, 
ROW_NUMBER() OVER (Partition by Name Order by DDate DESC) AS ROWE
,Decision,DDate
FROM RSVP)
SELECT *
FROM CTE1
WHERE Rowe = 1
AND Decision = 'Y'

select count(Name) from RSVP where Decision = 'Y' group by Name Having Max(Date)

select count(*) from (select Name,decision,polldate from RSVP group by Name Having Max(PollDate)) a where decision = 'Y';

This will even handle if some one has said 'Y' before and then 'N'

select count(name) from (select name,decision,max(Dt) from rsvp where decision = "Y" group by Dt)a;

select count(0) from (
select Row_Number() over (partition by Name order by date desc) as RowNo,*
from rsvp
) T
where RowNo=1 and Descision='Y'

select count(0) from (
select Row_Number() over (partition by Name order by date desc) as RowNo,*
from rsvp
) T
where RowNo=1 and Descision='Y'

select count(*) from (select Name,decision,polldate from RSVP group by Name Having Max(PollDate)) a where decision = 'Y';

select count(*) from (
select name, max(date) as date from RSVP
INTERSECT
select name, date from RSVP where decision = 'Y' )

select count(*) from (
select name, max(date) as date from rsvp group by name
intersect
select name, date from rsvp group by name )

--Simple answer
create table #tblParty (name varchar(100),decision char(1), Actiondate date)
insert into #tblParty values('Jon','Y','1 jan 2016')
insert into #tblParty values('Jon','N','2 jan 2016')
insert into #tblParty values('Linda','Y','1 jan 2016')
insert into #tblParty values('Mark','Y','1 jan 2016')
insert into #tblParty values('Rob','N','1 jan 2016')



SELECT * FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ACTIONDATE DESC) AS ROW_NUM,NAME,DECISION,ACTIONDATE FROM #tblparty
)T WHERE ROW_NUM=1 AND T.DECISION='Y'

select Name, Decision, Date from
(
select Name, Decision, Date, rank() over(partition by Name order by Date desc) as Ranking from rsvp
where Decision = 'Y'
)a
where a.Ranking = 1

select count(1) from
(
select name,decision,row_number() over(parition by name order by date desc) rn from rsvp)
where rn=1
and decision='Y'

There are 2 ways to solve the query:

--Using join on table with latest_response
WITH
latest_response AS
(
SELECT
name,
MAX(response_date) final_response_date
FROM
rsvp
GROUP BY
name
)
SELECT
COUNT(a.name)
FROM
rsvp a,
latest_response b
WHERE
a.response_date=b.final_response_date
AND a.name=b.name
AND decision='Y';


--Without join using row_number to get latest_response
WITH ordered_response AS
(select *
, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY response_date desc) r
from rsvp)
SELECT count(Name)
FROM ordered_response
WHERE r = 1
and Decision = 'Y';

select x.name, x.decision from rsvp x where x.decision ='Y' and not exists ( select null from rsvp y
where x.name = y.name and y.decision = 'N' )

This is the perfect answer
select name, start_dt as start_dt, decision from (
select name, start_dt, decision, rank() over (partition by name order by start_dt desc) from rsvp order by name,rank
) as a where rank=1 and decision='Y';



Table Details:
create table rsvp (
name varchar(30),
decision char(1),
start_dt date);

insert into rsvp values ('Jon','Y', '2016-01-01');
insert into rsvp values ('Jon', 'N', '2016-01-02');
insert into rsvp values ('Linda', 'Y', '2016-01-01');
insert into rsvp values ('Mark', 'Y', '2016-05-01');
insert into rsvp values ('Rob', 'N', '2016-01-05');
insert into rsvp values ('jack', 'N', '2016-01-06');
insert into rsvp values ('Jon','Y','2016-01-03');
insert into rsvp values ('Keith','Y','2016-01-01');
insert into rsvp values ('Keith','N','2016-01-02');
insert into rsvp values ('Rob', 'N', '2016-01-06');

with cte as (
select 
    rdate, gname,
    decision,
    rank() over (partition by gname order by rdate desc ) as latest
    from rsvp)
select count(decision) from cte
where decision = 'Y' and latest = 1

;WITH CTE_BaseT
AS
(
SELECT 'Jon' AS CName, 'Y' as Decision, CAST('2016-01-01' AS Date) AS [D_Date]
UNION ALL
SELECT 'Jon' AS CName, 'N' as Decision, CAST('2016-01-02' AS Date) AS [D_Date]
UNION ALL
SELECT 'Linda' AS CName, 'Y' as Decision, CAST('2016-01-01' AS Date) AS [D_Date]
UNION ALL
SELECT 'Mark' AS CName, 'Y'as Decision, CAST('2016-01-05' AS Date) AS [D_Date]
UNION ALL
SELECT 'Rob' AS CName, 'Y' as Decision, CAST('2016-01-05' AS Date) AS [D_Date]
UNION ALL
SELECT 'Rob' AS CName, 'N' as Decision, CAST('2016-01-07' AS Date) AS [D_Date]
UNION ALL
SELECT 'Rob' AS CName, 'Y' as Decision, CAST('2016-01-08' AS Date) AS [D_Date]

)

SELECT CName,Decision
FROM CTE_BaseT C1
WHERE C1.D_Date = (SELECT MAX(C2.D_Date) FROM CTE_BaseT C2 WHERE C2.CName = C1.CName)

Is this correct?

SELECT NAME FROM RSVP WHERE (NAME,D) IN (
SELECT NAME, MAX(D) FROM RSVP
GROUP BY NAME)
AND decision ='Y'

SELECT COUNT(*) FROM RSVP r WHERE r.Decision = 'Y' AND r.Date IN (SELECT max(s.Date) FROM RSVP s WHERE s.Name = r.Name)

select count(*) from temptab t 
where decision = 'Y'
and t.name not in (select name from temptab t1 where decision='N' and t.name=t1.name)

Select count(*)  from rsvp a left join (select * from rsvp where decision='N') b on a.name=b.name where a.decision='Y' and  b.decision is Null

Select count(*) from rsvp a left join (select * from rsvp where decision='N') b on a.name=b.name where a.decision='Y' and b.decision is Null

select * from rsvp where (name,dt) in (
select name,max(dt) from rsvp 
group by name)
and decision='Y';

select count(name) As Total_people
from RSVP where decision = 'Y' AND Date In
( select MAX(Date) As Highest_date from RSVP
GROUP BY RSVP.Name)

select count(name) As Total_people
from RSVP where decision = 'Y' AND Date In
( select MAX(Date) As Highest_date from RSVP
GROUP BY RSVP.Name)

IF OBJECT_ID('tempdb..#RSVP', 'U') IS NOT NULL
DROP TABLE #RSVP;

CREATE TABLE #RSVP
(
Name VARCHAR(25) ,
Decision CHAR(1) ,
Dates DATE
);

INSERT INTO #RSVP
( Name, Decision, Dates )
VALUES ( 'John', 'Y', '2016-01-01'),
( 'John', 'N', '2016-01-02'),
( 'Linda', 'Y', '2016-01-01'),
( 'Mark', 'Y', '2016-01-05'),
( 'Rob', 'N', '2016-01-05');

WITH CTE AS
(SELECT Name,
Decision,
Dates,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Dates DESC) AS RowN
FROM #RSVP
)
SELECT COUNT(*)
FROM CTE WHERE CTE.RowN = 1 AND CTE.Decision = 'Y';

function closest(arr, n){
  if(arr.length === 1) return arr[0];
  var left = arr.slice(0, Math.floor(arr.length/2));
  var right = arr.slice(Math.floor(arr.length/2), arr.length);
  if( Math.abs((n - left[left.length-1])) <  Math.abs((n -right[0])) ){
    return closest(left, n);
  } else {
    return closest(right, n);
  }
}

select * from RVSP R1
    WHERE NAME NOT IN (SELECT NAME FROM RVSP R2
    WHERE DECISION='N')

select count(case when dec=y then 1 else 0 end) from a
where dt = (select maxdt from b where a.id=b.id)
group by id

select count(case when dec=y then 1  else 0 end) from a 
where dt = (select maxdt from b where a.id=b.id)
group by id

select count(*) from rsvp r1
inner join (select name,max(date) as max_date from RSVP group by name) r2
on r1.name=r2.name
and r1.date=r2.max_date
where r1.decision='Y'

select count(name) from rsvp t1
where decision ='Y'
and not exists (select 1 from rsvp where name=t1.name and decision ='N' and date>t1.date)

Select a.count(*) from
(select Name from RSVP WHERE Decision ="Y" AND Name not in (select Name from RSVP WHERE Decision ="N")) a;

select Name 
from RSVP
where Decision = 'Y'
group by Name
Having Max(Date)