Bit of a humdinger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bit of a humdinger

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-04 : 13:15:55

Hiya,
I am trying a conversion from one table to another, with very different structures, and this is what I have hit on. I guess if I work long enough I could figure it out, but A. I'm lazy B. The SQLTeamers might like this. Here is the table Old I'm converting
(DDL)
CREATE TABLE OldSchedule (Code CHAR(10),SUNSTR CHAR(60),MONSTR CHAR(60),TUESTR CHAR(60), WEDSTR CHAR(60), THUSTR CHAR(60), FRISTR CHAR(60), SATSTR CHAR(60))
INSERT INTO OldSchedule VALUES('DR',
'UUUUUUUUUUUU33333333333333333333UUUUUUUUUUUUUUUUUUUUUUUUUUUU','UUUUUUUUUUUUUUUUUU3333333333333333333333333333UUUUUUUUUUUUUU', 'UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU', 'UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU', 'UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU','UUUUUUUUUUUU33333333333333333333UUUUUUUUUUUUUUUUUUUUUUUUUUUU','UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU')
INSERT INTO OldSchedule VALUES('WA','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU','UUUU2222222222222222UUUU22222222222222222222UUUUUUUUUUUUUUUU')
INSERT INTO OldSchedule VALUES('NYU','UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU','UUUUUUUUUUUU111111111111111111111111111111111UUUUUUUUUUUUUUU','UUUUUUUUUUUU111111111111111111111111111111111UUUUUUUUUUUUUUU','UUUUUUUUUUU1111111111111111111111111111111111UUUUUUUUUUUUUUU','UUUUUUUUUUU111111111111111111111111111111111111UUUUUUUUUUUUU','UUUUUUUUUUU111111111111111111111111UUUUUUUUUUUUUUUUUUUUUUUUU','UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU')

Trying to convert to:
CREATE TABLE NewSchedule(DrID char(10),DayOfWeek Tinyint,StartTime INT,EndTime INT, Units TINYINT)

Which should have, for each doctor, multiple rows consisting of his available time ranges. According to the OldSchedule above, each STR column consists of a day, with each character representing a 15 minute slot, starting at 6 AM. (60 * 15 = 900 mins., 6 am = 360 mins, so 360 + 900 = 1260 which is 9 pm). A 'U' is an Unavailable slot, and a number indicates the Units, or number of patients allowed per single 15 min. slot. So e.g. 'DR' should have rows in NewSchedule as follows:
'DR',1,0,540,0
'DR',1,540,840,3
'DR',1,840,1260,0
etc, although it's also OK not to enter unavailable ranges that are not sandwiched between available ranges. Maybe Graz will want to post this as a user challenge for whoever comes up with the shortest answer!

Sarah Berger MCSD

EDITED: And Reason C. The Gurus here can come up with something much better than I can!

Edited by - simondeutsch on 11/04/2002 13:22:13

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-04 : 13:42:18

create table #a(StartTime int, pos int)
insert #a select 0, 1
insert #a select 15, 2
insert #a select 30, 3
...
do this in a loop

create table #Times (StartTime int, Units int)
insert #Times
select #a.STartTime, convert(int,substring(SUNSTR, #a.pos,1))
from #a, OldSchedule
where substring(SUNSTR, #a.pos,1) <> 'U'

this should give a list of the units and times.
It is then just a matter of summing the units for adjacent time slots and filling in the unavailable slots.
And doing it for the other days.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-04 : 14:13:24

Did you check the output? It looks like this:
StartTime Units
----------- -----------
195 3
210 3
225 3
240 3
255 3
270 3
285 3
300 3
315 3
330 3
345 3
360 3
375 3

which is not correct for summing the units because you will get way too many of them, also, there isn't really any way to group this data to get the right result which is a starttime and endtime. BTW, what's interesting is that since #a has all possible times, it is no longer necessary to fill in the unavailable slots; they show as regular rows in the #Times table with a unit count of 0.
This statement gets the right result, but it's got to go further:
SELECT CODE,1,360 + ((PATINDEX('%[1-9]%',SUNSTR)- 1)* 15),360 + ((CHARINDEX('U',SUNSTR,PATINDEX('%[1-9]%',SUNSTR))- 1) * 15) ,SUBSTRING(SUNSTR,PATINDEX('%[1-9]%',SUNSTR),1) FROM OldSchedule WHERE PATINDEX('%[0-9]%',SUNSTR)> 0
because it will only return the first open range in the day. I'm thinking maybe to do STUFF on the first open range with U's and then rerun this statement again, but there should be a better way.

Sarah Berger MCSD

Edited by - simondeutsch on 11/04/2002 14:14:53

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-04 : 17:27:25

How about this:


INSERT INTO NewSchedule (DrID, DayOfWeek, StartTime, EndTime, Units)
SELECT Code, dayOfWeek, 360 + 15 * (startSlot - 1) AS startTime,
  360 + 15 * (startSlot + slotCount - 1) AS endTime, units
FROM (
    SELECT Code, dayOfWeek,
      CAST(SUBSTRING(SchedStr, startSlot + 1, 1) AS tinyint) AS units,
      startSlot,
      PATINDEX('%[^' + SUBSTRING(SchedStr, startSlot + 1, 1) + ']%',
        SUBSTRING(SchedStr, startSlot + 2, 60)) AS slotCount
    FROM (
        SELECT Code, N AS dayOfWeek,
          '0' + REPLACE(
            CASE N
              WHEN 1 THEN SUNSTR
              WHEN 2 THEN MONSTR
              WHEN 3 THEN TUESTR
              WHEN 4 THEN WEDSTR
              WHEN 5 THEN THUSTR
              WHEN 6 THEN FRISTR
              WHEN 7 THEN SATSTR
            END, 'U', '0') + '0' AS SchedStr
        FROM OldSchedule
        CROSS JOIN Numbers
        WHERE N BETWEEN 1 AND 7
      ) AS DaySchedule
    INNER JOIN (
        SELECT N AS startSlot
        FROM Numbers
        WHERE N BETWEEN 1 AND 60
      ) AS Slots
      ON SUBSTRING(SchedStr, startSlot, 1) <> SUBSTRING(SchedStr, startSlot + 1, 1)
  ) a
WHERE slotCount > 0

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-05 : 11:12:33

Whoa! Arnold!
That's beautiful! I'm still in the middle of deciphering it, but it definitely works properly.

THREE BEERS TO YOU!!

Sarah Berger MCSD

P.S S/O please tell me... How do you do those beer and yak icons?
Edited by - simondeutsch on 11/05/2002 11:13:49

Edited by - simondeutsch on 11/05/2002 11:15:09

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 11:18:13

Well, how did you do the

icons?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-05 : 11:23:34

Don't tell me its

and

!?

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-05 : 11:32:23

Okay, here goes Arnold:

!!

Now how about the drinkin-tongue smackin'-smiley?

Sarah Berger MCSD

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-05 : 11:38:48

Thanks!

The DaySchedule subquery just transposes the column-per-day
layout of the table into a row per day for each Code. While
it's doing that, it turns the U characters into 0 and adds
a 0 on each end as a sentinel -- since we don't want an
initial or final 0 to be seen as a change.

The join in subquery 'a' finds any changes between characters
in schedStr, just by testing each character with the following
character. This goes in startSlot.

The sneaky bit is the use of PATINDEX to find the first
character after startSlot that isn't the same -- this gives
us the length of the period. When startSlot refers to an
unavailable timerange at the end of the day the PATINDEX will
return 0, so these are excluded with the
WHERE slotCount > 0

[Edited for line length]

Edited by - Arnold Fribble on 11/05/2002 11:46:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources