Forum Discussion

vissvess's avatar
vissvess
Helper V
6 years ago

Next higher value

Hi Community experts, 

I have a scenario where the data is as follows

SequenceActualenddatetimeLOS
13/12/2020 12:44:01L2A
23/12/2020 12:44:42L2B
53/12/2020 12:45:02L2A
13/12/2020 12:46:25L2A
43/12/2020 13:44:01L2A
33/12/2020 13:44:42L2B
83/12/2020 12:55:02L2A
43/12/2020 12:49:25L2A

 

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.

 

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      vissvess
      Helper V
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        Get rid of the syntactic error and you'll be OK.

        Best
        D