Ledger Query ,is not giving expected output

Question

Hi @EchoLiu-MSFT @Tom Cooper @MelissaMa-MSFT

Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);  
  Create table #tbl_Shop_InvM (Inv_ID int ,Customer_ID int,E_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);  
   Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50),opening int);  
                  
   insert into #tbl_Customer values (1,'Akhter',2000), (2,'Hussian',0);  
      
     INSERT INTO #tbl_Shop_InvM VALUES  
   (211,1,'2020-03-06',5000,'Payable',0),  
      
   (222,1,'2020-03-08',8000,'Payable',0),  
   (223,2,'2020-03-08',2000,'Payable',0),  
     (224,1,'2020-03-09',5500,'Payable',0),  
   (225,2,'2020-03-10',15000,'Payable',500)  
                  
   INSERT INTO #tbl_Receivable VALUES  
       
   (112,211,1,'2020-03-07',2000,'Received',0),  
   (115,222,1,'2020-03-09',4000,'Received',0),  
    (116,222,1,'2020-03-09',2000,'Received',0),  
      
   (117,211,1,'2020-03-12',1000,'Received',0),  
   (118,225,2,'2020-03-11',15000,'Received',0);  
      
  declare @begin_date date = '2020-03-06';  
    declare @end_date date = '2020-03-11';  
  
  
	  
 ;with cte as (  
 select Customer_id ,'' Descript,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
 from #tbl_Shop_InvM  
 where #tbl_Shop_InvM.Customer_ID=1 and  Inv_Type='Payable' and (E_date between @begin_date and @end_date)  
 union   
 select Customer_id ,'' Descript,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
 from #tbl_Receivable   
  where #tbl_Receivable.Customer_ID=1 and Inv_Type='Received' and (Rec_Date between  @begin_date and @end_date))  
 ,cte1 as (  
 select *,Payable+ received*-1 balance from cte   
 )  
 select Customer_id,Descript,E_Date,Payable,Received,  
 sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date ROWS UNBOUNDED PRECEDING) Balance   
 from cte1  

Answer 1

Hi @Analyst_SQL ,

Please refer below updated query:

declare @begin_date date = '2020-03-06';  
 declare @end_date date = '2020-03-11';  
          
 ;with cte as (  
 select Customer_id ,'' Descript,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
 from #tbl_Shop_InvM  
 where #tbl_Shop_InvM.Customer_ID=1 and  Inv_Type='Payable' --and (E_date between @begin_date and @end_date)  
 union   
 select Customer_id ,'' Descript,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
 from #tbl_Receivable   
 where #tbl_Receivable.Customer_ID=1 and Inv_Type='Received'-- and (Rec_Date between  @begin_date and @end_date)  
 ),cte1 as (  
 select *,Payable+ received*-1 balance from cte   
 union all  
 select distinct a.Customer_id,'Openning' Descript, dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, b.opening Balance  
 from cte a  
 inner join #tbl_Customer b on a.Customer_ID=b.Customer_ID  
 ),  
 cte2 as (  
 select Customer_id,Descript,E_Date,Payable,Received,  
 sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date ROWS UNBOUNDED PRECEDING) Balance   
 from cte1)  
 ,cte3 as   
 (select  Customer_id,'Openning' Descript, E_Date,0 Payable,0 Received, Balance  
 ,ROW_NUMBER() OVER(PARTITION BY Customer_id ORDER BY (select null))  rn   
 from cte2  
 where E_Date < @begin_date)  
 select Customer_id,Descript,dateadd(day,-1,@begin_date) E_Date,Payable,Received,Balance from cte3 where rn=(select max(rn) from cte3)  
 union all  
 select * from cte2 where E_Date between @begin_date and @end_date  
 order by E_Date  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Hi @Analyst_SQL ,

Please refer below query:

;with cte as (  
select Customer_id ,'' Descript,E_Date,rec_amount Payable,'' received  
from #tbl_Shop_InvM  
union   
select Customer_id ,'' Descript,rec_date  E_Date,'' Payable,rec_amount received  
from #tbl_Receivable)  
,cte1 as (  
select *,Payable+ received*-1 balance from cte   
)  
select Customer_id,Descript,E_Date,Payable,Received,  
sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date ROWS UNBOUNDED PRECEDING) Balance   
from cte1  

Output:

Customer_id	Descript	E_Date	Payable	Received	Balance  
1		2020-03-06	5000		5000  
1		2020-03-07		2000	3000  
1		2020-03-08	8000		11000  
1		2020-03-09		2000	9000  
1		2020-03-09		4000	5000  
1		2020-03-09	5500		10500  
1		2020-03-12		1000	9500  
2		2020-03-08	2000		2000  
2		2020-03-10	15000		17000  
2		2020-03-11		15000	2000  

But I still have some conern about your output after giving the period.

So if i give date from 2020-03-12 to 20-03-12 then opening will be 10500.

What is the expected output of above condition? Only one row of 2020-03-12 with 10500? Or all the rows from 2020-03-06 to 2020-03-09?

Please provide expected outputs for some conditions like below and I could proceed with this query.

• from 2020-03-05 to 2020-03-05
• from 2020-03-05 to 2020-03-09
• from 2020-03-06 to 2020-03-12
• from 2020-03-12 to 2020-03-12

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

Hi @Analyst_SQL ,

I still have some concerns about your update.

query check if Opening exit in customer table then query pick from opening there as you can see customer_ID =1 have opening is 2000.

I still could not find out where is 2000 from. Please kindly provide more details.

Besides, what about the expected output of 'from 2020-03-12 to 2020-03-12'?

I tried my best to update the query as below. Please check and provide any update so that I could proceed with updating.

declare @begin_date date = '2020-03-06';  
declare @end_date date = '2020-03-12';  
	  
;with cte as (  
select Customer_id ,'' Descript,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
from #tbl_Shop_InvM  
where #tbl_Shop_InvM.Customer_ID=1 and  Inv_Type='Payable' --and (E_date between @begin_date and @end_date)  
union   
select Customer_id ,'' Descript,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
from #tbl_Receivable   
where #tbl_Receivable.Customer_ID=1 and Inv_Type='Received'-- and (Rec_Date between  @begin_date and @end_date)  
),cte1 as (  
select *,Payable+ received*-1 balance from cte   
),cte2 as (  
select Customer_id,Descript,E_Date,Payable,Received,  
sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date ROWS UNBOUNDED PRECEDING) Balance   
from cte1)  
,cte3 as   
(select  Customer_id,'Openning' Descript, E_Date,0 Payable,0 Received, Balance  
,ROW_NUMBER() OVER(PARTITION BY Customer_id ORDER BY (select null))  rn   
from cte2  
where E_Date < @begin_date  
union   
select Customer_id,'Openning' Descript, dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, 2000 Balance,0 rn  
from cte2)  
select Customer_id,Descript,dateadd(day,-1,@begin_date) E_Date,Payable,Received,Balance from cte3 where rn=(select max(rn) from cte3)  
union all  
select * from cte2 where E_Date between @begin_date and @end_date  
order by E_Date  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Hi @Analyst_SQL ,

It is not recommended to edit your orginal post by updating your original query with answers posted by other experts since it may cause other users who encounter the same issue and read this thread be mixed with this issue. 

I have updated my query as below and check whether it is working to you.

declare @begin_date date = '2020-03-06';  
 declare @end_date date = '2020-03-11';  
          
 ;with cte as (  
 select Customer_id ,'' Descript,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
 from #tbl_Shop_InvM  
 where #tbl_Shop_InvM.Customer_ID=1 and  Inv_Type='Payable' --and (E_date between @begin_date and @end_date)  
 union   
 select Customer_id ,'' Descript,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
 from #tbl_Receivable   
 where #tbl_Receivable.Customer_ID=1 and Inv_Type='Received'-- and (Rec_Date between  @begin_date and @end_date)  
 ),cte1 as (  
 select *,Payable+ received*-1 balance from cte   
 ),cte2 as (  
 select Customer_id,Descript,E_Date,Payable,Received,  
 sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date ROWS UNBOUNDED PRECEDING) Balance   
 from cte1)  
 ,cte3 as   
 (select  Customer_id,'Openning' Descript, E_Date,0 Payable,0 Received, Balance  
 ,ROW_NUMBER() OVER(PARTITION BY Customer_id ORDER BY (select null))  rn   
 from cte2  
 where E_Date < @begin_date  
 union   
 select a.Customer_id,'Openning' Descript, dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, b.opening Balance,0 rn  
 from cte2 a  
 inner join #tbl_Customer b on a.Customer_ID=b.Customer_ID)  
 select Customer_id,Descript,dateadd(day,-1,@begin_date) E_Date,Payable,Received,Balance from cte3 where rn=(select max(rn) from cte3)  
 union all  
 select * from cte2 where E_Date between @begin_date and @end_date  
 order by E_Date  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 5

You have asked the same question multiple times and changing your requirements. Please provide an EXACT example of what EXACTLY your data looks like and EXACTLY the output expected from that data provided.