All Forums Database
Saarang84 16 posts Joined 08/14
23 Oct 2014
Error While Trying to cast as Timestamp

Hi,
I'm trying to modify a code script and trying to add a few new fields to it. The tricky part I come across while try to add these fields is while trying to cast them. I would need the format of a timestamp (calculated based on division / modulo of two fields) [Format is hh:mm:ss], but unable to cast it using timestamp.
 
Average Speed
=If IsNull([Tot_Answered]) Then "" Else FormatNumber(Floor(([Delay_Dur]/[Tot_Answered])/3600); "00")+ ":"+FormatNumber(Floor(Mod(([Delay_Dur]/[Tot_Answered] ) ;3600)/60) ;"00")+ ":" + FormatNumber(Mod(Mod(([Delay_Dur]/[Total_Answered]); 3600);60);"00")
 
The code script I use is :

SELECT  
Table4.Data_Dt,  Table3.Service_Name, 
  SUM(((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle')  THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
       +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short')   THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))
    +( CASE WHEN ( DRV_Table1.Resource_Type ) = 2 AND ( Table2.Handle_Type ) IN ('Abandon') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))) AS "Tot_Offered",
  SUM ((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle')  THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
       +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short')   THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))) AS "Tot_Answered",
  SUM ((  CASE WHEN ( DRV_Table1.Resource_Type ) = 2 AND ( Table2.Handle_Type ) IN ('Abandon') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))  AS "Tot_Abandoned",
  SUM ((  CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ( 'Handle') THEN DRV_Table1.Q_Dur  ELSE 0 END )
       +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ( 'Handle') THEN DRV_Table1.Dlay_Dur ELSE 0 END )
    +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle')  THEN DRV_Table1.Rng_Dur ELSE 0 END )) AS "Delay_Dur",
  SUM ((  CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Tlk_Dur*1.0000 ELSE 0*1.0000  END )) AS "Tot_Tlk_Tm",
  SUM ((  CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Wrk_Dur ELSE 0 END )) AS "Tot_Wrk_Tm",
  CASE WHEN CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0  END )
    +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))))  AS DECIMAL (10,2) ) IS NULL 
      OR CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
    +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))))   AS DECIMAL (10,2) ) = 0 THEN 0 
  ELSE 
 (1.0000 * CAST((SUM((CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) = 'Handle' AND ( Table2.Handle_Type_Code2 ) = 'In Service' THEN 1 ELSE 0 END ))) AS DECIMAL (10,2)) /  
           CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
     +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))))  AS DECIMAL (10,2) ))*100 END AS "Srv_Lvl(%)",
  SUM(( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Hld_Dur ELSE 0 END )
     +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Tlk_Dur*1.0000 ELSE 0*1.0000  END )
     +( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Wrk_Dur ELSE 0 END )) AS "Hndl_Tm" 
FROM  Table4 
INNER JOIN ( 
SELECT * FROM Table1 WHERE Strt_Dt_ID  BETWEEN (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100  
AND (CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 )  DRV_Table1 
ON (Table4.Date_ID=DRV_Table1.Strt_Dt_ID) 
INNER JOIN Table2 ON (DRV_Table1.Seg_Result_ID=Table2.Seg_Result_ID)
INNER JOIN Table3 ON (DRV_Table1.Resource_ID=Table3.Resource_ID) 
  
WHERE (
   Table3.Service_ID  IN  ( 9572511 ) AND Table3.Skl_Grp_ID  IN  ( 258, 0 ) AND
   Table4.Date_ID  BETWEEN  (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100  AND 
   (CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 )
GROUP BY  1,  2 
ORDER BY 1

 
I believe, this could also be done by casting it as varchar. Can some help me modify this code to add the new fields ?

Sarang

dnoeth 4628 posts Joined 11/04
24 Oct 2014

Hi Sarang,
the field seems to be a number of seconds, of course can't you cast that to a Time.
 
You need an Interval instead:

Tot_Answered * interval '00:00:01' second

 

Dieter

Saarang84 16 posts Joined 08/14
24 Oct 2014

Hi Dieter,

I tried modifying the script to add the new fields, though I come across errors in this particular line in the above code :

 

SELECT * FROM Table1 WHERE Strt_Dt_ID  BETWEEN (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100  
AND (CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 )  DRV_Table1 
ON (Table4.Date_ID=DRV_Table1.Strt_Dt_ID) 

 

The data types of the Strt_Dt_Id field of Table1 and Date_ID of Table4 (above) is integer, but casting a date input by the user at runtime gives out an error.

 

What would be correct usage ?

 

Sarang

dnoeth 4628 posts Joined 11/04
25 Oct 2014

Hi Sarang,
there are many ways to store a date in an integer :)
 
 
So how is the date stored (yyyymmdd?) and what's the user's input?

Dieter

Saarang84 16 posts Joined 08/14
25 Oct 2014

Hi Dieter,
The date's stored as a 5 digit integer (just like how Excel stores dates in General format). I would want to extract data for a particular date range. I'm trying to cast the Start and End dates that the user inputs at runtime and fetch the records pertaining to that date range from Table1.

Sarang

dnoeth 4628 posts Joined 11/04
26 Oct 2014

Hi Sarang,
Excel stores the number of days since a starting date, 1900-01-01 is day 1. So the difference between the start_dt and this starting date is almost correct, you just need to substract '1899-12-30' instead of '1900-01-01' ('1899-12-30' instead of 31 because Excel's calculation includes the date '1900-02-29' which never existed).
But why do you substract 100 from this?
 
And of course the end user has to key in a correct date with the correct format, yyyy-mm-dd.

Dieter

You must sign in to leave a comment.