t-sql help

Question

Post reply

t-sql help

Mh-397891

SSCertifiable

Points: 6704
More actions
April 26, 2009 at 10:09 pm

#203343

The duration for each time slot is 15 minutes, some of the patients have duration of more than 15 minutes. The overbook_flag needs to be 'Y' when the apptcount exceeds the apptlimit_for_day.
I need to get the output similar to the data in the #output table. I have provided the sample data.
Please let me know. Thanks.
--DROP TABLE #Temp1
CREATE TABLE #Temp1
(
Appointment_time CHAR(4),
Duration INT,
Appt_count INT,
ApptLimit_For_Day INT,
patientRecNum VARCHAR(15),
PatientName VARCHAR(148),
ApptCreatedDate DATETIME,
Overbook_Flag CHAR(3),
Doctor VARCHAR(200),
[ApptDate] VARCHAR(50)
)
INSERT INTO #Temp1
SELECT '0800',15,12,7,100,'patient1','2009-02-17 15:51:55.427',NULL,'Doctor1','04/27/2009'
UNION
SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'
UNION
SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'
UNION
SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370',NULL,'Doctor1','04/27/2009'
UNION
SELECT '0945',15,12,7,112,'patient5','2009-03-02 19:27:46.693',NULL,'Doctor1','04/27/2009'
UNION
SELECT '1000',15,12,7,122,'patient6','2009-03-10 09:02:17.607',NULL,'Doctor1','04/27/2009'
UNION
SELECT '0800',15,7,1,300,'patient101','2009-03-01 15:51:55.427',NULL,'Doctor2','04/27/2009'
UNION
SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323',NULL,'Doctor2','04/27/2009'
UNION
SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980',NULL,'Doctor2','04/27/2009'
UNION
SELECT '0915',15,7,1,306,'patient306','2009-03-30 17:06:19.370',NULL,'Doctor2','04/27/2009'
UNION
SELECT '0800',30,2,1,401,'patient401','2009-03-31 09:01:24.323',NULL,'Doctor1','04/28/2009'
UNION
SELECT '0815',15,2,1,402,'patient402','2009-03-31 11:01:49.980',NULL,'Doctor1','04/28/2009'
SELECT * FROM #Temp1 AS T
--DROP TABLE #output
CREATE TABLE #output
(
Appointment_time CHAR(4),
Duration INT,
Appt_count INT,
ApptLimit_For_Day INT,
patientRecNum VARCHAR(15),
PatientName VARCHAR(148),
ApptCreatedDate DATETIME,
Overbook_Flag CHAR(3),
Doctor VARCHAR(200),
[ApptDate] VARCHAR(50)
)
INSERT INTO #output
SELECT '0800',15,12,7,100,'patient1','2009-02-17 15:51:55.427',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'
UNION ALL
SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'
UNION ALL
SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'
UNION ALL
SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'
UNION ALL
SELECT '0945',15,12,7,112,'patient5','2009-03-02 19:27:46.693','Y','Doctor1','04/27/2009'
UNION ALL
SELECT '1000',15,12,7,122,'patient6','2009-03-10 09:02:17.607','Y','Doctor1','04/27/2009'
UNION ALL
SELECT '0800',15,7,1,300,'patient101','2009-03-01 15:51:55.427',NULL,'Doctor2','04/27/2009'
UNION ALL
SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0915',15,7,1,306,'patient306','2009-03-30 17:06:19.370','Y','Doctor2','04/27/2009'
UNION ALL
SELECT '0800',30,2,1,401,'patient401','2009-03-31 09:01:24.323',NULL,'Doctor1','04/28/2009'
UNION ALL
SELECT '0815',15,2,1,402,'patient402','2009-03-31 11:01:49.980','Y','Doctor1','04/28/2009'
--This should be the required output which I am expecting.
SELECT * FROM #output

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

Here it is... I think cursor is the option...

CREATE TABLE #Temp1

(

ID int Identity(1,1),

Appointment_time CHAR(4),

Duration INT,

Appt_count INT,

ApptLimit_For_Day INT,

patientRecNum VARCHAR(15),

PatientName VARCHAR(148),

ApptCreatedDate DATETIME,

Overbook_Flag CHAR(3),

Doctor VARCHAR(200),

[ApptDate] VARCHAR(50)

)

INSERT INTO #Temp1

SELECT '0800',15,12,7,100,'patient1','2009-02-17 15:51:55.427',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0945',15,12,7,112,'patient5','2009-03-02 19:27:46.693',NULL,'Doctor1','04/27/2009'

UNION

SELECT '1000',15,12,7,122,'patient6','2009-03-10 09:02:17.607',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0800',15,7,1,300,'patient101','2009-03-01 15:51:55.427',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0915',15,7,1,306,'patient306','2009-03-30 17:06:19.370',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0800',30,2,1,401,'patient401','2009-03-31 09:01:24.323',NULL,'Doctor1','04/28/2009'

UNION

SELECT '0815',15,2,1,402,'patient402','2009-03-31 11:01:49.980',NULL,'Doctor1','04/28/2009'

SELECT * FROM #Temp1 AS T order by PatientName

-- Added ID Column Here...

CREATE TABLE #output

(

ID int Identity(1,1),

Appointment_time CHAR(4),

Duration INT,

Appt_count INT,

ApptLimit_For_Day INT,

patientRecNum VARCHAR(15),

PatientName VARCHAR(148),

ApptCreatedDate DATETIME,

Overbook_Flag CHAR(3),

Doctor VARCHAR(200),

[ApptDate] VARCHAR(50)

)

INSERT INTO #output

SELECT '0800',15,12,7,100,'patient1','2009-02-17 15:51:55.427',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'

UNION ALL

SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'

UNION ALL

SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'

UNION ALL

SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370','Y','Doctor1','04/27/2009'

UNION ALL

SELECT '0945',15,12,7,112,'patient5','2009-03-02 19:27:46.693','Y','Doctor1','04/27/2009'

UNION ALL

SELECT '1000',15,12,7,122,'patient6','2009-03-10 09:02:17.607','Y','Doctor1','04/27/2009'

UNION ALL

SELECT '0800',15,7,1,300,'patient101','2009-03-01 15:51:55.427',NULL,'Doctor2','04/27/2009'

UNION ALL

SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0915',15,7,1,306,'patient306','2009-03-30 17:06:19.370','Y','Doctor2','04/27/2009'

UNION ALL

SELECT '0800',30,2,1,401,'patient401','2009-03-31 09:01:24.323',NULL,'Doctor1','04/28/2009'

UNION ALL

SELECT '0815',15,2,1,402,'patient402','2009-03-31 11:01:49.980','Y','Doctor1','04/28/2009'

-- Your Output ...

SELECT * FROM #output order by PatientName

CREATE TABLE #output2

(

ID int ,

Appointment_time CHAR(4),

Duration INT,

Appt_count INT,

ApptLimit_For_Day INT,

patientRecNum VARCHAR(15),

PatientName VARCHAR(148),

ApptCreatedDate DATETIME,

Overbook_Flag CHAR(3),

Doctor VARCHAR(200),

[ApptDate] VARCHAR(50)

)

--- Cursor to do this...

Declare @vAppointment_time varchar(100), @vDuration int, @vID int

Declare C1 Cursor For Select Appointment_time ,Duration, ID from #Temp1

Open C1

Fetch Next from C1 into @vAppointment_time, @vDuration, @vID

while @@Fetch_Status = 0

begin

while @vDuration 0

begin

Insert into #output2

Select @vID,Appointment_time ,Duration,

Appt_count ,

ApptLimit_For_Day ,

patientRecNum ,

PatientName ,

ApptCreatedDate ,

Overbook_Flag ,

Doctor ,

[ApptDate] from #Temp1

where ID = @vID

Set @vDuration = @vDuration - 15

end

Fetch Next from C1 into @vAppointment_time, @vDuration, @vID

end

Deallocate C1

--- Required Output

SELECT * FROM #output2 order by PatientName

drop Table #Temp1

drop table #output

drop table #output2

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 2

Not really clear what you're asking, but try this

WITH Numbers AS (SELECT number

FROM master.dbo.spt_values

WHERE type='P')

SELECT Appointment_time,Duration,Appt_count,ApptLimit_For_Day,patientRecNum,

PatientName,ApptCreatedDate,

CASE WHEN ROW_NUMBER() OVER(PARTITION BY Doctor,ApptDate ORDER BY Appointment_time) > ApptLimit_For_Day THEN 'Y' END AS Overbook_Flag,

Doctor,[ApptDate]

FROM #Temp1

INNER JOIN Numbers ON Number BETWEEN 1 AND Duration/15

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

Not really clear what you're asking, but try this
WITH Numbers AS (SELECT number
FROM master.dbo.spt_values
WHERE type='P')
SELECT Appointment_time,Duration,Appt_count,ApptLimit_For_Day,patientRecNum,
PatientName,ApptCreatedDate,
CASE WHEN ROW_NUMBER() OVER(PARTITION BY Doctor,ApptDate ORDER BY Appointment_time) > ApptLimit_For_Day THEN 'Y' END AS Overbook_Flag,
Doctor,[ApptDate]
FROM #Temp1
INNER JOIN Numbers ON Number BETWEEN 1 AND Duration/15

Well, Thanks Mark. Actually I was thinking How to repeat the records on the basis of the integer value of column. It really didnt came into my mind and i opted to go for cursors...

I hope the query resolves the OP issue...

Using the Tally table, you can also go through this query (as I know the trick now...:-P),

SELECT * FROM #Temp1

Inner join tblTally on N Between 1 and Duration/15

Order by PatientName

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 4

Thanks for the reply. I am getting the below error on running the below code:

Error: Incorrect syntax near 'Numbers'.

master.dbo.spt_values: please tell me about this table

I googled it and it says that table is a lookup table. What are type = P,

low, high, status columns of that table?

CREATE TABLE #Temp1

(

ID int Identity(1,1),

Appointment_time CHAR(4),

Duration INT,

Appt_count INT,

ApptLimit_For_Day INT,

patientRecNum VARCHAR(15),

PatientName VARCHAR(148),

ApptCreatedDate DATETIME,

Overbook_Flag CHAR(3),

Doctor VARCHAR(200),

[ApptDate] VARCHAR(50)

)

INSERT INTO #Temp1

SELECT '0800',15,12,7,100,'patient1','2009-02-17 15:51:55.427',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0815',45,12,7,105,'patient2','2009-02-27 09:01:24.323',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0900',45,12,7,106,'patient3','2009-02-27 09:01:49.980',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0915',45,12,7,107,'patient4','2009-03-02 17:06:19.370',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0945',15,12,7,112,'patient5','2009-03-02 19:27:46.693',NULL,'Doctor1','04/27/2009'

UNION

SELECT '1000',15,12,7,122,'patient6','2009-03-10 09:02:17.607',NULL,'Doctor1','04/27/2009'

UNION

SELECT '0800',15,7,1,300,'patient101','2009-03-01 15:51:55.427',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0815',45,7,1,301,'patient103','2009-03-01 16:01:24.323',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0845',30,7,1,305,'patient104','2009-03-15 09:01:49.980',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0915',15,7,1,306,'patient306','2009-03-30 17:06:19.370',NULL,'Doctor2','04/27/2009'

UNION

SELECT '0800',30,3,1,401,'patient401','2009-03-31 09:01:24.323',NULL,'Doctor1','04/28/2009'

UNION

SELECT '0815',15,3,1,402,'patient402','2009-03-31 11:01:49.980',NULL,'Doctor1','04/28/2009'

UNION

SELECT '0800',15,3,3,502,'patient502','2009-02-28 8:01:49.980',NULL,'Doctor3','04/29/2009'

UNION

SELECT '0815',15,3,3,503,'patient503','2009-02-28 11:01:49.980',NULL,'Doctor3','04/29/2009'

UNION

SELECT '0845',15,3,3,510,'patient510','2009-03-01 08:01:49.980',NULL,'Doctor3','04/29/2009'

UNION

SELECT '0800',15,1,1,610,'patient610','2009-03-01 11:01:55.980',NULL,'Doctor4','04/29/2009'

WITH Numbers AS

(

SELECT number

FROM master.dbo.spt_values

WHERE type='P'

)

SELECT Appointment_time,Duration,Appt_count,ApptLimit_For_Day,patientRecNum,

PatientName,ApptCreatedDate,

CASE WHEN ROW_NUMBER() OVER(PARTITION BY Doctor,ApptDate ORDER BY Appointment_time) > ApptLimit_For_Day THEN 'Y' END AS Overbook_Flag,

Doctor,[ApptDate]

FROM #Temp1

INNER JOIN Numbers ON number BETWEEN 1 AND Duration/15

GSquared SSC Guru Points: 260824 More actions · Answer 5

Put a semicolon in front of "WITH", so it's ";WITH". That should solve your syntax error. Common Table Expressions have to have a semicolon before them, unless they are the first piece of the script.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 6

Thanks it works.

master.dbo.spt_values: please tell me about this table

I googled it and it says that table is a lookup table. What are type = P,

low, high, status columns of that table?

GSquared SSC Guru Points: 260824 More actions · Answer 7

It's an undocumented table that SQL Server uses for it's own stuff. Basically, don't even try to change anything in it, but it can come in handy for generating a sequence of integers.

Personally, rather than relying on an undocumented system table, I create a Numbers table on each server that I work with.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 8

GSquared (4/28/2009)
It's an undocumented table that SQL Server uses for it's own stuff. Basically, don't even try to change anything in it, but it can come in handy for generating a sequence of integers.
Personally, rather than relying on an undocumented system table, I create a Numbers table on each server that I work with.

How about the columns of that table:

type = P, low, high, status columns of that table?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Mh (4/29/2009)
GSquared (4/28/2009)
It's an undocumented table that SQL Server uses for it's own stuff. Basically, don't even try to change anything in it, but it can come in handy for generating a sequence of integers.
Personally, rather than relying on an undocumented system table, I create a Numbers table on each server that I work with.
How about the columns of that table:
type = P, low, high, status columns of that table?
Thanks.

I think your best bet at this time is to simply ignore the contents of the table. It was simply being used to provide you with a list of ordered numbers to assist you with your original problem.