Timestamp difference help needed!

Database

Timestamp difference help needed!

The below query has given me alot of trouble as I am new to writing.  I am trying to segment sales calls based on how quickly after the sales call, that the rep "closes/completes" the call.  While I can subtract timestamps in date formats for a day count, I was asked to also account for those calls completed at the end of the business day(after 5:30 or 17:30).  I have gotten every error from invalid date field to multiple formats...Any help would be greatly appreciated.

select

rgn_desc,

b.prim_terr_grp_desc,

prim_terr_sales_org_desc,

prim_terr_rgn_desc,

prim_terr_dist_desc,

prim_terr_desc,

b. prim_terr_div_nbr,

b. prim_terr_grp_nbr,

b. prim_terr_sales_org_nbr,

b.prim_terr_rgn_nbr,

b.prim_terr_area_nbr,

b.prim_terr_dist_nbr,

b.prim_terr_nbr,

prim_terr_seg_desc,

b.first_nm,

b.last_nm,

 

CASE

WHEN CAST(call_end_tmstp as DATE FORMAT 'MM/DD/YYYY')-CAST(rec_last_updt_tmstp as DATE FORMAT 'MM/DD/YYYY') between -10000000 and -1

then

'before the call'

******when

CAST(call_end_tmstp as DATE FORMAT 'MM/DD/YYYY')-CAST(rec_last_updt_tmstp as DATE FORMAT 'MM/DD/YYYY')=0

and

CAST( rec_last_updt_tmstp AS TIME )>

(

select

Cast('17:30:00' as TIME )

from ISELL_PROD_VIEW_DB.sfdc_call_action)

THEN

'end of day'*****

 

 

when

CAST(call_end_tmstp as DATE FORMAT 'MM/DD/YYYY')-CAST(rec_last_updt_tmstp as DATE FORMAT 'MM/DD/YYYY') = 0

then

'end the call'

when

CAST(call_end_tmstp as DATE FORMAT 'MM/DD/YYYY')-CAST(rec_last_updt_tmstp as DATE FORMAT 'MM/DD/YYYY') between 1 and 3

then

'1 to 3 days after'

else

'more than 3 days after'

end

as range_of_completion,

count

(distinct call_actn_key_nbr) as cnt

from ISELL_PROD_VIEW_DB.sfdc_call_action a, ISELL_PROD_VIEW_DB.sfdc_user_profile b

where

a.owner_user_key_nbr = b.user_key_nbr

and

a.sale_call_stat_desc = 'Completed'

-- replace data range as desired

and

a.crte_tmstp > CAST('08/01/2012' as DATE FORMAT 'MM/DD/YYYY')

and

a.crte_tmstp < CAST('09/30/2012' as DATE FORMAT 'MM/DD/YYYY')

group

by 1,2,3,4,5,6,7, 8, 9, 10,11,12,13,14,15,16,17

1 REPLY
Junior Supporter

Re: Timestamp difference help needed!

Hi:

Not sure about what you're after. You could try something like:

WHEN ( CAST(call_end_tmstp AS DATE) = CAST(rec_last_updt_tmstp AS DATE)

       AND

       CAST(rec_last_updt_tmstp AS TIME(0)) > Cast('17:30:00' as TIME(0)

     )

THEN 'end of day'

HTH.

Cheers.

Carlos.