BETWEEN tider eller sådan noget

En måde at gøre det på, kunne være at have en tabel med alle aktuelle tidspunkter på en dag. Denne tabel linker du til den tabel som giver oversigt over optagne tider, således at du kan vælge ud alle tidspunkter, fra den første tabel, som ikke har en tilsvarende record i den anden tabel.

I første tabel har du:

Tabel: AktuelleTider

Tid
09:00
09:15
09:30
10:00
10:15
11:00
11:15
11:30
11:45
12:00

I anden tabel har du

Tabel: vw_avaTime
Dato        Tid
11/11        9:00
11/11      10:15
11/11      11:30


Select T1.Tid
From AktuelleTider T1
Where Not Exists(Select * From vw_avaTime T2 where T2.Tid = T1.Tid and T2.Dato = '11/11')

Ovenstående er meget forenklet, og forudsætter at det er de samme ledige tider hver dag. Alternativt må den første tabel indeholde ledige tider for hver dag, hvor du også har dato med. Et andet alternativ er at du har f.eks nogle typer af dage, med ulige antal ledige tider for hver type dag. Så kan du eventuelt have en 3. tabel, hvor du angiver hvilke type hver enkelt tag tilhører.

I eksemplet ovenfor er datoen '11/11' bare vist som tekst for at lave det simpelt. Dette vil du lave som datofelt.

Håber ovenstående kan give dig lidt inspiration.

det har jeg sådan set allerede gjort i viewet. Det skal være sådan at når jeg har valgt en varighed på 30 min, og jeg vælger en dag hvor der allerede er reserveringer, så skal de tider hvor varigheden passer ind vises, og de tider hvor varigheden ikke passer skal ikke vises.

Fx. (jvf ovennævnte liste), så skal listen kun vise (hvis varigheden er 30 min):
09:00
09:15
11:00
osv...
Dvs. en tid som fx 09:30, 10:00 og 10:15 ikke skal kunne vælges da varigheden vil overlappe en allerede reserveret tid.

Din problemstilling er nok nemmest at løse i applikationen. Alternativt vil jeg tro du skal lave en stored procedure. Her kunne du lave en temporær tabel, som du fylder ud med alle hullerne i kalenderen, og med hvormange minutter der er ledig til næste aftale. Derefter vil det være nemt at søge alle starttidspunkter, hvor der er mere end x antal minutter ledig til næste aftale.

Men jeg kan ikke helt gennemskue hvordan kodningen skal være enten i applikationen (VB.NET) eller i stored procedure'n.

Hej hellfishdk

Jeg har lavet en procedure der kan det du gerne vil. Jeg har omdannet dine tider 09:00, 09:15, 09:30 osv. til minuttet på dagen... Sådan at eks.vis 10:00 er 600 minutter. Til det har jeg oprettet en tabel, nu har jeg kun lige smidt lidt testdata ind i den, men du kan bare udvide efter behov jo :-)


create table T_time
(
id int,
shorttime int,
is_taken bit
)

insert into T_time values (1, 540, 0)
insert into T_time values (2, 555, 0)
insert into T_time values (3, 570, 0)
insert into T_time values (4, 585, 1)
insert into T_time values (5, 600, 0)
insert into T_time values (6, 615, 0)
insert into T_time values (7, 630, 1)
insert into T_time values (8, 645, 1)
insert into T_time values (9, 660, 0)
insert into T_time values (10, 675, 0)

Så har jeg forudsat at eks.vis en tidsbestilling på 30 minutter udgør 2 enheder af 15minutter, derfor er @required_period = 2. Det burde også være relativt nemt at udvide den til eks.vis 5minutters interval... det kan du selv bestemme jo.


Så har jeg lavet et loop i T-Sql der looper de tilgængelige tider igennem og hvergang periodcounteren er over den periode som du har sat, i det tilfæld her 2 vil den printe den tilgængelige tid ud:

declare @required_period int
declare @id int
declare @shorttime int
declare @is_taken int
declare @periodcounter int

set @periodcounter = 0
set @required_period = 2

select @id = max(id) from T_time
while @id > 0
begin
    select @shorttime = shorttime, @is_taken = is_taken from T_time where id = @id
   
    if @is_taken = 0
        set @periodcounter = @periodcounter + 1


    if @is_taken = 1
        set @periodcounter = 0


    if @periodcounter >= @required_period
        print @shorttime

    set @id = @id -1
end

Den printer den tilgængelige tid ud, du kan bare sætte den til at smide resultatet ind i en tabel eller lign. istedet jo.

Håber du kan bruge det og se idéen med algoritmen... ellers bare spørg :-)

janus_007, jeg kan godt se logikken i dit forslag, og det fungerer fint med dine data. Men når jeg prøver at "konverterer" det til min egen sproc, går det galt. Så har du mulighed for at prøve at lave det med de info jeg har givet? :)

Naturligvis da... Er det alle tidspunkterne du vil have med i eksemplet?

Ja tak :)

Jeg har lavet T_time lidt nemmere at sætte tider ind i, hvordan du fremover vil sætte dem ind, vil jeg ikke blande mig i. Men jeg kunne forestille mig noget med en datocolumn osv, som der skal tages med...

create table T_time
(
id int identity(1, 1),
shorttime int,
is_taken bit default 0
)

declare @s int
set @s = (select isnull(max(shorttime), 465) +15 from T_time)
insert into T_time (shorttime) values (@s)


Marker de ovenstående 3 linjer og tryk F5, 10, 15, 20 gange...

select * from T_time

Så har jeg lavet en procedure der giver dig alle de tilgængelige tider.

alter proc sp_available_times
    @required_period int
as
begin

    declare @id int
    declare @shorttime int
    declare @is_taken int
    declare @periodcounter int
   
    set @periodcounter = 0
    select @id = max(id) from T_time
   
    create table #avatime
    (
    id int, shorttime int, is_taken bit
    )
   
    while @id > 0
    begin
        select @shorttime = shorttime, @is_taken = is_taken from T_time where id = @id
       
        if @is_taken = 0
            set @periodcounter = @periodcounter + 1
   
   
        if @is_taken = 1
            set @periodcounter = 0
   
   
        if @periodcounter >= @required_period
                insert into #avatime select * from T_time where id = @id
        set @id = @id -1
   
    end
select * from #avatime order by id
end

Nu reserverer vi 3 tider, så vi har lidt at teste med:
update T_time set is_taken = 1 where shorttime in(585, 630, 645)

Så vil vi gerne have tilgængelige tider, hvis der skal reserveres for 30 min (2*15):

exec sp_available_times 2



Den giver en liste over tiderne som kunden kan få.

Jeg har prøvet at rette til så den passer mine tabeller og views, men jeg får ikke nogen tid listet.

alter proc sp_avaTime
    @required_period varchar(2)
as
begin

    declare @id int
    declare @ledigTid varchar(5)
    declare @ResDato datetime
    declare @varighed varchar(2)
   
    set @varighed = 0
    select @id = max(tidid) from vw_avaTime
   
  --  create table #avaTime
  --  (
  --  id int, ledigTid varchar(5), resdato datetime
  --  )
   
    while @id > 0
    begin
        select @ledigTid = ledigTid, @resDato = resDato from vw_avaTime where tidid = @id
       
        if @resDato = 0
            set @varighed = @varighed + 1
   
   
        if @resdato = 1
            set @varighed = 0
   
   
        if @varighed >= @required_period
                insert into #avatime select * from vw_avaTime where tidid = @id
        set @id = @id -1
   
    end
select * from #avaTime order by id
end

Jeg kan se du har deklareret @resDato som datetime? Det skulle meget gerne være bit eller int eller lign.

Jeg kan se du her kommenteret  --  create table #avaTime , jeg formoder det kun er her i testfasen eller?

Kan du ikke lige smide et udtræk fra select * from vw_avaTime

Det var en kommentering har fjernet den fra sproc'en

hvis jeg ændre resDato fra datetime til int får jeg denne besked:
Msg 245, Level 16, State 1, Procedure sp_avaTime, Line 23
Conversion failed when converting the nvarchar value '28-11-2006' to data type int.

Jeg har prøvet at ændre resdato til nvarchar.

Nu får jeg en temp tabel, som jeg skal, men der kommer ingenting i den. ReturnValue er 0(nul).

Vil det være nemmere hvis jeg viste min datatyper i vw_avaTime? Det view der viser de tilgængelige tider, på en dag der er reserveret.

vw_avaTime
----------
ResDato nvarchar <-Dato, fx 29-11-2006
tidID int        <-Id for tid 1, 2....
ledigTid char    <-tidspunkt fx 11:30

Jeg tænker at det måske er fordi @resDato slet ikke giver 0 retur? Hvad er default værdien i tabellen når der ikke er indsat nogen reservation?

Når du vil bruge dato til at angive om tiden er taget eller ej, så tænker jeg der kan opstå et problem når der skal reserveres flere datoer på samme tid?

Mit oplæg til den oprindelige T_time tabel er at bygge den op med alle datoer som bliver reserveret, det giver en  meget stor tabel, men det vil løse opgaven.
Dvs. en kunde bestiller måske 12-12-2006 kl. 12:00, den dato findes ikke i tabellen og derfor skal man så indsætte hele dagen, altså en række pr. 15min fra kl 0900 til 1700 på d. 12.12.2006

Ellers må jeg jo lave det om så det passer med datoer :-|

Applikationen er bygget således op, at man vælger en dato fra en kalender, hvor så der hentes en liste med tider. Hvis der ikke er nogen reservering i den valgte dato, hvis alle tider, men hvis der er reserveret tider, så listes tider fra vw_avaTime.

vw_avaTime er bygget op således at de tider der er ledige på en allerede reserveret dato, listes. Fx 28-11-2006 er der foretaget reserveringer, så de tider der er reserveret vil ikke blive vist på den dato.
Listen vil selvfølgelig gå hen og blive ret stor, men der skal vel bare en sproc ind og lægger valgte tider før dagsdato ind i en slags backuptabel. Men det er en helt anden snak :)

hmm okay.. men så skulle man jo nok lave en tabel med tid og reservationer i. Vi forudsætter hver reservation har en varighed af 15min.

Eks.vis
create table T_reserveret_tid
(
shorttime int,
res_date datetime
)
insert into T_reserveret_tid(600, '2006-12-20')
insert into T_reserveret_tid(615, '2006-12-20')
insert into T_reserveret_tid(900, '2006-12-20')
insert into T_reserveret_tid(660, '2006-12-23')

Dvs. vi nu har en tabel med reserverede tider med en dato på. I ovenstående tilfælde kl. 10:00-10.30 d. 20. dec og kl. 1500-1515, og igen den 23.dec kl. 11:00 til 11:15.

Så vil jeg i den procedure jeg lavede tidligere markere de reserverede tider fra arbejdstabellen T_time, altså hvis kunden skal bruge tilgængelige tider d. 20.dec, se nedenstående procedure ændring
Jeg laver en temptabel udfra T_time og opdaterer den med de reserverede tider og dato.

Husk at T_time nu skal indeholde alle tider fra åbningstid til lukketid for at de tilgængelige tider kan listes fra den :-)

**************************
alter proc sp_available_times
    @required_period int,
    @res_dato datetime

as
begin

    declare @id int
    declare @shorttime int
    declare @is_taken int
    declare @periodcounter int
   
    set @periodcounter = 0

-- her er ændringen...
    select * into #tmptime from T_time
    update T_time set is_taken = 1 where shorttime in(select shorttime from      T_reserveret_tid where res_date = @res_dato )

    select @id = max(id) from #tmptime
   
    create table #avatime
    (
    id int, shorttime int, is_taken bit
    )
   
    while @id > 0
    begin
        select @shorttime = shorttime, @is_taken = is_taken from #tmptime where id = @id
       
        if @is_taken = 0
            set @periodcounter = @periodcounter + 1
   
   
        if @is_taken = 1
            set @periodcounter = 0
   
   
        if @periodcounter >= @required_period
                insert into #avatime select * from #tmptime where id = @id
        set @id = @id -1
   
    end
select * from #avatime order by id
end

Så vil vi gerne have tilgængelige tider, hvis der skal reserveres for 30 min (2*15):

exec sp_available_times 2, '2006-12-20'

Jeg kan faktisk godt li denne her løsning, det giver god mening at have tabellen T_reserveret_tid, den kan altid udvides med flere kolonner uden det for betydning for selve reservationslogikken.

Jeg håber det giver lidt mere mening nu :-)

Det ser næsten fornuftigt ud. Jeg har afprøvet din sproc, men sådan som det er lige nu så er is_taken ikke dynamisk. Jeg tænkte så følgende:

Jeg har de her to tabeller:
den første med alle arbejdstider:

tbl_tid
-------
tidID int
tidspunkt varchar(5)
tidMinut int <--svarende til din shorttime

den næste er det aktuelle reserveringer

tbl_reservering
---------------
resID int
FK_KundeID int
FK_MedarbejderYdelse int
BestilDatoTid datetime <--det tidspunkt reserveringen er foretaget
FK_tidID int <--tidspunktet (ID) for ydelsen skal foretages
ResDato datetime <--dato for ydelsen skal foretages


Min tanke er jo så, i stedet for at sætte is_taken "permanent", om man i sprocen kunne lave en IF-sætning der tjekker om fk_TidID er i tbl_reservering. Hvis den er
så skal man i en temp_Tid har en kolonne der hedder is_taken.

lidt ala (jeg ved ikke om det er sådan her):
          if tbl_Reservering.FK_TidID=tbl_Tid.tidID where tbl_reservering.resdato=@resdato
              #temp_Tid.is_taken=1
          Else
              #temp_Tid.is_taken=0

(Jeg programmerer i VB.NET, derfor denne if-struktur :) )

temp_Tid skal så creates så den har de nødvendige kolonner.

Jeg tror at hvis det bliver på den måde, så vil dit forslag virke med mine tabeller :)

Ja sådan kunne man jo godt gøre for at tilpasse det ind i de tabeller du opererer med.

Jeg ser lige jeg har lavet en lille skrivefejl..
-- her er ændringen...
    select * into #tmptime from T_time
    update T_time set is_taken = 1 where shorttime in(select shorttime from      T_reserveret_tid where res_date = @res_dato )

Der skulle stå.
-- her er ændringen...
    select * into #tmptime from T_time
    update #tmptime set is_taken = 1 where shorttime in(select shorttime from      T_reserveret_tid where res_date = @res_dato )

Jeg ved ikke om det gør den store forskel for dig? Den skal jo kun sættes i #tmptime arbejdstabellen.
Så du kunne jo måske lave en update af #tmptime på flg. måde:

update #tmptime set is_taken = 1 where shorttime in(
                      select tidMinut from tbl_tid tt
                        inner join tbl_reservering tr
                        on tr.FK_tidID = tt.tidID
                      where ResDato = @res_dato)

Og så fortsætte med resten af proceduren.

Hej..

Er vi mon ved at være ved vejs ende?

hej janus_007

Jeg har slet ikke kigget på den pga. sygdom :( Jeg får lige kigget på den i morgen.

Ok jeg har kigget lidt på, og prøvet at eksekverer sprocen:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[sp_available_times]
    @required_period int,
    @res_dato nvarchar(256)

as
begin

    declare @id int
    declare @shorttime int
    declare @is_taken int
    declare @periodcounter int

   
    set @periodcounter = 0

    select * into #tmptime from tbl_Tid
   
    select @id = max(tidid) from #tmptime

while (select resdatotid from tbl_reservering) = @res_dato
begin
update #tmptime
set is_taken = 1
where shorttime in(select shorttime from tbl_tid tt
                        inner join tbl_reservering tr
                        on tr.FK_tidID = tt.tidID
                      where ResDatoTid = @res_dato)
  end 

    create table #avatime
    (
    id int, shorttime int, is_taken bit
    )
   
    while @id > 0
    begin
        select @shorttime = shorttime, @is_taken = is_taken from #tmptime where tidid = @id
       
        if @is_taken = 0
            set @periodcounter = @periodcounter + 1
   
   
        if @is_taken = 1
            set @periodcounter = 0
   
   
        if @periodcounter >= @required_period
                insert into #avatime select * from #tmptime where id = @id
        set @id = @id -1
   
    end
select * from #avatime order by id
end


Jeg får denne meddelse:

(37 row(s) affected)
Msg 512, Level 16, State 1, Procedure sp_available_times, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(0 row(s) affected)

(1 row(s) affected)

Så får jeg disse resultater:

  |id|shorttime|is_taken
--------------------------


og den her

  |Return_Value|
-----------------
  1| -6        |
-----------------

Ellers tror jeg, den er ved at være løst :D

Det lykkedes nu :D Tusind tak for hjælpen.

Den endelige sproc ser således ud:

alter proc sp_available_times
    @required_period int,
    @res_dato datetime

as
begin

    declare @id int
    declare @shorttime int
    declare @is_taken int
    declare @periodcounter int
   
    set @periodcounter = 0

-- her er ændringen...
    select * into #tmptime from tbl_Tid
    update #tmptime set is_taken = 1 where shorttime in(
                      select shorttime from tbl_tid tt
                        inner join tbl_reservering tr
                        on tr.FK_tidID = tt.tidID
                      where tr.ResDatoTid = @res_dato)

    select @id = max(tidid) from #tmptime
   
    create table #avatime
    (
    id int, tidspunkt nvarchar(5), shorttime int, is_taken bit
    )
   
    while @id > 0
    begin
        select @shorttime = shorttime, @is_taken = is_taken from #tmptime where tidid = @id
       
        if @is_taken = 0
            set @periodcounter = @periodcounter + 1
   
   
        if @is_taken = 1
            set @periodcounter = 0
   
   
        if @periodcounter >= @required_period
                insert into #avatime select * from #tmptime where tidid = @id
        set @id = @id -1
   
    end
select * from #avatime order by id
end

Det virker med at den fjerne de tider der ikke kan honorer varigheden.

Superfedt... Jeg er glad for vi fik løst opgaven på en god måde.