sql server programming: Hi!

I'm trying count the days that a person has been registered in my
system given a specific time interval, i.e. 2007-06-01 to 2007-06-30.
A person can be registered several times during a period.

If a person starts at 2007-05-12 and ends 2007-06-10, and then starts
2007-06-15 and ends 2007-06-20 my query should display 15 days. So
there is a number of ways to overlap the time interval and only the
time in between should be counted.

I wrote a query like this:

SELECT PERSON.PERSON_PNR AS Pnr,
SUM(
CASE
WHEN STARTDATE >= '2007-06-01' AND ENDDATE IS NULL THEN
DATEDIFF(day,STARTDATE,'2007-06-30')
WHEN STARTDATE <= '2007-06-01' AND ENDDATE IS NULL THEN
DATEDIFF(day,'2007-06-01','2007-06-30')
WHEN STARTDATE >= '2007-06-01' AND ENDDATE <= '2007-06-30' THEN
DATEDIFF(day,STARTDATE,ENDDATE)
WHEN STARTDATE <= '2007-06-01' AND ENDDATE <= '2007-06-30' THEN
DATEDIFF(day,'2007-06-01',ENDDATE)
WHEN STARTDATE >= '2007-06-01' AND ENDDATE >= '2007-06-30' THEN
DATEDIFF(day,STARTDATE,'2007-06-30')
WHEN STARTDATE < '2007-06-01' AND ENDDATE > '2007-06-30' THEN
DATEDIFF(day,'2007-06-01,'2007-06-30')
ELSE 0
END) AS NumberOfDays
FROM PERSON
INNER JOIN ARENDE ON ARENDE.PERSON_LNR=PERSON.PERSON_LNR
AND ARENDE.STARTDATE <= '2007-06-30'
AND (ARENDE.ENDDATE IS NULL OR ARENDE.ENDDATE >= '2007-06-01')
GROUP BY PERSON.PERSON_PNR
ORDER BY NumberOfDaysDESC

My question is: Is there a better way of writing this query? Am I
complicating things? Can reduce the case statement somehow?

Have you considered using a calendar table?

Here are some examples from the experts:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html


ML

---

Thanks. But the reasons for using a calendar table is not relevant in
my case, I think.

What I need is similar to accumulated login time between timeX and
timeY. And I have to take into account persons that are still logged
in and login sessions that overlap the specified interval.

[quoted text, click to view]

[quoted text, click to view]

That's exactly what the calendar table is there for.

I'm quite certain that if you post table DDL, sample data and expected
results, we can come up with a solution, or at least point you in the right
direction.


ML

---

For instance:

-- Prepare sample data
if (object_id('dbo.Events_fixedDates') is not null)
begin
drop view dbo.Events_fixedDates
end
go

if (object_id('dbo.Events') is not null)
begin
drop table dbo.Events
end
go

create table dbo.Events
(
EventId int identity (1, 1) not null
,UserName nvarchar(64) not null
,StartTime datetime not null
,EndTime datetime null
)
go

insert dbo.Events
(
UserName
,StartTime
,EndTime
)
select 'Jim' as UserName
,dateadd(d, -90, getdate()) as StartTime
,dateadd(d, -42, getdate()) as EndTime
union all
select 'Jim'
,dateadd(d, -40, getdate())
,getdate()
union all
select 'Bob'
,dateadd(d, -97, getdate())
,getdate()
union all
select 'Bob'
,dateadd(d, -12, getdate())
,null
go

create view dbo.Events_fixedDates
with schemabinding
as
select EventId
,UserName
,dateadd(d, 0, datediff(d, 0, StartTime)) as StartDate
,EndDate
= case
when EndTime is null
then '9999-12-31'
else dateadd(d, 0, datediff(d, 0, EndTime))
end
from dbo.Events
go

-- Get durations
select Durations.UserName
,sum(Durations.Duration) as TotalDuration
from (
select Durations.EventId
,Durations.UserName
,Duration
= case
when dbo.Events.EndTime is null
then datediff(d, dbo.Events.StartTime, getdate())
else Durations.Duration
end
,Activity
= case
when EndTime is null
then 'Unfinished'
else 'Finished'
end
from dbo.Events
inner join (
select EventId
,UserName
,count(*) as Duration
from dbo.Events_fixedDates
inner join dbo.Calendar
on (dbo.Calendar.dt >= dbo.Events_fixedDates.StartDate)
and (dbo.Calendar.dt <= dbo.Events_fixedDates.EndDate)
group by EventId
,UserName
) Durations
on Durations.EventId = dbo.Events.EventId
) Durations
group by Durations.UserName
go



ML

---

CREATE TABLE PERSON
(PERSON_PNR varchar(11) PRIMARY KEY,
PERSON_FIRSTNAME varchar(30),
PERSON_LASTNAME varchar(30)
)
GO

CREATE TABLE ARENDE
(
ARENDE_STARTDATE datetime,
ARENDE_ENDDATE datetime,
PERSON_PNR varchar(11),
CONSTRAINT FK_ARENDE_PERSON FOREIGN KEY (PERSON_PNR)
REFERENCES PERSON (PERSON_PNR)
)

INSERT INTO PERSON VALUES ('3333333','John','Smith')
INSERT INTO PERSON VALUES ('4444444','Bertrand','Russell')

INSERT INTO ARENDE VALUES ('2007-05-01','2007-06-15','3333333')
INSERT INTO ARENDE VALUES ('2007-06-20','2007-06-25','3333333')

INSERT INTO ARENDE VALUES ('2007-01-01','2008-01-15','4444444')


/*
Say I want the time for these to persons between 2007-06-01 and
2007-07-01

For John Smith:
select datediff(day,'2007-06-01','2007-06-15') +
datediff(day,'2007-06-20','2007-06-25') = 19

For Bertrand Russell:
select datediff(day,'2007-06-01','2007-07-01') = 30

*/
[quoted text, click to view]

Have you checked out the example I posted?
What is the key in the ARENDE table?


ML

---

[quoted text, click to view]

Yes, thanks for effort, I really appreciate it, but creating a
calendar table is not an option at the moment. However I will consider
it in the future.

ARENDE_ID int Identity

This is the simplest method possible, but without a calendar table you can't
know whether the durations include holidays or nonworking days.

select UserName
,sum(Duration)
from (
select UserName
,datediff(dd, StartTime, coalesce(EndTime, getdate())) as Duration
from dbo.Events
) Durations
group by UserName

Based on your DDL (untested):

select PERSON_FIRSTNAME
,PERSON_LASTNAME
,TotalDuration
from (
select PERSON.PERSON_PNR
,sum(Duration) as TotalDuration
from PERSON
inner join (
select PERSON.PERSON_PNR
,datediff(dd, ARENDE.ARENDE_STARTDATE, ARENDE.ARENDE_ENDDATE) as
Duration
from PERSON
inner join ARENDE
on ARENDE.PERSON_PNR = PERSON.PERSON_PNR
) Durations
on Durations.PERSON_PNR = PERSON.PERSON_PNR
group by PERSON.PERSON_PNR
) Persons
inner join PERSON
on PERSON.PERSON_PNR = Persons.PERSON_PNR



ML

---

Ah! Forgot about "keep it simple"... ;)

select PERSON_FIRSTNAME
,PERSON_LASTNAME
,TotalDuration
from (
select PERSON.PERSON_PNR
,sum(datediff(dd, ARENDE.ARENDE_STARTDATE, ARENDE.ARENDE_ENDDATE)) as
TotalDuration
from PERSON
inner join ARENDE
on ARENDE.PERSON_PNR = PERSON.PERSON_PNR
group by PERSON.PERSON_PNR
) Durations
inner join PERSON
on PERSON.PERSON_PNR = Durations.PERSON_PNR


ML

---