Finding the first empty column in a data row

Question

I saw similar questions and most turned into arguments about table design, normalization, etc. Not all of us have the luxury of making our clients change their database designs.

Here is my dilemma. My client asks that I make it possible for their workers to be able to add call out time for the preceding day. I will provide a pop-up to permit them to enter a callout_start and callout_stop for the day before. Many already have call outs for the previous day.

CREATE TABLE MyTable(
empNo int NOT NULL,
workDate datetime NOT NULL,
callout_start1 datetime NULL,
callout_stop1 datetime NULL,
callout_start2 datetime NULL,
callout_stop2 datetime NULL,
callout_start3 datetime NULL,
callout_stop3 datetime NULL,
callout_start4 datetime NULL,
callout_stop4 datetime NULL,
callout_start5 datetime NULL,
callout_stop5 datetime NULL,
callout_start6 datetime NULL,
callout_stop6 datetime NULL,
callout_start7 datetime NULL,
callout_stop7 datetime NULL,
callout_start8 datetime NULL,
callout_stop8 datetime NULL,
callout_hours decimal(5, 2) NULL
)

I want them to be able to update the next available callouts of the previous day. IOWs - the first callout that is null. The SQL has to be generic. I am writing to a dbIsam database that will be sync'd to an MS/SQL DB via remObjects.
Thanks

Since you're having a GUI ("will provide a pop-up to permit them to enter a callout_start") you could also move that logic into your application rather than SQL. I think that will be more maintainable, than writing an SQL statement that you, in the end, cannot service. — maio290, Jul 22 '21 at 17:35
Not sure what you are talking about. The GUI is not data bound. A tech may do 20 call outs. Doubt he would remember how many. — Meta Mussel, Jul 22 '21 at 21:28

score 1 · Accepted Answer · answered Jul 22 '21 at 17:33

1

Well, you can use a complicated SQL statement to find the first null value. Then use conditional logic for updating all the columns:

with toupdate as (
      select t.*,
             (case when callout_start1 is null then 1
                   when callout_start2 is null then 2
                   . . .
              end) as which
      from t
     )
update toupdate
    set callout_start1 = (case when which = 1 then @param else callout_start1 end),
        callout_start2 = (case when which = 2 then @param else callout_start2 end),
        . . . 
    where <whatever>;

answered Jul 22 '21 at 17:33

Gordon Linoff

1,242,037
58
646
786

Not really how I will do it but I am going to following your pattern but in Delphi with a series of queries until I hit the first null callout for that day and employee ID. Its pretty ghetto but I have tested it and it works. If I were talking directly to a MS/SQL DB, I would probably write a SP and poll the record with a case expression until I found an empty slot and then update it. – Meta Mussel Jul 22 '21 at 21:32

Finding the first empty column in a data row

1 Answers1