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

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'