詳細做用請問你身邊的學會計的朋友 （= =）||
*********************************小花分割線************************************
先來看一下程式
--設定起始變數
declare @sday datetime
set @sday =開始日期
declare @eday datetime
set @eday =結束日期
declare @aday int
set @aday = DATEDIFF (mm,CONVERT(datetime,@sday),CONVERT(datetime,@eday))+1
declare @i int
set @i = 0
declare @fday datetime
declare @tday datetime
--建立一個暫存的資料表
create table #stack
(bid varchar(38) NOT NULL,
days varchar(38) NOT NULL)
--這邊是建立日期區間以每30天為一個間隔
while @aday <> 0
begin
Set @fday = dateadd(month,@i,@sday)
SET @tday = dateadd(month,@i+1,@sday)
INSERT INTO #stack
select a.bid,days= (@i * 30) + 30
from 會計使用的資料表 a
where @fday <= a.expire_date and @tday > a.expire_date
set @aday = @aday - 1
set @i = @i +1
end
--這裡就是基礎的select的查詢語法，列出你所希望展示的欄位值
SELECT a.bid,a.check_status,a.getchk_date,a.expire_date,a.osum,a.fcash_date ,
b.bid as cust,b.custname ,
c.bid as vendid,c.vendname ,
e.bid as bank,e.bankname ,f.* ,
case when a.check_status = 0 THEN N'未兌現'
when a.check_status = 1 THEN N'托收中'
when a.check_status = 2 THEN N'兌現'
when a.check_status = 3 THEN N'退票'
when a.check_status = 4 THEN N'作廢'
when a.check_status = 5 THEN N'轉付帳款'
END as check_name
FROM 與會計資料相關的所有資料表
inner join #stack f on a.bid = f.bid
~~~~~~~~~~~~~~~~~~~~~~~~波浪分割線~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
以上SQL語法確認可以查詢、列出所希望列出的數據後，將語法放入iReport中產出報表
