Finding the first empty column in a data row

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

Answer

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>;