CSV into rows w/ grouping - 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)

CSV into rows w/ grouping

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-27 : 13:02:53

I have the following field called DStr CHAR(96) representing a day divided into 15 minute segments, each character representing one segment, holding the number of appointments for that slot/segment. A typical DStr would look like this:
00000000000000000000000000000000000111111111111111100002222222233333333000000000000000000000000
I would like the data like this:
Start End Units
540 780 1
840 960 2
960 1080 3

There is the possibility of the data looking like this:
00000000000000000000000000000000000111122221111111100002222222233333333000000000000000000000000
resulting in a unit overlap, which must be separated.
Start End Units
540 600 1
600 660 2
660 780 1
840 960 2
960 1080 3

Thanks.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 13:38:31

I'm sorry, but I'm lost. What do Start and End represent?
I thought it was clock time, then I saw 7:80? And in mentioning overlap, where you have 2 values for a period, wouldn't the last one win?

Let me know and maybe I can help

Thanks

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-27 : 13:43:48

Use a tally table to transform your field into multiple rows:

(in this case the tally table is called Numbers with a field called Number containing numbers from 1 .... whatever)

SELECT Numbers.Number, SUBSTRING(Dstr,Number,1) as Appointments
FROM YourTable
INNER JOIN Numbers ON Number <= Len(Dstr)

Then from there, look at my article on detecting streaks in your data. That should give you all the info you need.

The article is at:http://www.sqlteam.com/item.asp?ItemID=12654

- Jeff

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-27 : 14:03:06

Thanks, Jeff, very much. I had already done the tally table idea, but wasn't sure how to continue. It's funny you should be mentioning this idea: I was just reading the chapter on runs in the Guru's Guide To Transact SQL and was wondering "When do you ever use this stuff?", but it ends up being in my own backyard...

The times represent the number of minutes since midnight, hence the first table of times is this:
Start End
9:00 AM 1:00 PM
2:00 PM 4:00 PM
4:00 PM 6:00 PM

Also, this isn't really a matter of "winning/losing" the values for a period. To give you an example: Dr. A does initial visits between 9:00 AM and 10:00 AM, which is long, one per 15 minutes. Between 10:00 AM and 11:00 he does lab procedures like blood tests, 2 per 15 minutes. Then he does more visits until lunch, at 1:00 PM, then back to work at 2:00 doing follow-up visits, then after 4:00 he does vaccinations, 3 per 15 minutes. Seems sensible.

Sarah Berger MCSD

Edited by - simondeutsch on 02/27/2003 14:05:45

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-27 : 14:18:00

Hopefully it will work for you. As for winning/losing, even though I only used 2 values (W and L) in the article my technique will actually work for any number of values you wish to track runs for.

Good luck !

I have browsed the Guru's Guide to T-SQL, but hadn't seen the article on runs -- does the book use the same techinque? I hope not, I thought I had come up with it myself !

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 14:43:17

OK I have 1 quest though if offsets are 15 minutes, then how can you have the same # of minutes for a strat and end time? Subtrac 1 when the visits change?

Anyway this should get you close:(Is this even what you're looking for though?). How about:

Declare @x char(96), @y int, @Start int, @End Int, @visits Int
Declare @t table (StartV Int, EndV Int, Visits Int)

Select @x = '00000000000000000000000000000000000111122221111111100002222222233333333000000000000000000000000'
, @y = 1, @Start = 0, @End = 0 , @visits = 0

While @y < 97

BEGIN
If Substring(@x,@y,1) <> 0
BEGIN
Select @Start = 15 * @y, @visits = Convert(Int,Substring(@x,@y,1))
While @Visits = Substring(@x,@y,1)
BEGIN Select @y = @y + 1 END
Select @End = 15 * @y
Insert Into @t (StartV, EndV, Visits) Values (@Start, @End, @Visits)
END
Select @y = @y + 1
END

Select * From @t

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-27 : 15:40:02

Thanks. Your solution is correct. As to the question: Yes, to verify that an appointment must end before a specified time, I subtract 1 (or 15, depending if I'm subtracting 1 segment or 15 minutes).
I ended up going with Jeff's solution of using group by with a run grouping column because it is easier to implement the way I am doing now, with nested derived tables, without having to do loops.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 15:41:21

Can you post the code?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-02-27 : 16:21:24

CREATE PROCEDURE DrMonthlySchedule @Dr CHAR(10),@From DATETIME,@To DATETIME
AS
SET NOCOUNT ON
DECLARE @Date DATETIME,@MinDay TINYINT,@Negate SMALLINT,@IsResource BIT
SET @Date = @From
CREATE TABLE #DaysInMonth (MonthDay DATETIME, DayOfMonth SMALLINT)
CREATE TABLE #Numbers(Num TINYINT)
CREATE TABLE #Temp(Dr CHAR(10),Date DATETIME,Description varchar(150),CloseDay TINYINT,DayOfWeek TINYINT,Units TINYINT,Start INT)
SET @Minday = 1
WHILE @MinDay <= 96
BEGIN
INSERT #Numbers VALUES(@Minday)
SET @MinDay = @Minday + 1
END
WHILE @Date < @To --Increment date in loop to fill temp table with all dates in month
BEGIN
INSERT INTO #DaysInMonth VALUES (@Date,DATEPART(dd,@date))
SET @Date = DATEADD(d,1,@Date)
END
SET @MinDay = (SELECT DATEPART(dw,MIN(Monthday)) FROM #DaysInMonth) --Check if the month starts on a Sunday
IF @MinDay > 1 --If not a Sunday, introduce negative rows into the #table
BEGIN
SET @Negate = -1
WHILE 0 = 0
BEGIN
INSERT INTO #DaysInMonth VALUES(NULL, @MinDay - @MinDay + @Negate)
SET @Negate = @Negate - 1
IF @Negate + @MinDay = 0 BREAK
END
END
IF EXISTS(SELECT Code FROM Doctors WHERE Code = @Dr) SET @IsResource = 0 ELSE SET @IsResource = 1 --do a check to see if @Dr is a doctor or resource If a row is returned, then it must be a doctor, else a resource

INSERT INTO #Temp(Dr,Date,Description,CloseDay,DayOfWeek,Units,Start) SELECT Dr,Date,Description,CloseDay,DayOfWeek,SUBSTRING(Dstr,Num,1) AS Units,(Num - 1) * 15 AS Start FROM
(SELECT DA.Dr,DC.Date,DC.Description,(CASE WHEN DC.DoctorID IS NULL THEN DA.CloseDay ELSE DC.CloseDay END) AS CloseDay,DA.DayOfWeek,(CASE WHEN DC.DoctorID IS NULL THEN DA.DStr ELSE DC.Dstr END) AS Dstr
FROM DoctorAvailability DA LEFT JOIN DoctorChanges DC ON DC.DoctorID = DA.Dr AND DATEPART(dw,DC.Date) =
DA.DayOfWeek AND Date BETWEEN @From AND @To WHERE Dr = @Dr)DTC, #Numbers

SELECT DrName,MonthDay AS FDate,DayofMonth AS MDay,(CASE WHEN MonthDay IS NULL THEN MONTH(@From) ELSE MONTH
(MonthDay) END) AS MMonth,DayOfWeek,Dr,Description,CAST(CloseDay AS BIT) AS Closed,StartTime,EndTime,Units FROM #DaysInMonth DM LEFT JOIN
((SELECT Date AS DateOfChange,Dr,Description,Closeday,DayOfWeek,Units,MIN(Start) AS StartTime,MAX(Start) + 15 AS EndTime FROM
(SELECT Description,CloseDay,Dr,Units,Start,Date,DayOfWeek,(SELECT COUNT(*) FROM #Temp T WHERE T.Units <> Tp.Units AND T.Start <= Tp.Start AND T.DayOfWeek = Tp.
DayOfWeek) AS Rungroup FROM #Temp tp)T WHERE NOT(CloseDay = 0 AND Units = 0) GROUP BY Dr,Date,Description,CloseDay,DayOfWeek,Units))DTC
ON DM.MonthDay = DTC.DateOfChange OR DATEPART(dw,MonthDay) = DayOfWeek INNER JOIN vwDocResources D ON D.Code = DTC.Dr WHERE D.Code = @Dr ORDER BY MDay,StartTime

DROP TABLE #Temp
DROP TABLE #Numbers
DROP TABLE #DaysInMonth
SET NOCOUNT OFF
RETURN

Comment: This data is for a report that looks like a calendar page. What was happening was that if the first day of the month was a Tuesday, e.g., the report wouldn't lay out right as the 1'st of the month was in the Sunday column (1'st column). That's why there is the loop to add negative values into the resultset for the missing month.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources