查詢篇-消費單匯總及明細 (4.0)

功能介紹

學生購買的課程，商品，雜費等實際產生的數據稱為消費單數據，這個查詢就是專門列表員工工號，收費方式，金額匯總，并加上明細，方便學校了解自己銷售給學生的收入。可以直接復制代碼填入【分析】-【查詢管理】-【新建SQL查詢】，也可在【分析】-【查詢管理】-【應用市場】找到后添加。

相關代碼

主查詢

Select
Bill.UserID as 經辦人工號,
Bill.TrueName as 經辦人姓名,
Bill.PayMethodName as 支付方式名稱,
Bill.UserID as ShowKey,
Bill.PayMethodName as ShowKey2,
sum(BillItem.UnitPrice*BillItem.Amount+BillItem.Discount ) as 實收
from BillItem,Bill
where Bill.BillID=BillItem.BillID
and Bill.BillTypeID in(1)
and Bill.PostSchoolID in ( {@SchoolID:校區})
and Bill.DateAndTime>= {@StartDate:開始日期}
and Bill.DateAndTime<= {@EndDate:結束日期}
group by Bill.UserID,Bill.TrueName,Bill.PayMethodName

子查詢

Select
Bill.StudentID as 所屬學員編號,
Bill.StudentName as 所屬學員姓名,
Bill.PayMethodName as 支付方式名稱,
BillItem.BillItemID as 消費明細編號,
BillItem.ProductName as 收費項名稱,
BillItem.UnitPrice as 單價,
BillItem.Amount as 數量,
BillItem.Discount as 優惠,
(BillItem.UnitPrice*BillItem.Amount+BillItem.Discount ) as 實收,
BillItem.UnitName as 單位,
Bill.DateAndTime as 消費日期,
Bill.PostSchoolID as 收費網點編號,
Bill.PostSchoolName as 收費網點名稱,
Bill.UserID as 經辦人工號,
Bill.TrueName as 經辦人姓名,
Bill.PaymentState as 支付狀態,
Bill.PayCount as 支付次數,
BillItem.Remark as 備注
from BillItem,Bill
where Bill.BillID=BillItem.BillID
and Bill.BillTypeID in(1)
and Bill.PostSchoolID in ( {@SchoolID:校區})
and Bill.DateAndTime>= {@StartDate:開始日期}
and Bill.DateAndTime<= {@EndDate:結束日期}
and Bill.UserID = {@ShowKey}
and Bill.PayMethodName= {@ShowKey2}