Information inquiry about SSRS request

Question

Hello everyone, hope you're fine !

I come back there to ask you so help, beacause I'm block... again :D

Ok, so let's resume :

I have this table :

What you need to see is that I've got some different L-ECP(L-ECP Number field), with a state (CURRENT_STATUS field) and a date of status change (CHANGE_DATE field).

With this expression :

=Switch(Previous(Fields!NB.Value) = Fields!NB.Value, ABS(Datediff(DateInterval.Day,Fields!CHANGE_DATE.Value,Previous(Fields!CHANGE_DATE.Value))),True, ABS(Datediff(DateInterval.Day,Fields!CHANGE_DATE.Value,Globals!ExecutionTime)))

I'm calculating time spent between each status, and for last request, I'm calculating time in the last state and today, as you can see below :

My problem is :

I want the average time spent for each status (I have around 9000 L-ECP with various status).
But, when I want to GROUP BY CURRENT_STATUS, the query re-calculate the time spent with the new disposition of the table.

Do you have an idea for me ? How can I in the same table or an other display the information that I want without broke data that I have with my query ?

First, thank for reading ! Don't hesitate to ask for more information if require.

Thanks by advance for your help !

Regards,

Fabien

Answer 1

Hi @fabien prieur ,

  SELECT *,  
  CASE WHEN (DATEDIFF(DAY,CHANGE_DATE,newdate)) <0 THEN DATEDIFF(DAY,CHANGE_DATE,newdate)*-1 ELSE DATEDIFF(DAY,CHANGE_DATE,newdate) END AS timeelapsed  
  FROM (SELECT  
  NF.SYSTEM_NAME AS PRODUCT,  
  OTH.OBJECT_IDENT NB,  
  OTH.CURRENT_STATUS,  
  OTH.DOCINDEXDATE CHANGE_DATE,  
  lag(CHANGE_DATE,1,getdate()) OVER (ORDER BY NB) as newdate    
             
  FROM CSA_ECPBASEVIEW_NF NF  
  INNER JOIN OT_STATUSHISTORY OTH  
  ON OTH.OBJECT_IDENT = NF.SONREFERENCE) a  

The code should be a entirety part and you could not execute them separately.

In other words, the code part is used as table a shown below, in your second part the from table is lost which caused the error.

(SELECT  
  NF.SYSTEM_NAME AS PRODUCT,  
  OTH.OBJECT_IDENT NB,  
  OTH.CURRENT_STATUS,  
  OTH.DOCINDEXDATE CHANGE_DATE,  
  lag(CHANGE_DATE,1,getdate()) OVER (ORDER BY NB) as newdate    
             
  FROM CSA_ECPBASEVIEW_NF NF  
  INNER JOIN OT_STATUSHISTORY OTH  
  ON OTH.OBJECT_IDENT = NF.SONREFERENCE)  a  

Regards,

Zoe

Answer 2

Hi @fabien prieur ,

I think your requirement will be easy to meet with T-SQL.

We could process the data in advance in the dataset to get the time elapsed.

Refer the code below.

;with cte as (  
  SELECT   
      [NB]  
      ,[CURRENT_STATUS]  
      ,[ORIGINAL_STATUS],[CHANGE_DATE],lag(CHANGE_DATE,1,getdate()) OVER (ORDER BY NB) as newdate  
  FROM [AdventureWorks2012].[dbo].[STAR])  
  select *,CASE WHEN (DATEDIFF(DAY,[CHANGE_DATE],newdate)) <0 THEN DATEDIFF(DAY,[CHANGE_DATE],newdate)*-1 ELSE DATEDIFF(DAY,[CHANGE_DATE],newdate) END AS timeelapsed  
  from cte  

And then we could GROUP BY CURRENT_STATUS and use expression to get the average time spent for each status.

Hope it will be helpful.

Regards，
Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 3

Hi @fabien prieur ,

CTE means common_table_expression, you could see this for details.

Modify your code, please refer below:

 SELECT *,  
 CASE WHEN (DATEDIFF(DAY,CHANGE_DATE,newdate)) <0 THEN DATEDIFF(DAY,CHANGE_DATE,newdate)*-1 ELSE DATEDIFF(DAY,CHANGE_DATE,newdate) END AS timeelapsed  
 FROM (SELECT  
 NF.SYSTEM_NAME AS PRODUCT,  
 OTH.OBJECT_IDENT NB,  
 OTH.CURRENT_STATUS,  
 OTH.DOCINDEXDATE CHANGE_DATE,  
 lag(CHANGE_DATE,1,getdate()) OVER (ORDER BY NB) as newdate    
         
 FROM CSA_ECPBASEVIEW_NF NF  
 INNER JOIN OT_STATUSHISTORY OTH  
 ON OTH.OBJECT_IDENT = NF.SONREFERENCE) a  

If this doesn't work, we'd suggest that you may provide DDL and simple data for us to do more analyze.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 4

Hi @ZoeHui-MSFT ,

First thank for you reply !
I'm really new in the world of SQL and I'm working with SSRS for 4 month now and start from scratch ! I'm working with Report Builder 3.0
I've got the idea of what you attempt to do that look like what I expected ! I didn't knew CTE function
I tried to "translate" what you gave me with what I had, but I've only got error (with FROM key word it seem), but I can't find the error ..

WITH cte as (  
      
SELECT  
NF.SYSTEM_NAME AS PRODUCT,  
OTH.OBJECT_IDENT NB,  
OTH.CURRENT_STATUS,  
OTH.DOCINDEXDATE CHANGE_DATE,  
lag(CHANGE_DATE,1,getdate()) OVER (ORDER BY NB) as newdate     
      
FROM CSA_ECPBASEVIEW_NF NF  
INNER JOIN OT_STATUSHISTORY OTH  
ON OTH.OBJECT_IDENT = NF.SONREFERENCE)  
   
SELECT *,  
CASE WHEN (DATEDIFF(DAY,CHANGE_DATE,newdate)) <0 THEN DATEDIFF(DAY,CHANGE_DATE,newdate)*-1 ELSE DATEDIFF(DAY,CHANGE_DATE,newdate) END AS timeelapsed  
FROM cte  

Need your help again, sorry ..

Fabien

Answer 5

Hi again @ZoeHui-MSFT

Thanks again for reply

Ok, I tried your solution, and I make some modification to make it "work"

SELECT  
NF.SYSTEM_NAME AS PRODUCT,  
OTH.OBJECT_IDENT NB,  
OTH.CURRENT_STATUS,  
OTH.DOCINDEXDATE CHANGE_DATE,  
lag(OTH.DOCINDEXDATE,1,sysdate) OVER (ORDER BY OTH.OBJECT_IDENT) as newdate    
                 
FROM CSA_ECPBASEVIEW_NF NF  
INNER JOIN OT_STATUSHISTORY OTH  
ON OTH.OBJECT_IDENT = NF.SONREFERENCE  

This part work perfectly, I change GETDATE() by SYSDATE and put OTH.OBJECT_IDENT instead of NB (because et wouldn't recognize NB ..)

Then when I had this part :

SELECT *,  
CASE WHEN (DATEDIFF(DAY,OTH.DOCINDEXDATE,newdate)) <0   
THEN (DATEDIFF(DAY,OTH.DOCINDEXDATE,newdate))*-1   
ELSE (DATEDIFF(DAY,OTH.DOCINDEXDATE,newdate))   
END AS timeelapsed  

I've got this error : "ORA-00923: FROM keyword not found where expected
(mscorlib)"

I tried a lot of thing but I can't find what is wrong

Fabien