Shulou(Shulou.com)06/03 Report--

The sales department of the company needs to count the salesman's customers and output Excel according to the salesman's name, see the following procedure

-- declare the required variables

Declare @ sql varchar @ TypeID varchar (6) @ MyName varchar (10)

Each salesman should have a serial number (numeric type)

Select @ TypeID=min (ID) from A_CUST

Select @ MyName = EMPLOYEE_NAME from A_CUST where ID=@TypeID

-processing when there is a record that satisfies the sequence number

While exists (select 1 from A_CUST where ID=@TypeID)

Begin

-- piece together the statements that need to be executed

Set @ sql='bcp "select * from (select'+'''CUSTOMER_CODE'''+' AS customer code,'+''CUSTOMER_NAME'''+' AS customer name,' + 'CUSTOMER_FULL_NAME'''+' AS customer full name,'

+''EMPLOYEE_CODE'''+' AS work number,' +''EMPLOYEE_NAME'''+' AS name,' +''ADDRESS'''+' AS address,' +''TELEPHONE'''+' AS phone,' +''CONTACT'''+' AS contact'-displays the column name in the xls file

Set @ sql=@sql+' union all select CUSTOMER_CODE,CUSTOMER_NAME,CUSTOMER_FULL_NAME,EMPLOYEE_CODE,EMPLOYEE_NAME,ADDRESS,TELEPHONE CONTACT from A_CUST where ID='+cast (@ TypeID as varchar (10)) +') a "queryout" D:\ customer\'+ cast (@ MyName as varchar (10)) + '.xls "- c-Q-S" 127.0.0.1 "- U" sa "- P" password "- d" Database name "- query the records that meet the criteria and save them to the xls file

Use xp_cmdshell system stored procedures to execute patchwork statements (you need to use advanced options switches to pre-open the cmdshell component)

Exec master..xp_cmdshell @ sql

-- get the serial number of the next salesman (the serial number is discontinuous)

Select @ TypeID=isnull (min (ID), @ TypeID+1) from A_CUST where ID > = @ TypeID+1

Select @ MyName = EMPLOYEE_NAME from A_CUST where ID=@TypeID

End

The following program is a view generated from the association of each table

Drop view A_CUST

Create view A_CUST as select CUSTOMER_CODE,CUSTOMER_NAME,CUSTOMER_FULL_NAME,EMPLOYEE_CODE,EMPLOYEE_NAME,d.ADDRESS,d.TELEPHONE,e.CONTACT,A_C.ID from CUSTOMER a

Left join CUSTOMER_SALES b ON a.CUSTOMER_BUSINESS_ID = b.CUSTOMER_ID

Left join EMPLOYEE c ON b.Owner_Emp = c.EMPLOYEE_ID

Left join has C ON c.EMPLOYEE_CODE = A_C.GH

Left join CUSTOMER_ADDRESS d ON b.CUSTOMER_BUSINESS_ID = d.CUSTOMER_BUSINESS_ID

Left join CUSTOMER_CONTACT e ON b.CUSTOMER_BUSINESS_ID = e.CUSTOMER_BUSINESS_ID

Where a.ApproveStatus ='Y'

Insert into Atrec (GH) select EMPLOYEE_CODE from EMPLOYEE

Select * from Atom C

Select from A_CUST where ADDRESS is not null

Select from CUSTOMER where CUSTOMER_CODE = '0080'

Select * from CUSTOMER_SALES where CUSTOMER_ID = 'F16DD932-0155-4A9A-4FE9-13BF5CF9277D'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.