/*三明电力临时卡处理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]  下一页