Getting Unavailable Dates for Renting a Product That Has Stocks

Need to store multiple date range values and retrieve them via sql

There are two different concepts that you're describing: the "on Wednesdays" is not as much of a date range as it is a recurrence pattern. The same goes for "weekends".

You're probably looking at two different tables in addition to your property table that define these unavailable dates: one that represents specific date ranges and one that represents recurrence patterns.

                         Property
                            |
              -------------- ---------------
             |                              |
PropertyUnavailableRecurrence PropertyUnavailableRange 

(Bear in mind that you might want to figure out shorter names)

PropertyUnavailableRecurrence would need to store the information necessary for turning "Wednesdays" and "weekends" into viable decision logic. I can't model this for you, since all of you've presented in this pattern are specific days of the week, but I'd imagine that you'd need to be able to account for "First of the month" or "Second Wednesday of the month", but I don't know. In any case, this is where you'd need to store that information.

PropertyUnavailableRange would just contain simple From and To dates that define the range. This part is pretty simple.

Of course, an alternative would be to take the recurrence patterns specified in the application and turn them into discreet PropertyUnavailableRange records, but you'd still need to set up a table to store these recurrences and associate the discreet records with a recurrence so that you could manage them.

Fnd consecutive dates withing a defined span where a Trainer is available to schedule

not sure how this will perform against a larger data set, but it gets the right results for the data set provided. Missing data points are assumed to be available.

declare @startDate datetime, @endDate datetime, @days int
select @startDate = '6/1/2013', @endDate='6/3/2013', @days=2

select trainer, min(date)
from
    (
    select  trainer,date,
            (select top 1 date
            from mySchedule sInner
            where sInner.date > sOuter.date
                    and sInner.trainer = sOuter.trainer
                    and sInner.Dirty = 1    
                    and sInner.date between @startDate and @endDate
            order by sInner.date) as nextDirtyDate
    from    mySchedule sOuter
    where sOuter.dirty=0
            and sOuter.date between @startDate and @endDate
    ) sub
group by trainer, nextDirtyDate
having dateDiff(d, min(date), isNull(nextDirtyDate,dateAdd(d,1,@endDate))) >= @days

How to find first free time in reservations table in PostgreSql

Postgres 9.2 has range type and I would recommend to use them.

create table reservation (reservation tsrange);
insert into reservation values 
('[2012-11-14 09:00:00,2012-11-14 10:00:00)'), 
('[2012-11-14 10:00:00,2012-11-14 11:30:00)'), 
('[2012-11-14 12:00:00,2012-11-14 14:00:00)'), 
('[2012-11-14 16:00:00,2012-11-14 18:30:00)');

ALTER TABLE reservation ADD EXCLUDE USING gist (reservation WITH &&);

"EXCLUDE USING gist" creates index which disallows to inset overlapping entries. You can use the following query to find gaps (variant of vyegorov's query):

with gaps as (
  select 
    upper(reservation) as start, 
    lead(lower(reservation),1,upper(reservation)) over (ORDER BY reservation) - upper(reservation) as gap 
  from (
    select * 
    from reservation 
    union all values 
      ('[2012-11-14 00:00:00, 2012-11-14 08:00:00)'::tsrange), 
      ('[2012-11-14 18:00:00, 2012-11-15 00:00:00)'::tsrange)
  ) as x
) 
select * from gaps where gap > '0'::interval;

'union all values' masks out non working times hence you can make reservation between 8am and 18pm only.

Here is the result:

        start        |   gap    
---------------------+----------
 2012-11-14 08:00:00 | 01:00:00
 2012-11-14 11:30:00 | 00:30:00
 2012-11-14 14:00:00 | 02:00:00

Documentation links:
- http://www.postgresql.org/docs/9.2/static/rangetypes.html "Range Types"
- https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf

Getting statistics from data according to a list of dates

It seems, improper use of aliases for @dates table in your query and missing group by clause for aggregate function Count. You can try this query. Also make sure that you have END for the WHILE loop.

SELECT
  d.day,
  COUNT(tickets.task_number) AS CountOfOpenTickets
FROM @Dates as d
LEFT JOIN dbo.wh_task tickets ON d.day BETWEEN tickets.create_time AND tickets.date_completed
group by d.day

---

Related Topics