Grant is correct about wanting to create a view in a sp but to answer...

• This reply was modified 3 years, 11 months ago by Ken McKelvey.
• This reply was modified 3 years, 11 months ago by Ken McKelvey.

On looking at this again this may be a safer approach:

WITH Rules
AS
(
   SELECT id, [value]
      ,CASE
    ...

This should cope with more edge conditions than my original query:

WITH Rules
AS
(
   SELECT id, [value]
      ,CASE
    ...

• This reply was modified 4 years ago by Ken McKelvey.
• This reply was modified 4 years ago by Ken McKelvey.

WITH Rules
AS
(

SELECT id, [value]
,CASE
WHEN 10 = SUM(CASE WHEN [value] >= 1000 THEN 1 ELSE 0 END)
OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING)
THEN 1
ELSE...

I presume you want to know why, when running under the DP300User1 context, the SP works but the query on it's own does not.

When a SP is created by default...

USE [master];
GO
CREATE DATABASE YourDB
ON 
   (FILENAME = N'D:\YourPath\DB_1.mdf')
   ,(FILENAME = N'D:\YourPath\DB_1.ldf')
FOR ATTACH;
GO

I found this article interesting not so much for Query Control but for the possibilities offered by the Gallium Data db proxy.

Do you think it would be possible to point...

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

I am glad it helped but you will need to test it very carefully before it goes near production. You should especially try to think of every boundary condition and...

• This reply was modified 4 years, 1 months ago by Ken McKelvey.
• This reply was modified 4 years, 1 months ago by Ken McKelvey.

It might be more efficient to avoid joins:

WITH Grps
AS
(
    --Use ShiftEnd as the group as the value will be needed anyway.
   ...

• This reply was modified 4 years, 1 months ago by Ken McKelvey.
• This reply was modified 4 years, 1 months ago by Ken McKelvey.

This looks like ROW_NUMBER() combined with a simple JOIN.

If you show what you have tried someone may help you.

Also, you do not want to be running anything which requires user interaction from the agent. eg pause, notepad etc.

ie The agent is running as its own process on the...

I have never used powershell with MS Access but I would be inclined to use Export-Csv which has -NoTypeInformation and -Delimiter parameters.

If you are planning for large amounts of data you might be interested in some 'light' reading:

https://www.itprotoday.com/sql-server/interval-queries-sql-server

1. Jeff recommends always using an anchor and I am not sure that @PrevDate counts.

2. It seems there can sometimes be problems with the 3 part update so it might...

• This reply was modified 4 years, 2 months ago by Ken McKelvey.