需要对分类帐进行SQL查询 [英] Required a SQL query for the ledger
问题描述
我有两张桌子A和B
在A
MobileNo信用日期
9888569493 10000 30 -Dec-16
在B
MobileNo借记日期
9888569493 81 3-Apr-17
9888569493 261 3-Apr-17
9888569493 500 4-Apr-17
9888569493 1000 4-Apr-17
所以我想要的输出如下：
MobileNo日期信用借方余额
9888569493 30-Dec-16 10000 0 10000
9888569493 3-Apr-17 81 9919
9888569493 3-Apr -17 261 9658
9888569493 4-Apr-17 500 9158
9888569493 4-Apr-17 1000 8158
所以请紧急帮我解决这个复杂的问题
提前致谢。
我尝试了什么：
我试过这个：
从中选择d。*（选择日期，信用卡，借记卡，总和（贷方）-sum（借记卡） ）作为余额（
选择转换（varchar（10），日期，103）作为日期，ISNULL（SUM（CAST（运行为int）），0）作为信用，
0作为借记来自UserMobile用户移动.REtailer ='9888569493'和月='十月'和一年= 2016
Group By UserMobile.date
Union All
选择转换（varchar（10），date，103）为Date，0为Credit， ISNULL（SUM（CAST（运行为int）），0）作为借方
来自DisptachOrder其中UserName = 9888569493
按日期分组）作为c group by Date，Credit，借方）为d
我的输出是：
日期信用借方余额
NULL 100000 0 100000
04/04/2017 0 81 -81
04/04/2017 0 99 -99
04/04/2017 0 117 -117
04/04/2017 0 252 -252
见这里：计算SQL Server中的简单运行总计 [ ^ ]
使用以下查询
SELECT MobileNo，convert （varchar（10），Date，103）as Date，
Credit，
Debit，
SUM（isnull（Credit，0） - isnull（Debit，0））OVER（ORDER BY Date）作为余额
FROM（选择日期，MobileNo，Credit，0作为借记从A
联盟所有
选择日期，MobileNo，0作为信用，从B借记） ）作为x
按日期排序
从表
收集数据-------- -----------------------------
SELECT * FROM tb_Debit
select * from tb_credit
----------------------------------------
工作从这里
-------------------------------------
创建表#t
（
MobileNo V. archar（15），
信贷浮动，
借记浮动，
TrnDate日期时间
）
插入#t
（
MobileNo，Credit，Debit，TrnDate
）
从
中选择MobileNo，credit，debit，trnDate
（
选择MobileNo，credit，0作为借记，trnDate来自tb_credit
union all
选择MobileNo，0作为信用卡，借记卡，trnDate来自tb_Debit
）y
订单来自trnDate asc
--select * from #t
，#ledger为
（
选择
MobileNo，
TrnDate，
Credit，
借记卡，
ROW_NUMBER（）结束（由trndate订购）作为行
来自#t
）
SELECT
L1.MobileNo，
L1.TrnDate，
（ISNULL（MAX（L1.Credit），0）+ ISNULL（SUM（L2.Credit），0） - ISNULL（SUM（L2.Debit），0））AS开盘，
L1.Credit As Credit，
L1.Debit As Debit，
（ISNULL（L1.Credit，0）+ ISNULL（SUM（L2.Credit），0） - ISNULL（SUM（L2.Debit），0） - ISNULL（ （L1.Debi t），0））收盘
FROM #ledger L1
LEFT JOIN #ledger L2
ON L1.row> L2.row
GROUP BY L1.MobileNo，L1.TrnDate ，L1.Credit，L1.Debit
：这是您的最佳解决方案之一问题
I have two tables A and B
In A
MobileNo Credit Date
9888569493 10000 30-Dec-16
In B
MobileNo Debit Date
9888569493 81 3-Apr-17
9888569493 261 3-Apr-17
9888569493 500 4-Apr-17
9888569493 1000 4-Apr-17
So I want my output like given below:
MobileNo Date Credit Debit Balance
9888569493 30-Dec-16 10000 0 10000
9888569493 3-Apr-17 81 9919
9888569493 3-Apr-17 261 9658
9888569493 4-Apr-17 500 9158
9888569493 4-Apr-17 1000 8158
So please help me about this complex query at urgent basis
Thanks in advance.
What I have tried:
I tried this one:
select d.* from(select Date,Credit,Debit, sum(Credit)-sum(Debit) as Balance from(
Select convert(varchar(10),date ,103) as Date, ISNULL(SUM(CAST(Runs as int)),0) as Credit ,
0 as Debit From UserMobile Where UserMobile .REtailer ='9888569493' And month ='October' And year =2016
Group By UserMobile.date
Union All
Select convert(varchar(10),date ,103) as Date, 0 as Credit ,ISNULL(SUM(CAST(Runs as int)),0) as debit
From DisptachOrder Where UserName =9888569493
Group By date)as c group by Date ,Credit,Debit) as d
And my Output is:
Date Credit Debit Balance
NULL 100000 0 100000
04/04/2017 0 81 -81
04/04/2017 0 99 -99
04/04/2017 0 117 -117
04/04/2017 0 252 -252
See here: Calculating simple running totals in SQL Server[^]
use below query
SELECT MobileNo,convert(varchar(10),Date,103) as Date, Credit, Debit, SUM(isnull(Credit,0) - isnull(Debit,0)) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance FROM (Select Date,MobileNo,Credit,0 as Debit from A Union all Select Date,MobileNo,0 as Credit,Debit from B) as x order by Date
Collect data From Table ------------------------------------- SELECT * FROM tb_Debit select * from tb_credit ---------------------------------------- work start from here ------------------------------------- create table #t ( MobileNo Varchar(15), Credit float, Debit float, TrnDate Datetime ) insert into #t ( MobileNo,Credit,Debit,TrnDate ) Select MobileNo,credit,debit,trnDate from ( select MobileNo,credit,0 as debit,trnDate from tb_credit union all select MobileNo,0 as credit,debit,trnDate from tb_Debit )y order by trnDate asc --select * from #t with #ledger as ( select MobileNo, TrnDate, Credit, Debit, ROW_NUMBER()over(ORDER by trndate)as row from #t ) SELECT L1.MobileNo, L1.TrnDate, (ISNULL(MAX(L1.Credit),0)+ ISNULL(SUM(L2.Credit),0)- ISNULL(SUM(L2.Debit),0)) AS Opening, L1.Credit As Credit, L1.Debit As Debit, (ISNULL( L1.Credit,0) + ISNULL(SUM(L2.Credit),0)- ISNULL(SUM(L2.Debit),0)- ISNULL((L1.Debit),0)) As closing FROM #ledger L1 LEFT JOIN #ledger L2 ON L1.row>L2.row GROUP BY L1.MobileNo,L1.TrnDate,L1.Credit,L1.Debit
: It is the One Of the Best Solution For your question
这篇关于需要对分类帐进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
