Populating textboxes with appointments from database

[Mustaphi]

Hello again VbForums
I wish the appropriate forum for my needs is Database Development.
My application is intended to organize appointments for doctors:
The day is divided into 3 periods: (Morning- Afternoon and evening)
Each period is allocated a set of time by a doctor.
For example: Morning: 240 min
Afternoon: 300 mn
When a doctor sets an appointment for a patient, he sets the period (morning or afternoon) and he also sets the time that the appointment would take. (20 mn or 30mn)
This time should be subtracted from the overall time devoted for the period of morning or afternoon.
eg: Morning = 240 mn - 20 mn for the first appointment = 220 mn (time left for remaining appointments for a given day)
When the time devoted for appointment is equal to the time assigned to the period (Morning), the doctor cannot set anymore appointments in this day in the slot time of the morning.
For this I have 3 tables:



Then I ran this query:

Select sum(Duration) as Duration, period, Date_appoint, COUNT(Date_appoint) AS AppCount, Period _tbl.ID, WorkTime_tbl.Period_ID,Full_Duration from WorkTime_tbl  " & _
" inner join Period _tbl on WorkTime_tbl.Period_ID = Period _tbl.ID " & _
" inner join Appointment_tbl on WorkTime_tbl.Period_ID = Appointment_tbl.Period_ID group BY Period _tbl.ID
      
Do While Not Rs.EOF

Text1(Day(Rs!Date_appoint)).Text = Rs!AppCount & " RDV" & vbNewLine & _
 "Period" & "           " & "Time left" & vbNewLine & _
Rs!Period & "          " & Rs!Duration - Rs!Full_Duration
Rs.MoveNext
Loop
End If

The output is this:




but I wish to get this output


Your help is very much appreciated
thank you in advance

[Zvoni]

1) You don't need to separate Slot_time and Worktime. Those belong together in one Table
2) You need a "Calendar-Table". You will have days where there is no appointment set, yet
3) You need a Left Join between Appointments and that "Calendar"
4) Counting how many Appointemnts there are for a day needs to be a separate Query. You can't do that in one Query

Here's an example for SQLite:
https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/0

Setup

CREATE TABLE 'slot_time' (
  ID Integer,
  Period TEXT);
  
CREATE TABLE 'WorkTime' (
  ID Integer,
  Period_ID Integer,
  Time_Start TEXT,
  Time_End TEXT,
  Full_Duration INTEGER);

CREATE TABLE 'Appointments' (
  ID Integer,
  Patient_ID Integer,
  Date_Appoint TEXT,
  Period_ID Integer,
  Duration Integer);
  
INSERT INTO 'slot_time' VALUES(1, 'Morning');
INSERT INTO 'slot_time' VALUES(2, 'Afternoon');
INSERT INTO 'slot_time' VALUES(3, 'Evening');

INSERT INTO 'WorkTime' VALUES(1,1, '08:00', '12:00', 240);
INSERT INTO 'WorkTime' VALUES(2,2, '13:00', '17:00', 240);
INSERT INTO 'WorkTime' VALUES(3,3, '17:00', '22:00', 300);

INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
INSERT INTO 'Appointments' VALUES(3,2, '2024-04-24', 2, 30);

Query

WITH
    DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),
    TT AS (SELECT WT.ID, ST.Period, WT.Full_Duration
           FROM slot_time AS ST 
           INNER JOIN WorkTime AS WT ON ST.ID=WT.Period_ID),
    DD AS (SELECT DT.AppDate, TT.Period, TT.ID As TTID, TT.Full_Duration FROM DT 
           INNER JOIN TT ON 1=1),
    AC AS (SELECT Date_Appoint, Period_ID, COUNT(*) AS AppCount
           FROM Appointments GROUP BY Date_Appoint, Period_ID)

SELECT DD.AppDate, DD.Period, COALESCE(AC.AppCount,0) AS AppCount,
DD.Full_Duration-COALESCE(A.Duration,0) As remain 
FROM DD 
LEFT JOIN Appointments AS A ON A.Date_Appoint=DD.AppDate AND A.Period_ID=DD.TTID
LEFT JOIN AC ON AC.Date_Appoint=A.Date_Appoint AND AC.Period_ID=A.Period_ID
GROUP BY DD.AppDate, DD.Period
ORDER BY DD.AppDate, DD.TTID

Returns

AppDate	Period	AppCount	remain
2024-04-17	Morning	1	220
2024-04-17	Afternoon	1	210
2024-04-17	Evening	0	300
2024-04-24	Morning	0	240
2024-04-24	Afternoon	1	210
2024-04-24	Evening	0	300

[Mustaphi]

1) You don't need to separate Slot_time and Worktime. Those belong together in one Table
2) You need a "Calendar-Table". You will have days where there is no appointment set, yet
3) You need a Left Join between Appointments and that "Calendar"
4) Counting how many Appointemnts there are for a day needs to be a separate Query. You can't do that in one Query

Here's an example for SQLite:
https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/0

Setup

CREATE TABLE 'slot_time' (
  ID Integer,
  Period TEXT);
  
CREATE TABLE 'WorkTime' (
  ID Integer,
  Period_ID Integer,
  Time_Start TEXT,
  Time_End TEXT,
  Full_Duration INTEGER);

CREATE TABLE 'Appointments' (
  ID Integer,
  Patient_ID Integer,
  Date_Appoint TEXT,
  Period_ID Integer,
  Duration Integer);
  
INSERT INTO 'slot_time' VALUES(1, 'Morning');
INSERT INTO 'slot_time' VALUES(2, 'Afternoon');
INSERT INTO 'slot_time' VALUES(3, 'Evening');

INSERT INTO 'WorkTime' VALUES(1,1, '08:00', '12:00', 240);
INSERT INTO 'WorkTime' VALUES(2,2, '13:00', '17:00', 240);
INSERT INTO 'WorkTime' VALUES(3,3, '17:00', '22:00', 300);

INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
INSERT INTO 'Appointments' VALUES(3,2, '2024-04-24', 2, 30);

Query

WITH
    DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),
    TT AS (SELECT WT.ID, ST.Period, WT.Full_Duration
           FROM slot_time AS ST 
           INNER JOIN WorkTime AS WT ON ST.ID=WT.Period_ID),
    DD AS (SELECT DT.AppDate, TT.Period, TT.ID As TTID, TT.Full_Duration FROM DT 
           INNER JOIN TT ON 1=1),
    AC AS (SELECT Date_Appoint, Period_ID, COUNT(*) AS AppCount
           FROM Appointments GROUP BY Date_Appoint, Period_ID)

SELECT DD.AppDate, DD.Period, COALESCE(AC.AppCount,0) AS AppCount,
DD.Full_Duration-COALESCE(A.Duration,0) As remain 
FROM DD 
LEFT JOIN Appointments AS A ON A.Date_Appoint=DD.AppDate AND A.Period_ID=DD.TTID
LEFT JOIN AC ON AC.Date_Appoint=A.Date_Appoint AND AC.Period_ID=A.Period_ID
GROUP BY DD.AppDate, DD.Period
ORDER BY DD.AppDate, DD.TTID

Returns

AppDate	Period	AppCount	remain
2024-04-17	Morning	1	220
2024-04-17	Afternoon	1	210
2024-04-17	Evening	0	300
2024-04-24	Morning	0	240
2024-04-24	Afternoon	1	210
2024-04-24	Evening	0	300

I greatly appreciate your help
However your code looks so advanced and I have a little problem to understand this part of the code:

DT(AppDate) AS (VALUES('2024-04-17'),('2024-04-24')),

The dates ('2024-04-17'),('2024-04-24') are just sample examples.
What if I have dozens of appointment dates in my table?

INSERT INTO 'Appointments' VALUES(1,1, '2024-04-17', 1, 20);
INSERT INTO 'Appointments' VALUES(2,2, '2024-04-17', 2, 30);
INSERT INTO 'Appointments' VALUES(3,3, '2024-04-24', 2, 30);
INSERT INTO 'Appointments' VALUES(4,4, '2024-04-25', 1, 15);
INSERT INTO 'Appointments' VALUES(5,5, '2024-04-28', 1, 20);

I attempted to do the following but I need your confirmation.

WITH DT AS (SELECT Date_Appoint  as AppDate FROM Appointments  " & _
        "   INNER JOIN WorkTime  ON Appointments.Period_ID=WorkTime.Period_ID),

Thank you for confirming this point

[Mustaphi]

Another issue is that
On my form , I have an array of 31 texboxes.
I need to display the number of appointments , the slot time or period and time remained on the corresponding textboxes.
the slot time where there is no appointments are ignored.
I did as follows but no success

Do While Not Rs.EOF
Labl1(Day(Rs!AppDate)).Text = _
"Period" & "                   " & "Time left" & vbNewLine & _
Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
Rs.MoveNext
Loop
End If

this is what I"m having:

For a table of appointment like this one

INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);"

What I dream to produce is this outcome

[Zvoni]

btw: Which DBMS are you using?
My Example was for SQLite

EDIT: And your sample is wrong.
According to your INSERTS, you don't have any appointments in the evening

[Mustaphi]

btw: Which DBMS are you using?
My Example was for SQLite

EDIT: And your sample is wrong.
According to your INSERTS, you don't have any appointments in the evening

I'm using SQLITE too and RC5

INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);

i believe 2 refers to the evening .Isn't it?

[Zvoni]

I'm using SQLITE too and RC5

INSERT Into Appointments VALUES(1,1, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(2,2, '2024-04-17', 1, 20);"
INSERT Into Appointments VALUES(3,2, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(4,4, '2024-04-17', 2, 30);"
INSERT Into Appointments VALUES(5,5, '2024-04-24', 1, 20);"
INSERT Into Appointments VALUES(6,6, '2024-04-24', 2, 20);"
INSERT Into Appointments VALUES(7,7, '2024-04-24', 2, 20);

i believe 2 refers to the evening .Isn't it?

You should know your own Data.
"2" refers to Afternoon

EDIT: Here's an updated version of how i would do it
https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/1

[Mustaphi]

You should know your own Data.
"2" refers to Afternoon

EDIT: Here's an updated version of how i would do it
https://www.db-fiddle.com/f/gbu7v9msesk9qxBLQKSEtU/1

Thank you for taking the trouble to help me and I 'm very sorry for my lack of concentration.
I tested the last code on a flexgrid and everything works like a charm.
But when running the code on textboxes, it is showing only one slot time.

WITH
	CT AS (SELECT DT.ID, DT.CalDate, ST.Period, ST.ID As Period_ID, ST.Full_Duration, ST.Time_Start 
           FROM Calendar AS DT INNER JOIN slot_time AS ST ON 1=1
           ORDER BY DT.CalDate, ST.Time_Start)

SELECT CT.CalDate, CT.Period, COUNT(AP.ID) As AppCount, AP.Cal_ID, CT.Full_Duration-SUM(COALESCE(AP.Duration,0))  As Remain
FROM CT 
LEFT JOIN Appointments AS AP ON AP.Cal_ID=CT.ID AND AP.Period_ID=CT.Period_ID
WHERE strftime('%Y',CT.CalDate) = '" & Format(Date, "yyyy") & "' AND  strftime('%m',CT.CalDate) = '" & Format(Date, "mm")
GROUP BY CT.CalDate, CT.Period 
ORDER BY CT.CalDate, CT.Time_Start
Do While Not Rs.EOF
text1(Day(Rs!Cal_ID)).Text = _
"Period" & "                   " & "Time left" & vbNewLine & _
Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
Rs.MoveNext
Loop

The first image is not clear enough this is a more clear image.

[Zvoni]

Because you are overwriting it
(untested)

If text1(Day(Rs!Cal_ID)).Text = "" Then 
   text1(Day(Rs!Cal_ID)).Text = "Period" ...blablablabla
Else
   text1(Day(Rs!Cal_ID)).Text = text1(Day(Rs!Cal_ID)).Text & vbNewLine & "Period" ...blablabla
End If

EDIT: Or the way i would do it
(Untested)

Dim i As Long
Dim ID As Long
Dim r(0 To 3) As String

Do While Not Rs.EOF
   ID = Day(Rs!Cal_ID)
   r(0)="Period                   Time left"
   For i=1 To 3
      r(i)=Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
      Rs.MoveNext
   Next
   text1(ID).Text = Join(r, vbNewLine)
Loop

[Mustaphi]

Because you are overwriting it
(untested)

If text1(Day(Rs!Cal_ID)).Text = "" Then 
   text1(Day(Rs!Cal_ID)).Text = "Period" ...blablablabla
Else
   text1(Day(Rs!Cal_ID)).Text = text1(Day(Rs!Cal_ID)).Text & vbNewLine & "Period" ...blablabla
End If

EDIT: Or the way i would do it
(Untested)

Dim i As Long
Dim ID As Long
Dim r(0 To 3) As String

Do While Not Rs.EOF
   ID = Day(Rs!Cal_ID)
   r(0)="Period                   Time left"
   For i=1 To 3
      r(i)=Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
      Rs.MoveNext
   Next
   text1(ID).Text = Join(r, vbNewLine)
Loop

Thank you for such a wonderful contribution
The second loop did not work but the first one worked like a charm.
I modified a bit your code to meet my needs:

CT AS (SELECT DT.ID, DT.CalDate, ST.Period, ST.ID As Period_ID, ST.Full_Duration, ST.Time_Start 
           FROM Calendar AS DT INNER JOIN slot_time AS ST ON 1=1
           ORDER BY DT.CalDate, ST.Time_Start)

SELECT CT.CalDate, CT.Period, COUNT(AP.ID) As AppCount, AP.Cal_ID, CT.Full_Duration-SUM(COALESCE(AP.Duration,0))  As Remain
FROM CT 
LEFT JOIN Appointments AS AP ON AP.Cal_ID=CT.ID AND AP.Period_ID=CT.Period_ID
WHERE strftime('%Y',CT.CalDate) = '" & Format(Date, "yyyy") & "' AND  strftime('%m',CT.CalDate) = '" & Format(Date, "mm")
And AP.Cal_ID IN ( SELECT CT.ID FROM CT )
GROUP BY CT.CalDate, CT.Period 
ORDER BY CT.CalDate, CT.Time_Start
Do While Not Rs.EOF
text1(Day(Rs!Cal_ID)).Text = _
"Period" & "                   " & "Time left" & vbNewLine & _
Rs!Period & "    " & Rs!AppCount & " RDV" & "    " & Rs!remain
Rs.MoveNext
Loop

text1(Day(Rs!CalDate)).Text = text1(Day(Rs!Cal_ID)).Text

I am so very grateful for your time.