ETT - Bestpass

Information required from Bestpass

Infrastructure Requirements

Software License Details

Domain Transfer

SPAN Responsibilities

AWS-ETT Infrastructure - DevOps Team

ETT SQL Details

Mobile App Transfer

Transition Services Agreement (TSA)

Tax Year Changes

Transfer a Domain with NO Downtime

ETT Deployment

ETT Domains

ETT Build Deployments

ETT Payments - Flex Microform

Knowledge Transfer - Technical

Domain KT

Explore

ETT SQL Details

ETT Report Queries

Important Tables used

ee_users – This will have User Details

ee_businessdetails – This will have business details against each user

ee_returns – This will have return details, form type, return status and paid status in it.

ee_Group_Member_Details – This will have the user subscribed groups.

ee_Payments_Orders – This will have details about the paid order details against each returns by user.

ee_Form2290, ee_Form8849_Schedule6, ee_Form2290_Amendments – This will have details about the IRS forms data entered by users.

ee_efile – Tables with “efile” prefix will have IRS Efile data before sent to the IRS

1. All BO ETT clients who have previously filed the 2290 but have not filed the 2022-23 Form 2290

select distinct u.Email_Address, u.Contact_Name, u.Phone_Number, 'ETT_BO_Clients_Not_filed_for_2022-23_MAR_20_2023' as [Filing Status] from ee_users u where u.Is_Deleted = 0 and u.User_Type_Id = 1 and u.Email_Address not like '%@spanenterprises.com%' and u.Email_Address not like '%@expressexcise.com%' and u.Email_Address not like '%@spanllc.com%' and u.Email_Address not like '%@w3magix.com%' and u.Email_Address not like '%@pdffiller.com%' and u.Email_Address not like '%@dotnetethics.com%' and u.Email_Address not like '%@expressefile.com%' and u.Email_Address not like '%@spantechnologyservices.com%' and U.USER_ID IN ( SELECT USER_ID FROM ee_Returns where u.Is_Deleted = 0 and Is_Paid = 1 ) --with having have returns and U.USER_ID NOT IN ( SELECT USER_ID FROM EE_USERS WHERE Affiliate_Id in (363, 387) ) --EEF,TRUCK Logic and u.USER_ID NOT IN ( SELECT USER_ID FROM EE_USERS WHERE ( Referral_code in('NASTC') ) ) and u.user_Id not in ( select useR_Id from ee_Group_Member_Details where is_deleted = 0 and group_Id in (7, 70, 71, 72, 10, 8, 9, 11, 5, 1, 28, 54, 57, 73))

Tables used in above query

ee_users - Primary Table

ee_Returns - Primary Table

ee_Group_Member_Details

We select in above query like email address, contact name, phone number form the user who not filed current tax year using having paid return in user account and not filed current tax year using group id

Group id like if user filed form 2290 current tax year we maintain group member details using 2290 Filed 22-23, Pre-Filed 22-23

2.Clients who created an account they HAVE entered biz info but have not created returns

select distinct u.Email_Address as [Email Address], u.contact_name as [First Name], u.Phone_Number, 'ETT_Clients_Business_Created_But_No_Returns_MAR_27_2023' as [Filing Status] from ee_Users u where exists ( select user_id from ee_BusinessDetails b where b.Is_Deleted = 0 and b.user_id = u.user_id ) and NOT exists( select user_id from ee_Returns r where r.Is_Deleted = 0 and r.user_id = u.user_id ) and u.Is_Deleted = 0 and u.Create_Time_Stamp >= '2022-06-01 00:00:00.000' and u.email_address not like '%@spanenterprises%' and u.email_address not like '%@spanllc%' and u.Email_Address not like '%@pdffiller.com%' and u.Email_Address not like '%@dotnetethics.com%' and u.Email_Address not like '%@spantechnologyservices.com%' and u.Email_Address not like '%@expressefile.com%' and u.email_address not like '%@expressexcise%' and u.email_address not like '%@w3magix%' and u.user_id not in ( select user_id from ee_group_member_details where group_id in (7, 70, 71, 72, 10, 8, 9, 11, 5, 1, 28, 54, 57, 73) )

Tables used in above query

ee_Users

ee_BusinessDetails

ee_Returns

ee_group_member_details

The above query using user who created account and business details but return not created in business for the user.

3. Rejected and Cancel Returns for February, 2023

Rejected Return Count

select MONTH(r.Transmitted_Time_Stamp), count(1) as [Total Return Count] from ee_returns r where r.Filing_Status_Id in (4) and r.Transmitted_Time_Stamp >= '2022-01-01 00:00:00.000' and r.Transmitted_Time_Stamp < '2022-01-01 00:00:00.000' and r.Is_Deleted = 0 group by MONTH(r.Transmitted_Time_Stamp)

Cancelled Return Count

select MONTH(r.Transmitted_Time_Stamp), count(1) as [Total Return Count] from ee_returns r where r.Filing_Status_Id in (6) and r.Transmitted_Time_Stamp >= '2022-01-01 00:00:00.000' and r.Transmitted_Time_Stamp < '2022-01-01 00:00:00.000' and r.Is_Deleted = 0 group by MONTH(r.Transmitted_Time_Stamp)

The above queries using take count of Rejected and cancelled Returns for particular month.

We use take Rejected Return count using filing status id (4) in returns table to take counts in particular month.

We use take Cancelled Returns count using filing status id (6) in returns table to take counts in particular month.

4. Transmitted Return From New User and Existing Users for February, 2023

Transmitted Return From New User

( select count(distinct u.user_id) as [Return from New user] from ee_users u join ee_returns r on u.user_id = r.user_id where u.is_deleted = 0 and r.is_deleted = 0 and r.is_paid = 1 and u.create_time_stamp > @ExistDate and r.Transmitted_Time_Stamp >= @TransStartDate and r.Transmitted_Time_Stamp < @TransEndDate )

We use this query take return count from new user filing for particular month.

@ExistDate - user Account created date

@TransStartDate

@TransEndDate - User transmitted return dated

Transmitted Return From New User

select count(r.Return_Id) as [Return from Existing user] from ee_users u join ee_returns r on u.user_id = r.user_id where u.is_deleted = 0 and r.is_deleted = 0 and r.is_paid = 1 and u.create_time_stamp < @ExistDate and r.Transmitted_Time_Stamp >= @TransStartDate and r.Transmitted_Time_Stamp < @TransEndDate

We use this query take return count from existing user filing for particular month.

@ExistDate - User Account created date

@TransEndDate - User transmitted return date

5. Transmitted Return from New User for February 2023 compare to February 2022

select count(distinct u.user_id) as [Return from New user] from ee_users u join ee_returns r on u.user_id = r.user_id where u.is_deleted = 0 and r.is_deleted = 0 and r.is_paid = 1 and u.create_time_stamp > @ExistDate and r.Transmitted_Time_Stamp >= @TransStartDate and r.Transmitted_Time_Stamp < @TransEndDate group BY day(r.Transmitted_Time_Stamp)

@ExistDate - User Account created date

@TransEndDate -User transmitted return date

The above query take return count compare using different months.

6. Transmitted Return From Portal Sites February 2023

SELECT Aff.Referral_Code, Aff.Business_name, Aff.Phone_Number, Aff.Email_Address, Aff.create_time_stamp, ( SELECT COUNT(r.return_id) FROM ee_users u join ee_returns r on u.user_id = r.user_id WHERE r.filing_status_id > 2 and r.is_paid = 1 and r.create_time_stamp > '2022-05-16 00:00:00.000' and r.Update_Time_Stamp < '2023-03-07 00:00:00.000' and u.Referral_Code = Aff.Referral_Code and u.Is_Deleted = 0 and u.email_address not like '%spanenterprises%' and u.email_address not like '%spanllc%' and r.is_deleted = 0 and u.email_address not like '%expressexcise%' and u.email_address not like '%w3magix%' and u.Email_Address not like '%@spantechnologyservices.com' ) AS TOT_May_2023, ( SELECT COUNT(DISTINCT r.User_Id) FROM ee_users u join ee_returns r on u.user_id = r.user_id WHERE r.filing_status_id > 2 and r.is_paid = 1 and r.create_time_stamp > '2022-05-16 00:00:00.000' and r.Update_Time_Stamp < '2023-03-07 00:00:00.000' andu.Referral_Code = Aff.Referral_Code and u.Is_Deleted = 0 and u.email_address not like '%spanenterprises%' and u.email_address not like '%spanllc%' and r.is_deleted = 0 and u.email_address not like '%expressexcise%' and u.email_address not like '%w3magix%' and u.Email_Address not like '%@spantechnologyservices.com' ) as TOT_May_User_2023, ( SELECT COUNT(1) FROM ee_users WHERE ee_users.Referral_Code = Aff.Referral_Code and ee_Users.Is_Deleted = 0 and ee_users.email_address not like '%spanenterprises%' and ee_users.email_address not like '%spanllc%' and ee_users.email_address not like '%expressexcise%' and ee_users.email_address not like '%w3magix%' and ee_Users.Email_Address not like '%@spantechnologyservices.com' ) AS TOT_Users FROM ee_Affiliates as Aff where Aff.is_deleted = 0 and Affiliates_Type != 'Agency' and Affiliates_Type != 'Agent' and aff.is_active = 1 order by TOT_May_User_2023 desc

The above query is used to find how many return and user filing using portal site in particular month.

Group Member Details

Group Member Details

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.

ETT Report Queries

Important Tables used

1. All BO ETT clients who have previously filed the 2290 but have not filed the 2022-23 Form 2290

Tables used in above query

2.Clients who created an account they HAVE entered biz info but have not created returns

Tables used in above query

3. Rejected and Cancel Returns for February, 2023

Rejected Return Count

4. Transmitted Return From New User and Existing Users for February, 2023

​Transmitted Return From New User

​Transmitted Return From New User

5. Transmitted Return from New User for February 2023 compare to February 2022

6. Transmitted Return From Portal Sites February 2023

Transmitted Return From New User

Transmitted Return From New User