SQL script 會計記賬 Debit-Credit Bookkeeping

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125

---會計記賬 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

/********** 塗聚文 Geovin Du 締友計算機信息技術有限公司 ***********/   create table tblAccount (  A_ID VARCHAR(20),  A_Type VARCHAR(20),  A_Amount MONEY ) GO -- insert into tblAccount (A_ID,A_Type, A_Amount) select '1','Credit',500 union all select '1','Credit',100 union all select '1','Debit',50 union all select '2','Debit',150 union all select '2','Credit',100 go   -- select  a.SumCr as Credit ,         b.SumDr as Debit ,         a.SumCr - b.SumDr as Balance from    ( select    sum(A_Amount) as SumCr           from      tblAccount           where     A_ID = 1                     and A_Type = 'Credit'         ) a ,         ( select    sum(A_Amount) as SumDr           from      tblAccount           where     A_ID = 1                     and A_Type = 'Debit'         ) b -- select  a.A_ID as [ID] ,         a.SumCr as Credit ,         b.SumDr as Debit ,         a.SumCr - b.SumDr as Balance from    ( select    A_ID ,                     sum(A_Amount) as SumCr           from      tblAccount           where     A_Type = 'Credit'           group by  A_ID         ) a         inner join ( select A_ID ,                             sum(A_Amount) as SumDr                      from   tblAccount                      where  A_Type = 'Debit'                      group by A_ID                    ) b on a.A_ID = b.A_ID   go   drop table tblAccount go ---