September 3, 2014 at 1:47 am
Hi...
I want to ask again..
I represent the table like this:
EmployeeIDStartDateEndDate
0035052014-08-01 2014-08-02
0035052014-08-04 2014-08-09
I want to split all date within the range StartDate and EndDate to become 1 field Date where i have to select the Date, if registered in the specified table then show it.
Master Date
===========
Date
2014-08-01
2014-08-02
2014-08-03
2014-08-04
2014-08-05
2014-08-07
2014-08-09
so the output like this
EmployeeID Date
003505 2014-08-01
003505 2014-08-02
003505 2014-08-04
003505 2014-08-05
003505 2014-08-07
003505 2014-08-09
Note: date 2014-08-06, 2014-08-08 not in the output since it's not registered on master Date table.
Is that possible using recursive cte to make a simple query ?
Thanks
September 3, 2014 at 3:51 am
DROP TABLE #Represent
CREATE TABLE #Represent (EmployeeID INT, StartDate DATE, EndDate DATE)
INSERT INTO #Represent (EmployeeID, StartDate, EndDate)
SELECT 003505, '2014-08-01', '2014-08-02' UNION ALL
SELECT 003505, '2014-08-04', '2014-08-09'
DROP TABLE #MasterDate
CREATE TABLE #MasterDate ([Date] DATE)
INSERT INTO #MasterDate ([Date])
SELECT '2014-08-01' UNION ALL
SELECT '2014-08-02' UNION ALL
SELECT '2014-08-03' UNION ALL
SELECT '2014-08-04' UNION ALL
SELECT '2014-08-05' UNION ALL
SELECT '2014-08-07' UNION ALL
SELECT '2014-08-09'
SELECT r.EmployeeID, x.[Date]
FROM #Represent r
CROSS APPLY (
SELECT m.[Date] FROM #MasterDate m WHERE m.[Date] BETWEEN r.StartDate AND r.EndDate
) x
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
September 3, 2014 at 4:38 am
You can do it also with an Inner Join:
create table #MasterDates (D DATE)
go
insert into #MasterDates (D) VALUES
('2014-08-01'),
('2014-08-02'),
('2014-08-03'),
('2014-08-04'),
('2014-08-05'),
('2014-08-07'),
('2014-08-09')
go
create table #Employees (EmployeeID char(6), StartDate date, EndDate date)
go
INSERT INTO #Employees (EmployeeID, StartDate, EndDate)
values('003505', '2014-08-01', '2014-08-02'),
('003505', '2014-08-04', '2014-08-09')
go
select E.EmployeeID, MD.D
FROM #Employees E inner join #MasterDates MD ON E.StartDate <= MD.D and E.EndDate >= MD.D
go
--cleanup
DROP TABLE #MasterDates
DROP TABLE #Employees
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 3, 2014 at 4:56 am
Adi Cohn-120898 (9/3/2014)
You can do it also with an Inner Join:
create table #MasterDates (D DATE)
go
insert into #MasterDates (D) VALUES
('2014-08-01'),
('2014-08-02'),
('2014-08-03'),
('2014-08-04'),
('2014-08-05'),
('2014-08-07'),
('2014-08-09')
go
create table #Employees (EmployeeID char(6), StartDate date, EndDate date)
go
INSERT INTO #Employees (EmployeeID, StartDate, EndDate)
values('003505', '2014-08-01', '2014-08-02'),
('003505', '2014-08-04', '2014-08-09')
go
select E.EmployeeID, MD.D
FROM #Employees E inner join #MasterDates MD ON E.StartDate <= MD.D and E.EndDate >= MD.D
go
--cleanup
DROP TABLE #MasterDates
DROP TABLE #Employees
The plans are identical:
SELECT e.EmployeeID, x.D
FROM #Employees e
CROSS APPLY (
SELECT md.D FROM #MasterDates MD WHERE md.D BETWEEN e.StartDate AND e.EndDate
) x
SELECT E.EmployeeID, MD.D
FROM #Employees E inner join #MasterDates MD ON md.D BETWEEN e.StartDate AND e.EndDate
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply