|
SELECT dp.dpname1 AS 部门, cust_dp_SumOddfre.sum_oddfare AS 当月卡总余额
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(YEAR(OpDt)
AS char)) + ''''-'''' + RTRIM(CAST(MONTH(OpDt) AS char))
+ ''''-'''' + RTRIM(DAY(0)) AS
dt
FROM
T_ConsumeRec
UNION
SELECT CustomerID, OpCount, RTRIM(CAST(YEAR(cashDt)
AS char)) + ''''-'''' + RTRIM(CAST(MONTH(cashDt) AS char))
+ ''''-'''' + RTRIM(DAY(0)) AS
dt
FROM T_Cashrec)
l1
WHERE (dt <=
''''2005-6-1'''')/*输入查询月份,可用参数传递*/
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
附：查询用到的基本表形成脚本：
CREATE TABLE [dbo].[T_CashRec] ( --出纳明细账本
[StatID] [tinyint] NOT
NULL ,
[CashID] [smallint] NOT NULL ,
[Port] [tinyint] NOT
NULL ,
[Term] [tinyint] NOT NULL ,
[CashDt] [datetime] NOT
NULL ,--存取款时间
[CollectDt] [datetime] NOT NULL ,
[CustomerID]
[int] NOT NULL ,
[OpCount] [int] NOT NULL
,--某卡的操作次数，只累加
[InFare] [money] NOT NULL ,
[OutFare] [money]
NOT NULL ,
[SumFare] [money] NOT NULL ,
[OddFare] [money] NOT
NULL ,--此次操作后该卡的余额
[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] ( --消费明细账本
[StatID] [tinyint] NOT
NULL ,
[Port] [tinyint] NOT NULL ,
[Term] [tinyint] NOT NULL
,
[CustomerID] [int] NOT NULL ,
[OpCount] [int] NOT NULL ,
--某卡的操作次数，只累加
[OpDt] [datetime] NOT NULL ,--消费时间
[CollectDt]
[datetime] NOT NULL ,
[MealID] [tinyint] NOT NULL ,
[SumFare]
[smallmoney] NOT NULL ,
[OddFare] [smallmoney] NOT NULL
,--此次操作后该卡的余额
[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] ( --客户账本
[CustomerID] [int] NOT
NULL , --客户号，主键
[StatCode] [varchar] (3) COLLATE Chinese_PRC_CI_AS NOT
NULL ,
[Account] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL
,--单位代号
[Name] [varchar] (12) 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 NUL [1] [2] 下一页 |
