May 16, 2018 at 4:33 pm
Hello good Morning,
CREATE TABLE #EMP (EMPID int, Name varchar(20))
INSERT INTO #EMP (EMPID , Name ) VALUES (100,'BOB'), (102,'TIM'), (103,'ROB'), (104,'SAM'), (105,'MARLY'), (106,'JOHN'), (107,'KING'), (108,'SERTA')
CREATE TABLE #EMPSCHETEMPSTATS (EMPID int, WorkLocation varchar(20), LocationStartDt DATETIME, LocationEndDt DATETIME)
INSERT INTO #EMPSCHETEMPSTATS (EMPID , WorkLocation , LocationStartDt , LocationEndDt )
VALUES
(100,'NYC','03/31/2010','05/10/2014'), -- Date Passed Row
(100,'BOSTON','05/11/2014','04/10/2017'), -- Date Passed Row
(100,'MILAN','04/11/2017','07/23/2023'), -- Current Rows
(100,'PARIS','07/24/2023','05/10/2027'), -- Future Row
(100,'SIDNEY','05/11/2027','05/10/2038'), -- Future Row
(101,'NYC','01/31/2015','09/10/2018'),
(101,'BOSTON','09/11/2018','09/10/2027'),
(101,'MILAN','09/11/2027','09/23/2033'),
(102,'PARIS','07/24/2010','05/10/2018'),
(102,'SIDNEY','05/11/2018','05/10/2038'),
(102,'LONDON','05/11/2038','05/10/2048'),
(105,'BOSTON','04/11/2014','04/10/2018'),
(105,'MILAN','04/11/2018','09/23/2023'),
(105,'PARIS','09/24/2023','09/10/2027'),
(105,'SIDNEY','09/11/2027','05/10/2028'),
(107,'NYC','03/31/2010','05/10/2014'),
(107,'BOSTON','05/11/2014','04/10/2016'),
(107,'MILAN','04/11/2016','07/23/2018'),
(107,'PARIS','07/24/2018','05/10/2019'),
(107,'SIDNEY','05/11/2019','03/30/2020'),
(107,'DUBAI','03/31/2020','05/10/2022'),
(107,'SANTO','05/11/2022','04/10/2023'),
(107,'BUGAB','04/11/2023','07/23/2026'),
(107,'URUGY','07/24/2026','05/10/2027'),
(107,'AUCKLAND','05/11/2027','09/10/2028'),
(107,'GAUTAM','09/30/2028','05/10/2029'),
(107,'PUTNAM','05/11/2029','07/10/2030'),
(107,'ADENS','07/11/2030','04/10/2035'),
(107,'KLINE','04/11/2035','04/10/2037')
SELECT * FROM #EMPSCHETEMPSTATS
Can you please help me out to get result as below
EMPID, EMPNAME, WorkingLocation1, LocationStartDt1, LocationEndDt1, WorkingLocation2, LocationStartDt2, LocationEndDt2,WorkingLocation3, LocationStartDt3, LocationEndDt3,.........WorkingLocation12, LocationStartDt12, LocationEndDt12
looking with always start from current date(getdate()) between startdt and enddate as locationstartdt1, locationenddate1 then next upcoming one then so on ignore the paased dates rows
for example for employee 100 the expected result is
SELECT 100 as EMPID, 'BOB' AS EMPNAME, 'MILAN' AS WorkingLocation1,'04/11/2017' LocationStartDt1 ,'07/23/2023' LocationEndDt1, 'PARIS'WorkingLocation2 ,'07/24/2023' LocationStartDt2 LocationEndDt2,'05/10/2027' ,'SIDNEY' WorkingLocation3,'05/11/2027' LocationStartDt2 ,'05/10/2038' LocationEndDt2 ..... till 12 locations please...
hope you understand... please help me
Thank you in advance
ASita
May 16, 2018 at 8:45 pm
Can somebody please help me i am using Sql 2012 please...
May 17, 2018 at 3:27 am
asita - Wednesday, May 16, 2018 8:45 PMCan somebody please help me i am using Sql 2012 please...
-- Crosstab query:
;WITH OrderedData AS (
SELECT e.EMPID, e.[Name], t.WorkLocation, t.LocationStartDt, t.LocationEndDt, rn = ROW_NUMBER() OVER(PARTITION BY e.EMPID ORDER BY LocationStartDt)
FROM #EMP e
INNER JOIN #EMPSCHETEMPSTATS t ON t.EMPID = e.EMPID)
SELECT
EMPID, [Name],
WorkingLocation1 = MAX(CASE WHEN rn = 1 THEN WorkLocation END), LocationStartDt1 = MAX(CASE WHEN rn = 1 THEN LocationStartDt END), LocationEndDt1 = MAX(CASE WHEN rn = 1 THEN LocationEndDt END),
WorkingLocation2 = MAX(CASE WHEN rn = 2 THEN WorkLocation END), LocationStartDt2 = MAX(CASE WHEN rn = 2 THEN LocationStartDt END), LocationEndDt2 = MAX(CASE WHEN rn = 2 THEN LocationEndDt END),
WorkingLocation3 = MAX(CASE WHEN rn = 3 THEN WorkLocation END), LocationStartDt3 = MAX(CASE WHEN rn = 3 THEN LocationStartDt END), LocationEndDt3 = MAX(CASE WHEN rn = 3 THEN LocationEndDt END),
WorkingLocation4 = MAX(CASE WHEN rn = 4 THEN WorkLocation END), LocationStartDt4 = MAX(CASE WHEN rn = 4 THEN LocationStartDt END), LocationEndDt4 = MAX(CASE WHEN rn = 4 THEN LocationEndDt END),
WorkingLocation5 = MAX(CASE WHEN rn = 5 THEN WorkLocation END), LocationStartDt5 = MAX(CASE WHEN rn = 5 THEN LocationStartDt END), LocationEndDt5 = MAX(CASE WHEN rn = 5 THEN LocationEndDt END),
WorkingLocation6 = MAX(CASE WHEN rn = 6 THEN WorkLocation END), LocationStartDt6 = MAX(CASE WHEN rn = 6 THEN LocationStartDt END), LocationEndDt6 = MAX(CASE WHEN rn = 6 THEN LocationEndDt END)
FROM OrderedData
GROUP BY EMPID, [Name]
ORDER BY EMPID, [Name]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply