Next higher value

[vissvess]

Hi Community experts, 

I have a scenario where the data is as follows

Sequence	Actualenddatetime	LOS
1	3/12/2020 12:44:01	L2A
2	3/12/2020 12:44:42	L2B
5	3/12/2020 12:45:02	L2A
1	3/12/2020 12:46:25	L2A
4	3/12/2020 13:44:01	L2A
3	3/12/2020 13:44:42	L2B
8	3/12/2020 12:55:02	L2A
4	3/12/2020 12:49:25	L2A

I need a calculated column which give me the next datetime for the same LOS and sequence. Say row 1 should result with 3/12/2020 12:46:25 (row 4) as it is the next datetime entry available for same LOS & same sequence.

Note: The dataset is direct query on SQL server

Going for rank method as I refer other posts in cummunity may reduce the responsiveness and increases the load on source.

Any other solution. 
I would be more thankful.

[TomMartens]

Hey @vissvess ,

I recommend avoiding a calculated column as every solution is creating a lot of stress to the underlying database, due to the necessary scanning of columns.

Instead I would create dedicated view in the source database to create the column:

SELECT
	[Sequence]
,	[ActualEndDateTime]
,	[LOS]
,	LEAD([ActualEndDateTime], 1) over(partition by [LOS] , [Sequence] order by [ActualEndDateTime]) as nextDateTime
  FROM [develop].[dbo].[Table_3]

The result of the above query creates this result:

Hopefully, this provides some additional ideas even if it is not a direct answer.

Regards,

Tom

[vissvess]

Thansk Tom Martens,

I tried the same way.
But the hurdle is that I only have read access with the database.

So, I thought to develop a native query where I can push the transformation to the SQL back during querying.

Thus, I developed a sql code as follows.

IF OBJECT_ID('TEMPDB.DBO.#MINIKANBANJOB','U') IS NOT NULL
   DROP TABLE #MINIKANBANJOB;

SELECT TOP(100) KANBAN
    ,CASE
        WHEN PLANACTIVITYNAME LIKE '%1A%' THEN 'Line 1A'
        WHEN PLANACTIVITYNAME LIKE '%1B%' THEN 'Line 1B'
        WHEN PLANACTIVITYNAME LIKE '%1C%' THEN 'Line 1C'
        WHEN PLANACTIVITYNAME LIKE '%1D%' THEN 'Line 1D'
        WHEN PLANACTIVITYNAME LIKE '%2A%' THEN 'Line 2A'
        WHEN PLANACTIVITYNAME LIKE '%2B%' THEN 'Line 2B'
        WHEN PLANACTIVITYNAME LIKE '%3A%' THEN 'Line 3A'
        WHEN PLANACTIVITYNAME LIKE '%3B%' THEN 'Line 3B'
        WHEN PLANACTIVITYNAME LIKE '%2N%' THEN 'Line 2B'
        ELSE NULL
        END AS 'LOS'
    ,PLANACTIVITYNAME
    ,CASE
        WHEN PLANACTIVITYNAME LIKE '%INDUCT%' OR PLANACTIVITYNAME LIKE '%Z1%' THEN 20
        WHEN PLANACTIVITYNAME LIKE '%EOL%' THEN 100
        WHEN PLANACTIVITYNAME LIKE '%BOXING%' THEN 90
        WHEN PLANACTIVITYNAME LIKE '%HIPOT%' THEN 80
        WHEN PLANACTIVITYNAME LIKE '%BURN%' AND NOT PLANACTIVITYNAME LIKE '%CFI%' THEN 50
        WHEN PLANACTIVITYNAME LIKE 'L1_-DT-Z5' 
            OR PLANACTIVITYNAME LIKE '%WKS-Z7' 
            OR PLANACTIVITYNAME LIKE 'L2_-DT-Z6' 
            OR PLANACTIVITYNAME LIKE 'L3_-AIO-Z7' 
            OR PLANACTIVITYNAME LIKE 'L2_-AIO-Z8' 
            OR PLANACTIVITYNAME LIKE 'L2B-G-%-Z5' 
            OR PLANACTIVITYNAME LIKE '%SRV-Z7' 
            OR PLANACTIVITYNAME LIKE '%NB-Z8' 
            OR PLANACTIVITYNAME LIKE 'L2_-NB-Z8'
            THEN 30
        ELSE NULL
        END AS 'UNITSTATUS'
    ,ACTUALENDDATETIME
INTO #MINIKANBANJOB
FROM KANBANJOB
ORDER BY ACTUALENDDATETIME DESC

SELECT KANBAN
    ,ACTUALENDDATETIME
    ,PLANACTIVITYNAME AS 'STAGES'
    ,(SELECT MIN(ACTUALENDDATETIME) as 'Test123456' FROM #MINIKANBANJOB SUB WHERE SUB.LOS = MAIN.LOS AND SUB.UNITSTATUS = MAIN.UNITSTATUS AND SUB.ACTUALENDDATETIME > MAIN.ACTUALENDDATETIME) AS 'NEXTOUTPUT'
    ,LOS
    ,UNITSTATUS
FROM #MINIKANBANJOB MAIN
ORDER BY ACTUALENDDATETIME DESC

But, loading this query as source for direct query results in a 'microsoft sql incorrect syntax near the keyword 'if'. incorrect syntax near ')'' error.

Any one can help?

[Anonymous]

Get rid of the syntactic error and you'll be OK.

Best
D

[vissvess]

No there is no syntatic error. The same query when executed in SSMS fetches the desired table.
Even when it is loaded to Power BI via import mode, It loads correctly.

Only when using direct query, I am getting this error.

Please help.!

[Anonymous]

Then maybe there are some restrictions on what you can write when in the DQ mode. You should check the documentation.

Best
D

[TomMartens]

Hey @vissvess ,

the creation of temporary objects like temp tables is not supported, the same is also valid for cte's. For this reason you have to rewrite your query like so:

select
a.*
from
(
SELECT top(100)
*
from
fact.sale
) a

The subquery a represents your temp-table.

Hopefully, this provides some ideas to tackle your challenge.

Regards,

Tom