/*三明电力临时卡处理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 _disibledevent= dbo.T_CashDay.StatID AND