Actually 'dose' is what the function returns

Txs - I'll try it tomorrow. BTW in current configuration (with clustered index) it's done in 0:26

I might have problems with indexing - every night more than 20 K records...

Execution plan graph

And the query:

==============

SELECT

Real_Date_Time ChartedAt

,DRUG_INFO_1

,LEFT(drug_info_1,CHARINDEX(CHAR(32),drug_info_1)) Drug

,f.Dose

, Coalesce(case charindex('MG', DRUG_INFO_1)

when 0 then null

...

OK. Table:

==============

CREATE TABLE dbo.CHRT_Tran(

SITE nvarchar(2) NULL,

LOCATION nvarchar(13) NULL,

PMP nvarchar(5) NULL,

CHARTED_DATE nvarchar(8) NULL,

CHARTED_TIME nvarchar(5) NULL,

Real_Date_Time datetime NULL,

NURSE_ID nvarchar(12) NULL,

REASON nvarchar(60) NULL,

PAT_NUM nvarchar(12) NULL,

PTNAME nvarchar(32) NULL,

SIG nvarchar(15) NULL,

SCH_PRN_TKH nvarchar(9) NULL,

UNSPECIFIED nvarchar(3) NULL,

FREQUENCY...

Take me right: the result is 15,547 rows from 9 mln rows

This table has about 9,000,000 recs.

However, when I added clustered index the time dropped to 0:32

Is it still limping?

iTVF - 1:08

Scalar- 2:07

Long live iTVF and Sean!

Sean, I hate to look stupid - but this is a quality not easy to hide ...

I was not able to use iTVF in my query ... alas ...

Ok. I'll try it (never tried before)

Ha! Sean, you elegantly rewrote my function (stollen from somwhere anyway).

I do not think that to use cte in my case is a good idea - I use this function...

Dave - thanks, it works -need to play to make sure it's ok in other strings

Thanks

(I can only be your brother, not sister - need to ask dad 😉

Thanks Sean. Will read (love charts!)

Sorry Adi, it works ... Thanks a lot!

Thank you, but this does not solve the problem - we need to include or not to include records with null values using the variable that is sent to the...