|
/*三明电力临时卡处理SQL代码,临时卡为专门一类卡，消费时五折优惠，月消费有限额，采取策略是“钱多存，消费少报”*/
SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,
lll.OpDt AS 日期, lll.InFare AS 存款额, lll.InCount AS 存款次数, lll.OutFare AS 取款额,
lll.OutCount AS 取款次数, ISNULL(lll.suminfare, 0) / 2 AS 让利额, ISNULL(lll.countinfare,
0) AS 让利次数, (lll.InFare - lll.OutFare) - ISNULL(lll.suminfare, 0)
/ 2 AS 存取款应缴金额
FROM (SELECT dbo.T_CashDay.StatID, dbo.T_CashDay.OpDt, dbo.T_CashDay.CashID,
dbo.T_CashDay.InFare, dbo.T_CashDay.InCount, dbo.T_CashDay.OutFare,
dbo.T_CashDay.OutCount, ll.suminfare, ll.countinfare
FROM dbo.T_CashDay LEFT OUTER JOIN /*采用左外联接查询*/
(SELECT cash.StatID, cash.CashID, dbo.T_CashDay.OpDt,
dbo.T_CashDay.InFare, dbo.T_CashDay.InCount,
dbo.T_CashDay.OutFare, dbo.T_CashDay.OutCount, cash.suminfare,
countinfare
FROM (SELECT Cashrec.StatID, Cashrec.CashID, Cashrec.dt,
SUM(Cashrec.InFare) AS SumInfare, COUNT(*)
AS CountInfare
FROM (SELECT StatID, CashID, RTRIM(CAST(YEAR(CashDt)
AS char)) + ''''-'''' + RTRIM(CAST(MONTH(CashDt) AS char))
+ ''''-'''' + RTRIM(CAST(DAY(CashDt) AS char)) AS dt /*存款日期*/, InFare,
OutFare, CustomerID
FROM dbo.T_CashRec
WHERE outfare = 0) Cashrec /*存款具体时间转换为日期的视图*/ INNER JOIN
dbo.T_Customers ON
Cashrec.CustomerID = dbo.T_Customers.CustomerID
WHERE (dbo.T_Customers.CardType = 1 /*临时卡类型*/ )
GROUP BY Cashrec.StatID, Cashrec.CashID, Cashrec.dt)
cash /*以StatID,CashID,dt作为分组字段，统计出临时卡的存款总额及存款次数的视图*/ INNER JOIN
dbo.T_CashDay ON cash.StatID = dbo.T_CashDay.StatID AND
cash.CashID = dbo.T_CashDay.CashID AND
cash.dt = dbo.T_CashDay.OpDt) ll /*cash视图与T_CashDay表进行联接查询的视图*/ ON
dbo.T_CashDay.OpDt = ll.OpDt AND dbo.T_CashDay.CashID = ll.CashID AND
ll.StatID = dbo.T_CashDay.StatID
WHERE (dbo.T_CashDay.OpDt BETWEEN ''''2004-7-1'''' AND ''''2004-7-1''''/*临时卡存款的时间范围*/)) lll INNER JOIN
dbo.T_Cashiers ON lll.CashID = dbo.T_Cashiers.CashID AND
lll.StatID = dbo.T_Cashiers.StatID INNER JOIN
dbo.T_Station ON lll.StatID = dbo.T_Station.StatID
SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,
dbo.T_Group.GrpName AS 食堂经营组, dbo.T_Terms.Port AS 端口,
dbo.T_Terms.TermName AS 窗机名称, yf.SumDt AS 日期,
dbo.T_Meal.MealName AS 餐别, yf.IncomeFare AS 营业额,
yf.IncomeCount AS 营业次数, ISNULL(yf.SumOpfare, 0) / 2 AS 优惠额,
ISNULL(yf.CountOpfare, 0) AS 优惠次数, yf.MngFare AS 管理费,
yf.CorrectFare AS 纠错额, yf.IncomeFare - ISNULL(yf.SumOpfare, 0)
/ 2 + yf.MngFare - yf.CorrectFare AS 实际收入
FROM (SELECT dbo.T_IncomeRec.StatID, dbo.T_IncomeRec.MealID,
dbo.T_IncomeRec.Port, dbo.T_IncomeRec.Term, dbo.T_IncomeRec.SumDt,
dbo.T_IncomeRec.IncomeFare, dbo.T_IncomeRec.IncomeCount,
s_c_opf.SumOpfare, s_c_opf.CountOpfare, dbo.T_IncomeRec.MngFare,
dbo.T_IncomeRec.CorrectFare
FROM dbo.T_IncomeRec LEFT OUTER JOIN
(SELECT ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,
ConsumeRec.MealID, dt, SUM(ConsumeRec.OpFare) AS SumOpfare,
&nbs
[1] [2] [3] [4] 下一页
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
[办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
