Monthly Emergency Call List

Created by Sean Tomas, Modified on Tue, 5 Dec, 2023 at 1:33 PM by Sean Tomas

Copy file from ticket to C:\LocalDev\Development Source Code\Emergency_Service_Billing\New Emergency Service Billing System\DataFiles on DT-DEV
Open Access DB: "C:\LocalDev\Development Source Code\Emergency_Service_Billing\New Emergency Service Billing System\Direct Travel Emergency Service Billing.accdb"
Delete all rows from staging_ets_emergency_calls
Copied and pasted data into staging_ets_emergency_calls from Excel sheet provided

Run macro import_es_data-RunMeAfterImportingData

$Direct Travel Emergency Service Billing : Database- Source Emergency Service Billing System\Direct Travel Emergency Ser (Access 2W7 - 2016 file format) - Access Home Create External Data Database Tools Tell me what you want to do... Sean Tomas View Views Copy P aste Format Painter Clipboard Ascending Y Selection • A I Descendi ng Advanced • Remove Sort Toggle Filter Sort & Filter New Totals [E Save Refresh All • Delete • Records @ < frmMain Start Date: End Date: Compile Data •ac Replace Find Select • Find Text Formatting All Access Objects Seorcm tbIReportData Queries custid check ccra 01 -FindlnvaIidCustids custid check ccra 02-RemoveInvaIidCustidRecords custid check ets 03-FindInvaIidCustids custid check ets 04-RemoveInvaIidCustids -Action I -QueueMonFeesCharges Account: Customer ID: Repot Name: Run Repot NWA: 2553370011 ,3553000011 ,3553020011 ,3553020112.3553030011 ,3553040011 ,3553 080011 ,3553080011 ,3553080011 ,35531 00011 ,355311 0011 ,35531 20011 ,35531 400 1 1 ,35531 50011 ,35531 80011 ,35531 80011 ,3553200011 ,355321 0011 ,3553220011.3 553230011 ,3553240011 ,3553250011 ,3553280011 ,3553270011 ,3553280011 ,35532 80011 ,3553300011 ,355331 0011 ,3553320011 ,3553330011 ,3553340011 ,355335001 1 ,3553380011 ,3553380011 emergency_ emergency_ emergency_ emergency_ emergency_ emergency_ services fee 02-auiIdLocaIFeeTabIe services fee 03-GetStandardFees services fee 04-GetVIPFees services fee 05-GetExecFees services fee nn-FeeCaIcuIation services fees-Ol- auild Fee Structure frmMain_OI _ UniqueAccountNames d_staging-OI -ClearMainStagingTabIe MissingDks Report Report_TakeOutOFees Forms frmMain Repo rts rptCaIILogDetaiI rptCaIILogDetaiI_ayCostCenter rptCaIILogDetaiIayAcctP8rk-WithCostCenters rptCaIILogDetaiIayAcctpark-WithCostCenters_OriginaI rptCaIILogDetaiIayaranchP8rk Macros autoexec custid check emergency_services_fee e rim port ing Data load_staging Run Query Num Lock$

When running Query (will take several minutes) it will popup with message about appending rows, click yes

-- Run SQL Script to verify data is in SQL Server 
-- script to check final data 
-- ON DTIHASQL 

USE CallLogDB 

declare @StartDate date, @EndDate date 

set @StartDate= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month 
set @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month 


select @StartDate , @EndDate  

select * 
from [dbo].[emergency_services_calls] 
where Date between @StartDate AND @EndDate   
order by 1

From <https://help.dt.com/staff/Tickets/Ticket/View/1207697/inbox/160/6/-1>

Add note to ticket:

--- Steps

1. Delete all data from "staging_ets_emergency_calls" table

2. Copy data from new excel file to "staging_ets_emergency_calls" table

3. Run "import_es_data-RunMeAfterImportingData" macro - right click Macro and select run

4. Run final check script

-- Run SQL Script to verify data is in SQL Server

-- script to check final data

-- ON DTIHASQL

USE CallLogDB

declare @StartDate date, @EndDate date

set @StartDate= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month

set @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

select @StartDate , @EndDate

select *

from [dbo].[emergency_services_calls]

where Date between @StartDate AND @EndDate

order by 1

Reply to user:

Hi Tricia

File was processed today.

Thanks,

Sean

From <https://help.dt.com/staff/Tickets/Ticket/View/1223598>