SQL Server Report statistics-parameterized Dynamic Compaction row-to-column, sqlserverregression

Source: Internet
Author: User

SQL Server Report statistics-parameterized Dynamic Compaction row-to-column, sqlserverregression


List all table structures first:

-- Title Report -- table real-time Water Fee: hx_t_received -- field Charge Department id: hx_f1_mentid 1. Charge Department name: hx_fdepartmentname water fee information ID: hx_freceivableid -- charge type: hx_ftype (Water Fee (1 ): [Account Opening (table) Water Supply category: tap water (100000001) = water fee, water (100000002) = Water Fee], agency fee (2) = sewage treatment fee) -- paid amount: hx_freceivedamount Creation Time: createdon payment method: hx_fpayway (cash: 100,000,000 and check: 100,000,001) -- Transaction Status: hx_fstate (recorded at 100,000,000: Check payment 100,000,001 except: void: 100,000,002. Bank payment: 100,000,003) -- Bill information: hx_finvoiceid toll collector: CreatedByName Creation Time: createdon primary key: hx_t_receivedid -- billing department: hx_f1_mentid water meter ID: hx_fmeterid: modifiedon caller id: hx_fdunid toll collector: createdby -- table payable water fee: hx_t_0000able -- Field Water Information ID: primary key: hx_t_0000ableid customer information: hx_faccountid water meter: hx_fmeterid -- Record status: hx_fstate (check payment: 100,000,002) -- table water usage: hx_t_waterusedamount -- field account information: primary key: primary water meter: hx_fmeterid section: hx_fzone practical water usage: hx_famount -- Estimation reason: hx_festimateamountreason settlement water usage: hx_fpayamount -- Meter Reading type hx_frecordtype (estimated water: 100,000,001 not copied see: 100,000,002 normal: 100,000,000 pre-payment card table estimated water: 100,000,003) -- water volume status: hx_fstate (Plan: 100,000,000, already entered water volume: 100,000,001, generated receivables: 100,000,006 this meter reading stop: hx_freading -- Water Year: hx_fyear water month: hx_fmonth -- table Customer: account -- field primary key: accountid CUSTOMER name: name Customer Code: accountnumber -- table Customer Account Transaction details: hx_t_transactiondetails -- field transaction type: hx_ftranstype (account recharge: 100,000,000) transaction amount: hx_ftransamount paid-in water fee information: hx_freceivedid toll collector: createdby section: hx_fzoneid -- Transaction Status: hx_fstate (void: 100,000,002) ticket information: hx_finvoiceid payment method: hx_fpayway -- table customer recharge account: hx_t_rechargeaccount -- primary key; fill water meter ID: hx_fmeterid customer ID: hx_fbuildinaccountid -- table account information: hx_t_customerandmeterrela -- Section: hx_fzoneid primary key: hx_t_customerandmeterrelaid water meter: hx_fmeterid Customer: hx_faccountid -- ***************** -- temporary table water usage: hx_t_tempwaterreceived primary key: hx_t_tempwaterreceivedid -- field temporary water usage: hx_ftempwaterid payment method: hx_fpayway (cash: 100,000,000 and check: 100,000,001) -- Transaction Status: hx_fstate (except: void: 100,000,002, bank payment: 100,000,002) -- ticket information: hx_finvoiceid billing department ID: temporary -- table temporary water: hx_t_tempwater -- field primary key: hx_t_tempwaterid owner: ownerid charged items: hx_ffeeitems (Water Fee (next minute), sewage fee) Water Supply category: hx_fwatertype section: hx_fzoneid -- water volume: hx_fusedamount -- table User: systemuser -- field primary key: SystemUserId business department: businessunitid Full name: FullName -- table Department: businessunit -- field primary key: businessunitid name: name -- table section information: hx_t_zone -- field primary key: hx_t_zoneid Team Information: hx_fteamid table reader: hx_frecorder Area No.: hx_fzone -- Department: hx_fbureau reminder: hx_fdunname (Search) department name: hx_fbureauName -- table billing Institute hx_t_levyinstitute -- field primary key: Primary name: hx_fname -- table Meter Reading team hx_t_teamattribution -- field primary key: Primary table reading team info: hx_fteamid billing Institute info: Staff -- table staff: hx_t_staff -- field name: hx_name primary key: hx_t_staffid
SQL code:

-- Title Evaluation Report declare @ begin_date datetimedeclare @ end_date datetimeselect @ begin_date = getdate () declare @ Billing Clerk @ clerk = 'clerk-E174-46F2-855A-32F26BE38F3B '-- September 01, 2014 declare @ BusinessunitId uniqueidentifier -- billing Institute set @ BusinessunitId = '6a2537dd-B87A-E411-93FE-002590622897' ---- 15th toll Institute DECLARE @ TeamId uniqueidentifier Meter Reading SET @ TeamId = null -- '18acbd81-5B45-E411-9402-6CAE8B22702D '-- 15th Toll Collection (fragmented table Group) ----------------- SQL body -------------------------------------------------------- execution time: declare @ FMonth intdeclare @ FYear intdeclare @ FTimes intselect @ FYear = t. hx_FYear, @ FMonth = t. hx_FMonth, @ FTimes = t. hx_FTimes from hx_ClosingAccountInfo t where hx_ClosingAccountInfoId = @ hx_ClosingAccountInfoId -- select @ FYear, @ FMonth, @ FTimesbeginif object_id ('tempdb .. # t_estimateamountreason ') is not null drop table # t_estimateamountreasonif object_id ('tempdb .. # t_EstimateamountReasonTotal ') is not null drop table # t_EstimateamountReasonTotalselect * into # t_estimateamountreason from (select distinct t1.Label, t2.Value from MetadataSchema. localizedLabel t1 inner join MetadataSchema. attributePicklistValue t2 on t1.ObjectId = t2.AttributePicklistValueId inner join MetadataSchema. optionSet t3 on t2.OptionSetId = t3.OptionSetId where t3.Name = 'hx _ Region' and t1.ObjectColumnName = 'displayname' and t1.LanguageId = 2052) tselect * into # select from (select w. hx_frecordername fullname, -- table reader w. hx_fzone, -- area number 1 mcounts, e. label, w. hx_fpayamount, -- water volume r. hx_freceivablehx_freceivablefee, -- water fee r. hx_fcollchargesreceivable4 wsf -- sewage fee from hx_t_waterusedamount w -- water volume inner join Team t -- Team ON w. owningTeam = t. teamId AND isnull (w. owningTeam, '1970-0000-0000-0000-000000000000') = isnull (@ TeamId, w. owningTeam), '2017-0000-0000-0000-000000000000') AND w. hx_FYear = @ FYear and w. hx_FMonth = @ FMonth and w. hx_FTimes = @ FTimes and w. hx_frecordtype = '000000' inner join hx_t_teamattribution n ON t. teamId = n. hx_fteamid AND isnull (n. hx_flevyinstituteid, '1970-0000-0000-000000000000') = isnull (@ BusinessunitId, n. hx_flevyinstituteid), '1970-0000-0000-0000000000000000') inner JOIN # t_estimateamountreason e -- water cause estimation ON e. value = w. hx_festimateamountreason left join hx_t_retriable r -- receivables on w. hx_t_waterusedamountid = r. hx_fusedamountid UNION ALLselect null fullname, null hx_fzone, 1 mcounts, e. label, 0 hx_fpayamount, 0 hx_freceivableduplicate, 0 wsf from # t_estimateamountreason e) h -- parameterized dynamic delimiter row to column DECLARE @ SQL _str NVARCHAR (MAX) DECLARE @ SQL _col NVARCHAR (MAX) DECLARE @ tableName SYSNAME -- DECLARE @ orderColumn SYSNAME -- group field DECLARE @ row2column SYSNAME -- DECLARE @ row2columnValue SYSNAME -- field of the row-modified column value DECLARE @ OtherField NVARCHAR (100) DECLARE @ SQL _col_out NVARCHAR (MAX) SET @ tableName = '# t_EstimateamountReasonTotal' SET @ orderColumn = 'pvt. hx_fzone 'set @ row2column = 'label' SET @ row2columnValue = 'mcounts' SET @ OtherField = 'fullname, hx_fzone, hx_fpayamount, hx_freceivableamount, wsf '-- obtain possible columns from row data: SET @ SQL _str = n' SELECT @ SQL _col_out = ISNULL (@ SQL _col_out + '','','''') + QUOTENAME (['+ @ row2column +']) FROM ['+ @ tableName +'] group by ['+ @ row2column +'] '-- PRINT @ SQL _strEXEC sp_executesql @ SQL _str, n' @ SQL _col_out NVARCHAR (MAX) output', @ SQL _col_out = @ SQL _col OUTPUT--PRINT @ SQL _colSET @ SQL _str = n' SELECT * FROM (SELECT '+ @ OtherField + ', ['+ @ row2column +'], ['+ @ row2columnValue +'] '+' FROM ['+ @ tableName +']) p partition (sum (['+ @ row2columnValue +']) FOR ['+ @ row2column +'] IN ('+ @ SQL _col +') AS pvt where pvt. hx_fzone is not null order by '+ @ orderColumn -- PRINT (@ SQL _str) EXEC (@ SQL _str) drop table # t_estimateamountreasondrop table # t_EstimateamountReasonTotalend limit select @ end_date = getdate () select datediff (MS, @ begin_date, @ end_date) as 'time/millisecond'

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.