SQL script 會計記賬 Debit-Credit Bookkeeping

---會計記賬 Debit-Credit Bookkeeping

CREATE TABLE 
        #geovindu 
(        
        Account VARCHAR(20), 	--賬號
        [Date] DATETIME, 	--時間
        Debit DECIMAL(9,2), 	--借入
        Credit DECIMAL(9,2) 	--貸出
) 
GO
INSERT INTO #geovindu VALUES ('10139', '2007-08-31', 2025.91, 0.0) 
INSERT INTO #geovindu VALUES ('10139', '2007-08-31', 0.0, 3620.11) 
INSERT INTO #geovindu VALUES ('10139', '2007-09-30', 4631.52, 0.0) 
INSERT INTO #geovindu VALUES ('10139', '2007-09-30', 0.0, 11336.71) 
INSERT INTO #geovindu VALUES ('10139', '2007-09-30', 0.0, 14.8801) 
INSERT INTO #geovindu VALUES ('12211', '2007-08-31', 1352.76, 0.0) 
INSERT INTO #geovindu VALUES ('12211', '2007-08-31', 0.0, 3872.5) 
  
/*
塗聚文 締友計算機信息技術有限公司 
Geovin Du
*/ 
---查詢

SELECT 
        account AS '賬目編目號', 
        [date] AS '日期', 
        SUM(Debit) AS '借', 
        - SUM(Credit) AS '貸', 
        (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #geovindu m2 WHERE m1.account = m2.account AND m2.[date] <= m1.[date] AND  
  
CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END 
  
) AS '合計' --Balance 
FROM 
        #geovindu m1 
GROUP BY 
        account, 
        [date], 
        CASE WHEN debit <> 0 THEN 0 ELSE 1 END 
ORDER BY 
        account, 
        [date], 
        CASE WHEN debit <> 0 THEN 0 ELSE 1 END 
  
  
DROP TABLE #geovindu 

DROP TABLE #GeTbl
GO
create table #GeTbl (Tid VARCHAR(20), Ttype varchar(50), Tamt float) ;
GO
Drop table #GeType
Create table #GeType (TType varchar(50) primary key, GroupType tinyint)

insert into #GeType (TType,GroupType) values ('Cash',1)
insert into #GeType (TType,GroupType) values ('Expense',1)
insert into #GeType (TType,GroupType) values ('Credit',2)
insert into #GeType (TType,GroupType) values ('Debit',2)
insert into #GeType (TType,GroupType) values ('Petty Cash',3)
insert into #GeType (TType,GroupType) values ('Petty Expense',3)


insert into #GeTbl  select  
'101','Cash',-100
UNION ALL SELECT 
'101','Expense',-200
UNION ALL SELECT 
'101','Credit',-100
UNION ALL SELECT 
'101','Debit',-100 UNION ALL SELECT 
'101','Expense',-150 UNION ALL SELECT 
'102','Credit',-50 UNION ALL SELECT 
'102','Debit',-100 UNION ALL SELECT 
'102','Petty expense',100 UNION ALL SELECT 
'102','Cash',200 UNION ALL SELECT 
'102','Expense',-200 UNION ALL SELECT 
'102','Petty cash',100 UNION ALL SELECT 
'103','Cash',200 UNION ALL SELECT 
'103','Expense',-100 UNION ALL SELECT 
'104','Cash',200 UNION ALL SELECT 
'104','Expense',-200

---
select * from (
select * from #GeTbl
 where Ttype in ('Cash','Expense')
   and Tid in (select Tid from 
                (select Tid, SUM(Tamt) DrCrTotal
                   from #GeTbl
                  where Ttype in ('Cash','Expense')
                  group by Tid
                   having SUM(Tamt) <> 0) v)
union all
select * from #GeTbl
 where Ttype in ('Debit','Credit')
   and Tid in (select Tid from 
                (select Tid, SUM(case when TType = 'Credit' then Tamt * -1 else Tamt end) DrCrTotal
                   from #GeTbl
                  where Ttype in ('Debit','Credit')
                  group by Tid
                   having SUM(case when TType = 'Credit' then Tamt * -1 else Tamt end) <> 0) v)
union all
select * from #GeTbl
 where Ttype not in ('Debit','Credit', 'Cash','Expense')
 ) x
order by TID                      


---
select g.Tid, g.Ttype, g.Tamt 
from #GeTbl g
inner join #GeType gt
 on g.Ttype = gt.TType
inner join  (
select a.Tid, b.GroupType, Sum(a.Tamt) DrCrTotal
   from #GeTbl a
   inner join #GeType b
     on a.Ttype = b.TType                                          
  group by a.Tid, b.GroupType
   having SUM(a.Tamt) <> 0) v
on g.Tid = v.Tid
and gt.GroupType = v.GroupType   
order by g.Tid