April 22, 2015 at 7:50 am
Hi all,
I've got the following question.
My plan is to mix tables below and get first and last holiday per person per set.
TABLE USER
USERID -- NAME
1Robert
2..
3..
TABLE HOLIDAYS
USERID -- SET -- DATES
1 -- 1 -- 12/08/2015
1 -- 1 -- 13/08/2015
1 -- 1 -- 14/08/2015
1 -- 2 -- 12/09/2015
1 -- 2 -- 13/09/2015
2 -- 1 -- ...
For example outputview :
USERID -- NAME -- SET -- START_DATE_HOLIDAY -- END_DATE_HOLIDAY
1 Robert 1 12/08/2015 14/08/2015
1 Robert 2 12/09/2015 13/09/2015
2 ...
-->> as you can see 13/08 is excluded in the final view
thanks for support
April 22, 2015 at 7:58 am
Use window functions:
SELECT USERID, StartDate = MIN(DATES) OVER (PARTITION BY USERID, SET), EndDate = MAX(DATES) OVER (PARTITION BY USERID, SET)
FROM HOLIDAYS;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 22, 2015 at 9:22 am
Much simpler way to do this without using windowing functions:
create table #Users (
UserID int not null,
UserName varchar(10) not null
);
insert into #Users
values (1,'Robert');
create table #Holidays (
UserID int not null,
HolidaySet int not null,
Dates date
);
insert into #Holidays
values (1,1,'2015-08-12'),(1,1,'2015-08-13'),(1,1,'2015-08-14'),(1,2,'2015-09-12'),(1,2,'2015-09-13');
go
select
u.UserID,
u.UserName,
h.HolidaySet,
min(Dates) StartHoliday,
max(Dates) EndHoliday
from
#Users u
inner join #Holidays h
on (u.UserID = h.UserID)
group by
u.UserID,
u.UserName,
h.HolidaySet
order by
u.UserID,
u.UserName,
h.HolidaySet;
April 22, 2015 at 11:24 am
Thanks alot, sounds Logic now.. Seems i was making it to complicate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply