To implement the SQL query code for Grouping statistics by department monthly total Balance

grouping | statistics

Chen Youzhang's Column

(Original, to now to the most complex SQL query code) to achieve by the Department of the monthly total balance of the amount of the SQL query code (debugging in MS SQL Server through)

SELECT dp.dpname1 as department, cust_dp_sumoddfre.sum_oddfare as current month card total balance
From (SELECT t_department.dpcode1, SUM (Custid_sumoddfare_group.sum_oddfare)
As Sum_oddfare
From (SELECT L2. CustomerID, SUM (r1.oddfare) as Sum_oddfare
From (SELECT CustomerID, MAX (opcount) as Max_opcount
From (SELECT CustomerID, Opcount, RTRIM (CAST (OPDT)
As char)) + '-' + RTRIM (CAST (MONTH (OPDT) as Char))
+ '-' + RTRIM (day (0)) as DT
From T_consumerec
UNION
SELECT CustomerID, Opcount, RTRIM (CAST (CASHDT)
As char)) + '-' + RTRIM (CAST (MONTH (CASHDT) as Char))
+ '-' + RTRIM (day (0)) as DT
From T_cashrec) L1
WHERE (dt <= ' 2005-6-1 ')/* input Query month, can be passed by parameter * *
GROUP by CustomerID) L2 INNER JOIN
(SELECT CustomerID, Opcount, Oddfare
From T_consumerec
UNION
SELECT CustomerID, Opcount, Oddfare
From T_cashrec) R1 on L2. CustomerID = R1. CustomerID and
R1. Opcount = L2.max_opcount
GROUP by L2. CustomerID) Custid_sumoddfare_group INNER JOIN
T_customers on
Custid_sumoddfare_group. CustomerID = T_customers.customerid INNER JOIN
T_department on SUBSTRING (T_customers.account, 1, 2)
= T_department.dpcode1 and SUBSTRING (T_customers.account, 3, 2)
= T_department.dpcode2 and SUBSTRING (T_customers.account, 5, 3)
= T_department.dpcode3
GROUP by DpCode1) Cust_dp_sumoddfre INNER JOIN
(SELECT DISTINCT Dpcode1, dpname1
From T_department) dp on dp.dpcode1 = CUST_DP_SUMODDFRE.DPCODE1

Attach: Basic table Form script used for query:

CREATE TABLE [dbo]. [T_cashrec] (--Cashier Ledger
[Statid] [tinyint] Not NULL,
[Cashid] [smallint] Not NULL,
[Port] [tinyint] Not NULL,
[Term] [tinyint] Not NULL,
[CASHDT] [DateTime] Not NULL,--deposit and withdrawal time
[COLLECTDT] [DateTime] Not NULL,
[CustomerID] [INT] Not NULL,
[Opcount] [INT] Not NULL,--the number of times a card has been manipulated, accumulating only
[Infare] [Money] Not NULL,
[Outfare] [Money] Not NULL,
[Sumfare] [Money] Not NULL,
[Oddfare] [Money] Not NULL,--balance of the card after this operation
[Mngfare] [Money] Not NULL,
[Hz] [tinyint] Not NULL,
[Cursum] [SmallMoney] Null
[Curcount] [smallint] Null
[CARDSN] [tinyint] Null
) on [PRIMARY]
Go

CREATE TABLE [dbo]. [T_consumerec] (--Consumer Ledger
[Statid] [tinyint] Not NULL,
[Port] [tinyint] Not NULL,
[Term] [tinyint] Not NULL,
[CustomerID] [INT] Not NULL,
[Opcount] [INT] Not NULL,--the number of times a card is manipulated, only cumulative
[OPDT] [DateTime] Not NULL,--consumption time
[COLLECTDT] [DateTime] Not NULL,
[Mealid] [tinyint] Not NULL,
[Sumfare] [SmallMoney] Not NULL,
[Oddfare] [SmallMoney] Not NULL,--balance of the card after this operation
[Mngfare] [SmallMoney] Not NULL,
[Opfare] [SmallMoney] Not NULL,
[Hz] [tinyint] Not NULL,
[MenuID] [smallint] Null
[Menunum] [tinyint] Null
[Oddfarepre] [SmallMoney] Null
[Recno] [smallint] Null
[CARDSN] [tinyint] Not NULL,
[Cardver] [tinyint] Null
) on [PRIMARY]
Go

CREATE TABLE [dbo]. [T_customers] (--Customer ledger
[CustomerID] [INT] Not NULL,--customer number, primary key
[Statcode] [varchar] (3) COLLATE chinese_prc_ci_as not NULL,
[Account] [varchar] (7) COLLATE chinese_prc_ci_as not NULL,--Unit code
[Name] [varchar] (a) COLLATE chinese_prc_ci_as not NULL,
[Cardno] [INT] Not NULL,
[CARDSN] [tinyint] Null
[Cardtype] [tinyint] Not NULL,
[Status] [tinyint] Not NULL,
[Opendt] [DateTime] Not NULL,
[Cashid] [smallint] Not NULL,
[Sumfare] [SmallMoney] Not NULL,
[Consumefare] [SmallMoney] Not NULL,
[Oddfare] [SmallMoney] Not NULL,
[Opcount] [INT] Not NULL,
[Cursubsidyfare] [SmallMoney] Not NULL,
[Subsidydt] [DateTime] Not NULL,
[Subsidyout] [Char] (1) COLLATE chinese_prc_ci_as not NULL,
[Alias] [varchar] (a) COLLATE chinese_prc_ci_as NULL,
[Outid] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[UpdateID] [tinyint] Not NULL,
[PWD] [Char] (4) COLLATE chinese_prc_ci_as NULL,
[Quchargfare] [SmallMoney] Null
[Hastaken] [tinyint] Null
[Dragoncardno] [Char] () COLLATE chinese_prc_ci_as NULL,
[Applycharg] [SmallMoney] Null
[Chargper] [SmallMoney] Null
[Mingzu] [varchar] (m) COLLATE chinese_prc_ci_as NULL,
[Sex] [Char] (2) COLLATE chinese_prc_ci_as NULL,
[Memo] [varchar] (MB) COLLATE chinese_prc_ci_as NULL,
[WEIPEIDW] [varchar] (a) COLLATE chinese_prc_ci_as NULL,
[Cardconsumetype] [tinyint] Null
[Leaveschooldt] [DateTime] Null
[USEVALIDDT] [tinyint] Not NULL,
[Nousedate] [DateTime] Not NULL
) on [PRIMARY]
Go

CREATE TABLE [dbo]. [T_department] (--Unit books, three units of units, tree-type structure
[DpCode1] [Char] (2) COLLATE chinese_prc_ci_as not NULL,
[DpCode2] [Char] (2) COLLATE chinese_prc_ci_as NULL,
[DpCode3] [Char] (3) COLLATE chinese_prc_ci_as NULL,
[DpName1] [varchar] () COLLATE chinese_prc_ci_as NULL,
[DpName2] [varchar] () COLLATE chinese_prc_ci_as NULL,
[DpName3] [varchar] () COLLATE chinese_prc_ci_as NULL,
[N_SR] [INT] Not NULL,
[Batnum] [smallint] Null
) on [PRIMARY]
Go