Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Recursive CTE with multiple rows

Answered Recursive CTE with multiple rows

  • Monday, January 21, 2013 10:45 PM
     
      Has Code
    Sign In to Vote
    0
     
    ;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.

     

All Replies

  • Tuesday, January 22, 2013 2:07 AM
     
     Answered
    Sign In to Vote
    0

    In the part of "With Categories ....", you should have an union inside. See these for recursive query
    http://msdn.microsoft.com/en-us/library/ms186243%28v=SQL.105%29.aspx

    http://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/

    Many Thanks & Best Regards, Hua Min


     
  • Tuesday, January 22, 2013 2:36 AM
     
     
    Sign In to Vote
    0

    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