Good day everyone.
I have an SQL Server stored procedure that goes something like this. Ultimately it puts together multiple data items for a report.
cte_get_data1
cte_get_data2
cte_get_data3
SELECT *
FROM
cte_get_data1
inner join
cte_get_data2
inner join
cte_get_data3
Not quite that simple but you get the gist. Below, is one specific cte. I am still a newbie on SQL so don't laugh too hard. As you can see, I am counting the number of students that fall within a certain range. My overall stored procedure works great; however I need to add code that will also insert each of the person_id values into a separate table in the database for use by another program, while keeping the count intact for processing with the entire stored procedure. Any ideas? Is this not going to be possible with my current programming style of running everything within a CTE then gluing the data items together?
I know I can do an insert using output from the cte, but I cannot figure out how to do the insert from this particular cte AND STILL collect the data from all the ctes combined.
Thank you in advance for your input.
cte_census_date_enrollment(censusenrcount,Course_id)--,reason)
AS
(
select CONVERT(VARCHAR(3),COUNT(person_id))
,course_sec
from
(
select
sac_person_id as person_id
,SCS_COURSE_SECTION_ID as course_sec
from
(
SELECT
*
FROM
[COLLIVE].[dbo].[ENRL_ENROLLMENTS_Vw]
WHERE
((SECTION_CENSUS_DATE >= @QTRBEGIN) AND (SECTION_CENSUS_DATE < @NEXTQTR))
AND
(SAC_CURRENT_STATUS IN ('R','A'))
AND
SECTION_ACAD_LEVEL = 'CE'
and
SAC_CRED_TYPE <> 'SE'
and
ISNULL(SAC_REASON,'99') <> '6'
) as courtesy_layer
) as studcount
)
