Calculate number of unique days

[Toto]

Hello,

Please help me !

My table :

Id Start End

1 2013.11.20 2014.01.10 2 2014.01.17 2014.01.30 3 2014.01.06 2014.01.27 4 2014.02.04 2014.02.23 5 2014.02.07 2014.02.17 6 2014.02.21 2014.03.08

How to calculate total number of unique days? Date ranges are overlapped.

I counted manually- result must be 105 (the last day of the range is included)

Thank you

 

[pr2-eugin]

Define Unique days. Edit your post using the CODE tags to preserve formatting.

 

[Toto]

Day does not have to be counted twice, then it is in the overlapping date ranges.

Sorry, my table :

        [FONT=Calibri]ID[/FONT] [FONT=Calibri]Start[/FONT] [FONT=Calibri]End[/FONT]  [FONT=Calibri]1[/FONT] [FONT=Calibri]2013.11.20[/FONT] [FONT=Calibri]2014.01.10[/FONT]  [FONT=Calibri]2[/FONT] [FONT=Calibri]2014.01.17[/FONT] [FONT=Calibri]2014.01.30[/FONT]  [FONT=Calibri]3[/FONT] [FONT=Calibri]2014.01.06[/FONT] [FONT=Calibri]2014.01.27[/FONT]  [FONT=Calibri]4[/FONT] [FONT=Calibri]2014.02.04[/FONT] [FONT=Calibri]2014.02.23[/FONT]  [FONT=Calibri]5[/FONT] [FONT=Calibri]2014.02.07[/FONT] [FONT=Calibri]2014.02.17[/FONT]  [FONT=Calibri]6[/FONT] [FONT=Calibri]2014.02.21[/FONT] [FONT=Calibri]2014.03.08[/FONT][/SIZE]
 [SIZE=2]

 

[namliam]

select id, max(end) - min(start) NumberofDays from yourtable group by id

Is that what you are looking for?

 

[Toto]

Thank you, this expression calculates the number of days, but does not excludes the overlapped periods .

I found a similar question and the solution in the SQL Server forum:

h ttp://social.msdn.microsoft.com/Forums/sqlserver/en-US/a19e2c53-73b3-430f-9ced-99ee0e44215f/find-consecutive-days-from-overlapping-date-ranges-to-keep-from-double-counting-them-in-a-datediff?forum=transactsql
but I don't know how to use the solution for the Access 2010 query

 

[namliam]

ow wait your IDs are unique....
select max(end) - min(start) NumberofDays from yourtable

 

[namliam]

That solution in the thread you found is basicaly the same as the one I gave you

 

[Toto]

I think that SQL query select max(end) - min(start) NumberofDays from table calculates only number of days between first start date and last end date. Result is 108 (wrong).
I found out another method. I created the calendar table and 3 query.The first one creates a crosstab of all dates between the beginning date and the ending date The second is a union query that puts all the data into one column and selects out the dates that are unique (SELECT DISTINCT) . The third calculates the number of unique days . Result from my table is 105.