October 31, 2018 at 7:01 pm
Can somebody please help with this issue...
/*
Here’s the problem: Let's say we had a table of all US Presidents, with the date they took office and the date they died.
Let's say I want to get the periods of time (start date / end date) where at least 3 US Presidents or former US Presidents
were alive at the same time, sorted in order of
1.) the most number of presidents/former presidents alive at one time and
2.) the longest period of time. Here's a sample resultset I am looking for:NumberOfPresidentsAlive StartDate EndDate NumberOfDays
6 1/1/1853 12/31/1853 364
6 5/5/1854 5/6/1854 1
5 1/1/1854 3/1/1854 59
....Note that the periods of time should be based on calendar date and that both efficiency and elegancy are highly desired properties of the solution.
For bonus points, include a column with a comma separated list of the president's names.*/IF OBJECT_ID('tempdb..#President') IS NOT NULL DROP TABLE #President;Create table #President
(
PresidentID INT IDENTITY(1,1) PRIMARY KEY,
Name varchar(60),
StartDate date,
DiedDate date
) WITH (DATA_COMPRESSION=PAGE);
go
-- Data Source: https://en.wikipedia.org/wiki/List_of_Presidents_of_the_United_States_by_date_of_deathInsert into #President(Name,StartDate,DiedDate)
values
('George Washington','4/1/1789','12/14/1799')
,('Thomas Jefferson','3/4/1801','7/4/1826')
,('John Adams','3/4/1797','7/4/1826')
,('James Monroe','3/4/1817','7/4/1831')
,('James Madison','3/4/1809','6/28/1836')
,('William Henry Harrison','3/4/1841','4/4/1841')
,('Andrew Jackson','3/4/1829','6/8/1845')
,('John Quincy Adams','3/4/1825','2/23/1848')
,('James K. Polk','3/4/1845','6/15/1849')
,('Zachary Taylor','3/4/1849','7/9/1850')
,('John Tyler','4/4/1841','1/18/1862')
,('Martin Van Buren','3/4/1837','7/24/1862')
,('Abraham Lincoln','3/4/1861','4/15/1865')
,('James Buchanan','3/4/1857','6/1/1868')
,('Franklin Pierce','3/4/1853','10/8/1869')
,('Millard Fillmore','7/9/1850','3/8/1874')
,('Andrew Johnson','4/15/1865','7/31/1875')
,('James A. Garfield','3/4/1881','9/19/1881')
,('Ulysses S. Grant','3/4/1869','7/23/1885')
,('Chester A. Arthur','9/19/1881','11/18/1886')
,('Rutherford B. Hayes','3/4/1877','1/17/1893')
,('Benjamin Harrison','3/4/1889','3/13/1901')
,('William McKinley','3/4/1897','9/14/1901')
,('Grover Cleveland','3/4/1885','6/24/1908')
--,('Grover Cleveland','3/4/1893', '6/24/1908'),
,('Theodore Roosevelt','9/14/1901','1/6/1919')
,('Warren G. Harding','3/4/1921','7/2/1923')
,('Woodrow Wilson','3/4/1913','2/3/1924')
,('William Howard Taft','3/4/1909','3/8/1930')
,('Calvin Coolidge','7/2/1923','1/5/1933')
,('Franklin D. Roosevelt','3/4/1933','4/12/1945')
,('John F. Kennedy','1/20/1961','11/22/1963')
,('Herbert Hoover','3/4/1929','10/20/1964')
,('Dwight D. Eisenhower','1/20/1953','3/28/1969')
,('Harry S. Truman','4/12/1945','12/26/1972')
,('Lyndon B. Johnson','11/22/1963','1/22/1973')
,('Richard Nixon','1/20/1969','4/22/1994')
,('Ronald Reagan','1/20/1981','6/5/2004')
,('Gerald Ford','7/9/1974','12/26/2006')
,('Jimmy Carter','1/20/1977',Null)
,('George H. W. Bush','1/20/1989',Null)
,('Bill Clinton','1/20/1993',Null)
,('George W. Bush','1/20/2001',Null)
,('Barack Obama','1/20/2009',Null)
,('Donald Trump','1/20/2017',Null);
Thank you
Milan
October 31, 2018 at 10:30 pm
Sounds like an extra credit assignment for a take home test. Pass.
October 31, 2018 at 11:49 pm
Hi,
This is not an assignment but kind of question from my friend to me.... list questions i did all 18 except this one
if possible please help me....
Thank you,
MIlan
November 1, 2018 at 2:57 am
Create a calendar table with all dates from 1st April 1789 to the present day. Then join your table of presidents to that on calendar date between StartDate and DiedDate, and do a count grouped by calendar date.
By the way, thanks for providing DDL and sample data in consumable form. However, those dates will fail in parts of the world where the US date format isn't used. Better to specify dates in the universally recognised format '17991214'.
John
November 4, 2018 at 2:28 am
These should get you pretty close...
USE CodeTest;
GO
IF OBJECT_ID('tempdb..#President') IS NOT NULL
DROP TABLE #President;
CREATE TABLE #President (
PresidentID INT IDENTITY (1, 1) PRIMARY KEY,
Name VARCHAR (60),
StartDate DATE,
DiedDate DATE
)
WITH (DATA_COMPRESSION = PAGE);
GO
INSERT #President
VALUES
('George Washington', '4/1/1789', '12/14/1799'),
('Thomas Jefferson', '3/4/1801', '7/4/1826'),
('John Adams', '3/4/1797', '7/4/1826'),
('James Monroe', '3/4/1817', '7/4/1831'),
('James Madison', '3/4/1809', '6/28/1836'),
('William Henry Harrison', '3/4/1841', '4/4/1841'),
('Andrew Jackson', '3/4/1829', '6/8/1845'),
('John Quincy Adams', '3/4/1825', '2/23/1848'),
('James K. Polk', '3/4/1845', '6/15/1849'),
('Zachary Taylor', '3/4/1849', '7/9/1850'),
('John Tyler', '4/4/1841', '1/18/1862'),
('Martin Van Buren', '3/4/1837', '7/24/1862'),
('Abraham Lincoln', '3/4/1861', '4/15/1865'),
('James Buchanan', '3/4/1857', '6/1/1868'),
('Franklin Pierce', '3/4/1853', '10/8/1869'),
('Millard Fillmore', '7/9/1850', '3/8/1874'),
('Andrew Johnson', '4/15/1865', '7/31/1875'),
('James A. Garfield', '3/4/1881', '9/19/1881'),
('Ulysses S. Grant', '3/4/1869', '7/23/1885'),
('Chester A. Arthur', '9/19/1881', '11/18/1886'),
('Rutherford B. Hayes', '3/4/1877', '1/17/1893'),
('Benjamin Harrison', '3/4/1889', '3/13/1901'),
('William McKinley', '3/4/1897', '9/14/1901'),
('Grover Cleveland', '3/4/1885', '6/24/1908'),
--,('Grover Cleveland','3/4/1893', '6/24/1908'),
('Theodore Roosevelt', '9/14/1901', '1/6/1919'),
('Warren G. Harding', '3/4/1921', '7/2/1923'),
('Woodrow Wilson', '3/4/1913', '2/3/1924'),
('William Howard Taft', '3/4/1909', '3/8/1930'),
('Calvin Coolidge', '7/2/1923', '1/5/1933'),
('Franklin D. Roosevelt', '3/4/1933', '4/12/1945'),
('John F. Kennedy', '1/20/1961', '11/22/1963'),
('Herbert Hoover', '3/4/1929', '10/20/1964'),
('Dwight D. Eisenhower', '1/20/1953', '3/28/1969'),
('Harry S. Truman', '4/12/1945', '12/26/1972'),
('Lyndon B. Johnson', '11/22/1963', '1/22/1973'),
('Richard Nixon', '1/20/1969', '4/22/1994'),
('Ronald Reagan', '1/20/1981', '6/5/2004'),
('Gerald Ford', '7/9/1974', '12/26/2006'),
('Jimmy Carter', '1/20/1977', NULL),
('George H. W. Bush', '1/20/1989', NULL),
('Bill Clinton', '1/20/1993', NULL),
('George W. Bush', '1/20/2001', NULL),
('Barack Obama', '1/20/2009', NULL),
('Donald Trump', '1/20/2017', NULL);
WITH
cte_possible_range AS (
SELECT DISTINCT
id = DENSE_RANK() OVER (ORDER BY p2.StartDate, ISNULL(p1.DiedDate, GETDATE())),
p2.StartDate,
DiedDate = ISNULL(p1.DiedDate, GETDATE()),
NumerOfDays = DATEDIFF(DAY, p2.StartDate, ISNULL(p1.DiedDate, GETDATE()))
FROM
#President p1
JOIN #President p2
ON p1.StartDate <= p2.StartDate
AND ISNULL(p1.DiedDate, GETDATE()) > p2.StartDate
)
SELECT
NumberOfPresidentsAlive = COUNT(1) OVER (PARTITION BY pr.StartDate, pr.DiedDate),
*
FROM
cte_possible_range pr
JOIN #President p
ON p.StartDate >= pr.StartDate
AND ISNULL(p.DiedDate, GETDATE()) <= pr.DiedDate
ORDER BY
NumberOfPresidentsAlive DESC,
pr.StartDate,
pr.DiedDate,
p.StartDate;
WITH
cte_possible_range AS (
SELECT DISTINCT
id = DENSE_RANK() OVER (ORDER BY p2.StartDate, ISNULL(p1.DiedDate, GETDATE())),
p2.StartDate,
DiedDate = ISNULL(p1.DiedDate, GETDATE()),
NumerOfDays = DATEDIFF(DAY, p2.StartDate, ISNULL(p1.DiedDate, GETDATE()))
FROM
#President p1
JOIN #President p2
ON p1.StartDate <= p2.StartDate
AND ISNULL(p1.DiedDate, GETDATE()) > p2.StartDate
)
SELECT
NumberOfPresidentsAlive = COUNT(1),
pr.StartDate,
pr.DiedDate,
pr.NumerOfDays
FROM
cte_possible_range pr
JOIN #President p
ON p.StartDate >= pr.StartDate
AND ISNULL(p.DiedDate, GETDATE()) <= pr.DiedDate
GROUP BY
pr.StartDate,
pr.DiedDate,
pr.NumerOfDays
HAVING
COUNT(1) > 1
ORDER BY
COUNT(1) DESC,
pr.StartDate;
November 4, 2018 at 10:04 pm
The logic i am using is as follows, find out the records of presidents whose tenure began after another president and who died after the current record president died.
This is the query i used. But i am wondering how the expected output of works out?
NumberOfPresidentsAlive StartDate EndDate NumberOfDays
6 1/1/1853 12/31/1853 364
6 5/5/1854 5/6/1854 1
5 1/1/1854 3/1/1854 59
select x.* from (
select a.name,a.startdate,a.dieddate,b.name as alive_name,b.startdate alive_startdate,b.dieddate as alive_dieddate
,count(*) over(partition by a.name,a.startdate) as cnt
from president a
join president b
on a.startdate > b.startdate
and b.dieddate > a.dieddate
and a.name <> b.name
)x
where x.cnt>=3
order by x.cnt desc,x.startdate,x.name
November 5, 2018 at 11:50 am
Jeff Moden - Monday, November 5, 2018 5:44 AMgeorge_at_sql - Sunday, November 4, 2018 10:04 PMThe logic i am using is as follows, find out the records of presidents whose tenure began after another president and who died after the current record president died.This is the query i used. But i am wondering how the expected output of works out?
NumberOfPresidentsAlive StartDate EndDate NumberOfDays
6 1/1/1853 12/31/1853 364
6 5/5/1854 5/6/1854 1
5 1/1/1854 3/1/1854 59
select x.* from (
select a.name,a.startdate,a.dieddate,b.name as alive_name,b.startdate alive_startdate,b.dieddate as alive_dieddate
,count(*) over(partition by a.name,a.startdate) as cnt
from president a
join president b
on a.startdate > b.startdate
and b.dieddate > a.dieddate
and a.name <> b.name
)x
where x.cnt>=3
order by x.cnt desc,x.startdate,x.nameBoth your and Jason's methods result in Cartesian Products. I don't know how it will react with a much large number of items in the table but suspect a Cartesian Product will appear in such situations with devastating effects on resource usage and performance. Seriously, give Itzik's method #2 or #3 a good look at the link I previously provided.
Here is an implementation that uses #3.
WITH DateRanges AS
(
SELECT dt.EventDate AS BeginDate, LEAD(dt.EventDate, 1) OVER(ORDER BY dt.EventDate) AS EndDate, SUM(EventChangeCount) AS EventChangeTotal
FROM #President p
CROSS APPLY ( VALUES(p.StartDate, 1), (COALESCE(p.DiedDate, '9999-12-30'), -1) ) AS dt(EventDate, EventChangeCount)
GROUP BY EventDate
)
SELECT dr.BeginDate, dr.EndDate, SUM(dr.EventChangeTotal) OVER(ORDER BY BeginDate ROWS UNBOUNDED PRECEDING) AS LivingPresidents
FROM DateRanges dr
ORDER BY dr.BeginDate;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 7, 2019 at 1:48 pm
Hello all!
I am the author to this question/challenge. This is good discussion and I appreciate the reference to Itzik's packing intervals blog post.
Two notes to anyone finding this thread and trying to find a solution:
1.) Anyone receiving this question directly is expected to provide their own solution to it.
2.) I will absolutely know if they did not create their own solution to it and that just wastes everyone's time
January 7, 2019 at 7:20 pm
lazerathSQL - Monday, January 7, 2019 1:48 PMHello all!I am the author to this question/challenge. This is good discussion and I appreciate the reference to Itzik's packing intervals blog post.
Two notes to anyone finding this thread and trying to find a solution:
1.) Anyone receiving this question directly is expected to provide their own solution to it.
2.) I will absolutely know if they did not create their own solution to it and that just wastes everyone's time
Is this an exercise for a college or similar course/project/exam?
p.s. Welcome aboard!
--Jeff Moden
January 7, 2019 at 10:10 pm
Just a heads up... I "talked" with the author of this problem and have confirmed the intent. pietlinden was almost on the money but the intent is a bit more serious than some college take home exam.
asita - Wednesday, October 31, 2018 11:49 PMThis is not an assignment but kind of question from my friend to me.... list questions i did all 18 except this oneif possible please help me....
Thank you,
MIlan
"a kind of question from my friend to me"... heh... indeed.
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply