ifw_query

ifw_query

select concat(phonenumber.name, ' ', processed.calldatetime) as NewID, processed.phonenumber, processed.calldatetime, processed.originalcalledpartynumber, processed.secondextension, processed.callingpartynumber, phonenumber.name, count(case processed.originalcalledpartynumber when '8271' then 1 else null end) over (partition by phonenumber.name) as total, case when processed.smdrtype = 'A' then 'True' else 'False' end as Abandoned from processed
right join phonenumber on processed.phonenumber = RIGHT(phonenumber.phonenumber, LEN(phonenumber.phonenumber) - 1)
where convert(date, calldatetime) = convert(date, getdate()) and originalcalledpartynumber = '8271'
UNION
select concat(phonenumber.name, ' ', processed.calldatetime) as NewID, processed.phonenumber, processed.calldatetime as CallDate, processed.originalcalledpartynumber, processed.secondextension, processed.callingpartynumber, phonenumber.name, count(case processed.originalcalledpartynumber when '8271' then 1 else null end) over (partition by phonenumber.name) as total, case when processed.smdrtype = 'A' then 'True' else 'False' end as Abandoned from processed
right join phonenumber on right(processed.originalcalledpartynumber, len(processed.originalcalledpartynumber) - 2) = RIGHT(phonenumber.phonenumber, LEN(phonenumber.phonenumber) - 1)
where convert(date, calldatetime) = convert(date, getdate())