"tblstudent" already exists -- with a different number of columns -- so the create table statement is failing, but the insert still tries to execute.

If, as you say, you are querying by a single, unique PK value, the lookup should be quick...

I'll take a look at it a bit later today.

You're welcome.  Yeah, OUTER APPLY is pretty useful, like here when you kinda want a join but only want 1 row, not all of them.

;WITH CTE_ADMITS AS (
    SELECT A.*, ISNULL(LEAD(A.DISCHARGE_DATE, 1) OVER(PARTITION BY ID_NUM ORDER BY DISCHARGE_DATE), '20790601') AS NEXT_DISCHARGE
    FROM ADMITS A
)
SELECT A.*,...

IF the MMDDYYYY formatted string date is guaranteed to always be 8 characters, the following works (most of the posted code doesn't consider the inclusion of the ".txt") according...

I wasn't 100% sure if 101 and 102 could appear more than once in the risk table, so I coded it to allow that, just in case.

If each can appear...

What result do you want?  You never explicitly stated the results you want to see.

Maybe try this:

select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,case when substring(daterange,1,3) like '0-%'...

SELECT CAST(SUBSTRING((getFileName, 5, 4) + 
    LEFT(getFileName, 4) AS date) AS FileNameDate 
FROM #t

If you can afford to have CASCADE in effect, drop and recreate the index with ON DELETE CASCADE specified.

Hmm, yeah, if not, we'll have some complex code to write.  We...

If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might as...

If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might as well do...

It depends, but, yes, if this query is vital for you, then change the clustering key to ( registration_date, UserID /*assuming a unique UserID*/ ) (the PK can still be...

SQL itself will delete from the FK-related tables if you "tell" it to: that is, you set the ON DELETE CASCADE option on for that FK.