Monday, January 21, 2013 10:45 PM
;WITH Categories AS ( SELECT SUBSTRING(A.B, v.Number + 1, 1) AS NumSlots, CalendarDate, CalendarResourceId, CalendarId, ROW_NUMBER() OVER (ORDER BY CalendarId) AS RowNum FROM ( SELECT CatArray1 B, CalendarDate, CalendarResourceId, CalendarId FROM PracticeCalendar WHERE CalendarDate BETWEEN (SELECT TOP 1 CalendarDate FROM PracticeCalendar ORDER BY CalendarDate ASC) AND (SELECT TOP 1 CalendarDate FROM PracticeCalendar ORDER BY CalendarDate DESC) AND CatArray1<>'' ) A JOIN master..Spt_values v ON v.Number < DATALENGTH(a.B) / 2 WHERE v.Type = 'P' ) SELECT sb.Id as ScheduleBlockId,ac.Id as AppointmentCategoryId --INTO #SBAC FROM (SELECT CASE WHEN RowNum % 192 <> 0 THEN DATEADD(mi,(((RowNum % 192)-1)/4)*15,CONVERT(DateTime,CalendarDate)) WHEN RowNum % 192 = 0 THEN DATEADD(mi,705,CONVERT(DateTime,CalendarDate)) END AS ScheduleBlockStartTime, Category = CASE RowNum % 4 WHEN 1 THEN 'S' WHEN 2 THEN 'I' WHEN 3 THEN 'L' WHEN 4 THEN 'E' END, CalendarResourceId, CalendarId FROM Categories WHERE NumSlots<>'' ) c JOIN model.ScheduleBlocks sb on c.ScheduleBlockStartTime=sb.StartDateTime AND c.CalendarResourceId=sb.UserId JOIN model.AppointmentCategories ac on c.Category=ac.Name
I have the previous CTE, that has the NumSlots column. I'd like return (x) rows where X is the number of slots in the Query.
So Let's say the Num slots is 2, I'd like to have 2 rows where I see the ScheduleBlockId and the AppointmentCategoryId.
Tuesday, January 22, 2013 2:07 AM
In the part of "With Categories ....", you should have an union inside. See these for recursive query
Many Thanks & Best Regards, Hua Min
Tuesday, January 22, 2013 2:36 AM
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. You were not even close! Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect; you did not.
This is minimal polite behavior on SQL forums.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Why do you think that “Categories”, “generic_nbr”, “type”, etc are clear and precise names that can be maintained? And what is a “calendar_id”? How did a “name” become a “category”? They are totally different kinds of attribute properties!!
Nothing you posted makes sense in RDBMS. You play with strings like COBOL, and seem to be trying to fake a sequential file in SQL. Or build a look-up table of time slots? Who knows?
Want to try again with proper Netiquette?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL