Is this what is needed to encapsulate the above logic into a TVF? I'm wanting to use a like clause to pass in the variable: ' WHERE [Agent] like '%...

After a good night's sleep letting it 'incubate', I can now see where a Table Valued Function encapsulating the logic above is the way to make this versatile and reusable.

I thought that was what i was doing in my original code:

DECLARE @period INT = (SELECT period FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@yearINT = (SELECT year FROM [a2hr].[payroll].[fnGetPrevPeriodYear](GetDate())),
@PRStartDate Date,
@PREndDate Date

I'm quite...

If I wanted to do a SELECT on the resulting CTE query results on e.g. "WHERE date = '2021-07-26'  where would I place that?

OR if I wanted to add fields...

• This reply was modified 4 years, 8 months ago by DaveBriCam.

This is exactly the direction I was looking for... thanks!!

Really my only goal is to be able to be able to query the results of the code posted and select different fields and values for those fields to narrow...

This is a more elegant and more readable solution... thanks!!

I found a similar problem on another site that I modified and got to work:

DECLARE @Count2 INT = (SELECT [CallCount]
FROM [a2wh].[dbo].[CallLogCommonCount] Count1
WHERE (2-1) = ( 
SELECT COUNT(DISTINCT(Count2.[timestamp]))
FROM...

Finally got it! this works:

(SELECT Convert (varchar, (SELECT COUNT (*) FROM .[dbo].[Stage_Log] WHERE datestamp = LEFT(CONVERT(varchar, GetDate(), 120), 10))))

The query below works fine now isolated, but it is part of an email stored procedure and I'm getting the error from SQL Agent:

08/12/2021 13:11:45,tel_SMS,Error,2,GSO-SSIS-DEV,Ytel_SMS,Send success email,,Executed as user: NT...

• This reply was modified 4 years, 8 months ago by DaveBriCam.
• This reply was modified 4 years, 8 months ago by DaveBriCam.

Never mind the last crisis... fixed... and the Derived Column with just the expression worked perfectly... thanks!

Something really went bad in my VS 2017 SSDT. I went into the advanced editor for the derived column and VS closed, I reopened VS 2017 SSDT and now it...

Dumb question... I can just run the email step as a result of a successful run of the first step.

I too think I feel more comfortable keeping email notification in SQL Server rather than SSIS as I've learned that it is very versatile. The below code actually works for...

Thanks... How do I write to a file in C#? I'm really new to C#